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

[Bo Peng]

Hi,

> 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?

If so, you need just "pg_basebackup" -> "promote".
 
> 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/
> >


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/