[pgpool-general: 8835] Re: Questions on Recovery_1st Stage Script

[Zhaoxun Yan]

So if I install servers from scratch, without any real data yet.

Could I start the 2nd server just by `stream replication`and `promote` -
only these two commands instead?

On Wed, Jun 14, 2023 at 3:34 PM Bo Peng <pengbo at sraoss.co.jp> wrote:

> Hi,
>
> > regarding your latest commit:
> >
> >
> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob;f=src/sample/scripts/replication_mode_recovery_1st_stage.sample;h=300c30395f9bb51bd15948e3af177d59fb9142fc;hb=7ed589de5f608eabe4c0d8026f9adcce1eedd015
> >
> > *1) *Is it also applicable to snapshot mode as well? It seems to register
> > standby here as a stream replication of the primary
>
> Yes. replication_mode_recovery_1st_stage.sample and
> replication_mode_recovery_2nd_stage.sample
> are the scripts for isolation snapshot mode.
>
> In the first stage, using streaming replication to make sure
> the standby can receive the WALs which are generated after executing
> pg_basebackup.
>
> In the second stage, the standby will be promoted to primary.
>
> > *2) *There is no "pgpass" or "archivedir" in postgresql, at least to my
> > experience:
>
> These scripts assume you are using archive_mode and .pgpass,
> and archive_command copies all WALs to archivedir directory.
> It must be manually configured before you start pgpool.
>
> Please check the prerequisites:
> https://www.pgpool.net/docs/latest/en/html/example-replication-mode.html
>
> Because they are sample scripts, you need to modify them to
> match your PostgreSQL configurations.
> If you don't need archive_mode, you can customize the scripts.
>
> > ARCHIVEDIR=/var/lib/pgsql/archivedir
> >     cat > ${RECOVERYCONF} << EOT
> > primary_conninfo = 'host=${MAIN_NODE_HOST} port=${MAIN_NODE_PORT}
> > user=${REPLUSER} application_name=${DEST_NODE_HOST}
> > passfile=''/var/lib/pgsql/.pgpass'''
> > recovery_target_timeline = 'latest'
> > restore_command = 'scp ${SSH_OPTIONS} ${MAIN_NODE_HOST}:${ARCHIVEDIR} /%f
> > %p'
> >
> > *3) *What does it wait for here? Since it has waited postgresql to finish
> > the replication?
>
> No. "-w" doesn't wait for postgresql to finish recovery.
> That is why we need to check if standby catched up primary server.
>
> > # start target server as a streaming replication standby server
> > ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@$DEST_NODE_HOST "
> > $PGHOME/bin/pg_ctl -l /dev/null -w -D $DEST_NODE_PGDATA start
> > "
> > # wait till the standby catches up primary server or
> > # $MAX_DURATION seconds passes
> > sec=0
> > while [ $sec -lt $MAX_DURATION ]
> > do
> >   sec=`expr $sec + 1`
> >
> >   result=`psql -h ${MAIN_NODE_HOST} -p ${MAIN_NODE_PORT} -c "SELECT
> > sent_lsn = replay_lsn FROM pg_stat_replication where application_name =
> > '${DEST_NODE_HOST}'" postgres | sed -n 3p|sed 's/ //'`
> >
> >   echo "result: $result"
> >   if [ "$result" = "t" ];then
> >     sec=$MAX_DURATION
> >   fi
> >   sleep 1
> > done
> >
> > *4)* Did you mean `-U postgres` in the line beginning with `result=` ?
>
> It is the user who is running PostgreSQL.
> In this script it is "postgres" user.
>
> --
> Bo Peng <pengbo at sraoss.co.jp>
> SRA OSS LLC
> TEL: 03-5979-2701 FAX: 03-5979-2702
> URL: https://www.sraoss.co.jp/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20230614/43ec4d0f/attachment.htm>