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

[Daniel Huhardeaux]

Le 05/08/2016 à 15:33, Tatsuo Ishii a écrit :
>> 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.

I have a new question: the recovery script will get executed on the 1st 
stage of the master node or will be the 1st stage of a recovery node be 
executed as well ?

In the case of my above script, it is build to be executed on 1st stage 
of a node to recover as it delete the old local data path... no place 
for a mistake ;)

Thanks for your support

Daniel
-- 
TOOTAi Networks