[Pgpool-general] pgpool Online Recovery scripts example

[Marcelo Martins]

In regards to Tatsuo's suggestion I have modified the way I use the  
scripts that I have had previously shared with you guys.

Now, I no longer call a "recovery_2nd_stage_command". My pgpool.conf  
only shows recovery_2nd_stage_command = ' '
I have also modified the "copy-base-backup" and "pgpool_remote_start"  
scripts.


==========================  "copy-base-backup"   
====================================

#!/bin/bash

# Created: 10-14-2008
# Author: Marcelo
#
# Info:
#       Created under Debian-Etch systems
#       This is a Pgpool recovery script for stage one
#
#       Please note that PostgreSQL is not setup to perform WAL  
archiving
#       continously. It will only start WAL archiving when a trigger  
file is
#       created.
#
#       ARCHIVE COMMAND used by postgresql instances to trigger  
archiving
#       archive_command = 'test ! -f /var/lib/postgresql/8.3/ 
backup_in_progress
#       || rsync -a %p /var/lib/postgresql/8.3/wal_arc/%f'
#

# PGDATA path for master node
MASTER_PGDATA=$1

# Recovery node
RECV_NODE=$2

# PGDATA path for recovery node
RECV_PGDATA=$3

# clean up wal_arc directory
rm -f /var/lib/postgresql/8.3/wal_arc/*

# Clean existing WAL files on destination node
ssh -T $RECV_NODE 'find /var/lib/postgresql/8.3/main/pg_xlog/ -type f - 
exec rm -f {} \;'

# Create WAL archiving triger file
touch   /var/lib/postgresql/8.3/backup_in_progress


# start the backup
psql -c "SELECT pg_start_backup('pgpool-recovery-stg1')" postgres


# Perform an rsync of PGDATA from SRC node to DEST node

rsync  -avh --log-file=/var/log/postgresql/pgpool-recovery-stg1.log  -- 
delete -e ssh --exclude pg_xlog --exclude recovery.conf --exclude  
postmaster.opts
   --exclude postmaster.pid  --exclude root.crt --exclude server.crt -- 
exclude server.key $MASTER_PGDATA/  $RECV_NODE:$RECV_PGDATA/


# Stop the backup
psql -c "SELECT pg_stop_backup()" postgres


# Creating a recovery file for the RECOVERY node
echo "restore_command = 'rsync -pog $HOSTNAME:/var/lib/postgresql/8.3/ 
wal_arc/%f %p'" > /tmp/recovery.conf
scp /tmp/recovery.conf $RECV_NODE:/var/lib/postgresql/8.3/main/ 
recovery.conf 2>/dev/null 1>/dev/null

rm -f /tmp/recovery.conf

exit 0

==========================  "copy-base-backup"   
====================================


==========================  "pgpool_remote_start"   
====================================

#!/bin/bash

# Created: 10-14-2008
# Author: Marcelo
#
# Info:
#       Created under Debian-Etch systems
#       This is a Pgpool remote start script used for the end of the
#       online-recovery process
#
#       Please note that PostgreSQL is not setup to perform WAL  
archiving
#       continously. It will only start WAL archiving when a trigger  
file is
#       created. The trigger file was created at start of stage one.  
The trigger
#       file is removed by this script.
#
#       ARCHIVE COMMAND used by postgresql instances to trigger  
archiving
#       archive_command = 'test ! -f /var/lib/postgresql/8.3/ 
backup_in_progress
#       || rsync -a %p /var/lib/postgresql/8.3/wal_arc/%f'
#
#       NOTE:
#          I have a call to "pg_switch_xlog" because I was noticing less
#          data once the recovered node which appeared to be due to  
not all WAL
#          files being archived during high traffic of writes.
#

REMOTE_NODE=$1
REMOTE_PGDATA=$2


# Switch xlog
psql -c "select pg_switch_xlog()" postgres


sleep 5
# Remove WAL archiving trigger file
rm -f /var/lib/postgresql/8.3/backup_in_progress


# Start Postgres service on remote host
ssh -T $REMOTE_NODE /etc/init.d/postgresql-8.3 start 2>/dev/null 1>/ 
dev/null  < /dev/null

==========================  "pgpool_remote_start"   
====================================


Marcelo
PostgreSQL DBA
Linux/Solaris System Administrator
http://www.zeroaccess.org

On Dec 16, 2008, at 2:02 AM, Tatsuo Ishii wrote:

>> These have been tested under PostgreSQL 8.3.X versions
>> I have also tested these under stable version of pgpool and also the
>> latest CVS version
>> I'm sure there are things that can be improved/changed so feel free  
>> to
>> share that.
>
> Thanks. Small issues I saw in your script so far:
>
> 1) In copy-base-backup, you touch backup_in_progress file right after
>   pg_stop_backup() executed to start actual WAL archiving. I think
>   this is too late since while doing the base backup (rsync), WAL
>   segment files might be generated if there's enough updation to the
>   database. You need to start WAL archiving *before* doing base
>   backup.
>
> 2) In pgpool_recovery_pitr, you do base backup again. I think this is
>   not neccessary if you sends WAL segment files to the recovery
>   target node generated before statge two starts and will greatly
>   reduce the duration of state two.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan