[pgpool-general: 4876] Re: Need some explanation about recovery

[Tatsuo Ishii]

> Hello,
> 
> I installed pgpool-II version 3.5.3 (ekieboshi) on Ubuntu server
> 16.04.1 with psql (PostgreSQL) 9.5.3. I configure servers with
> replication and load balancing. Pgpool is listening on 5432 port, both
> Postgresql on 55432. To create the recovery to the standby server I
> used following script:
> 
> #!/bin/bash
> 
> master=192.168.10.53
> port=55432
> user=replicator
> userpwd=<replicator PWD>
> pg_path=/var/lib/postgresql/9.5/main #/var/lib/psql/data is a symlink
> pg_user=postgres
> triggerfile=/tmp/postgresql.trigger
> 
> echo Stopping PostgreSQL
> sudo service postgresql stop
> echo Cleaning up old cluster directory
> sudo rm -rf $pg_path
> 
> echo Starting base backup as replicator
> sudo pg_basebackup -h $master -p $port -D $pg_path -U $user -x -v -P
> 
> echo Writing recovery.conf file
> sudo bash -c "cat > $pg_path/recovery.conf <<- _EOF1_
>   standby_mode = 'on'
>   primary_conninfo = 'host=$master port=$port user=$user
>   password=$userpwd sslmode=off'
>   trigger_file = '$triggerfile'
> _EOF1_
> "
> 
> sudo /bin/chown -R $pg_user:$pg_user $pg_path
> 
> echo Starting PostgreSQL
> sudo service postgresql start
> 
> In MASTER and STANDBY pool_hba.conf I add
> 
> host    all         all         192.168.10.0/24       trust
> 
> In MASTER and STANDBY pcp.conf I add
> 
> # USERID:MD5PASSWD
> postgres:<md5 password>
> 
> So far so good, running this script does the job, replication and load
> balancing are working as expected.
> 
> My questions now are:
> 
> - how pgpool knows that standby database is no more synchronized with
> - master one ?

Pgpool-II checks replication delay every sr_check_period second by
calling pg_last_xlog_replay_location() on primary and
pg_last_xlog_replay_location() on standby, and compares the results.

> - can I put my script in recovery_1st_stage_command =
> - '/usr/local/bin/<myscript>' and recovery_2nd_stage_command = 'bash -c
> - "exit 0"' from standby host (see attached gppool.conf) for the
> - recovery?

First, you can leave recovery_2nd_stage_command as default. You don't
need it in streaming replication mode.

Probably your script works as a recovery_1st_stage_command. However I
recommend you to get primary server address etc. from the arguments
passed by pgpool-II when the script is invoked by pgpool-II, rather
than having fixed values. Otherwise recovery won't work if primary
server is changed.

> - what happend when MASTER goes down. STANDBY is going MASTER satrting
> - to do the job and writing DB. So when old MASTER comes back, how is
> - the replication done ? Should I install my recovery script as well on
> - MASTER and setup recovery stuff in MASTER?

Yes. That's why I recommend you to get necessary parameters from
arguments, rather than fixed values. Otherwise you need to modify the
script each time primary server is changed.

> Thanks for your help, I know I'm just near my perfect setup and this
> part is the one that let me some doubt.
> 
> Daniel
> 
> -- 
> TOOTAi Networks
>