[pgpool-general: 1587] Re: WAL files for online recovery.

Wolf Schwurack wolf at uen.org
Wed Apr 10 03:20:16 JST 2013

You only need WAL files from your last backup. I backup my 8.4 database daily and just depending on the database usage on how many WAL files are created in a day.


From: pgpool-general-bounces at pgpool.net [mailto:pgpool-general-bounces at pgpool.net] On Behalf Of Daniele Di Vito
Sent: Tuesday, April 09, 2013 11:56 AM
To: pgpool-general at pgpool.net
Subject: [pgpool-general: 1586] WAL files for online recovery.

I'm using pgpool-II 3.2.3 in recovery mode with 2 backend node cause my aim is to have a real-time replication of the primary database (bldlam server in the example is the primary and devlam0 is the backup server).
I do not use the streaming replication mode cause I'm using the 8.3 postgresql version.
Pgpool is working fine and i tested many times the online recovery using a few WAL files (10-15) and all seems to be working fine but I have a doubt and I would like to avoid any trouble in future.
Anyone knows if there is a way to know how many WAL files are needed to complete an online recovery after a node failover?

I ask it cause the performances of the server I use are really limited and if it's possible I would prefer not to archive all the wall files.

I suppose that WAL files created after "select pg_start_backup('pgpool-recovery');" can be enough

this is what my 1st and 2nd stage commands do:
1st stage

#! /bin/sh
##let's begin recovery mode
/usr/local/pgsql-test/bin/psql -p 5445 -c "select pg_start_backup('pgpool-recovery');" postgres
#let's set the restore command
echo "restore_command = 'cp /home/postgres/exchange_bldlam/wal/%f %p'" > /usr/local/pgsql-test/data/recovery.conf
echo "compressing base folder"
#tar -C /usr/local/pgsql-test/data -zcf pgsql.tar.gz base global pg_clog pg_multixact pg_subtrans pg_tblspc pg_twophase pg_xlog recovery.conf
tar -cvf pgsql.tar.gz -C /usr/local/pgsql-test/data base global pg_clog pg_multixact pg_subtrans pg_tblspc pg_twophase pg_xlog recovery.conf
echo "stopping backup mode"
/usr/local/pgsql-test/bin/psql -p 5445 -c "select pg_stop_backup();" postgres
echo "sending base data to devlam0"
scp pgsql.tar.gz devlam0:/usr/local/bldlam/pgsql/data

2nd stage

echo "executing pg_switch_xlog"
/usr/local/pgsql-test/bin/psql -p 5445 -c "SELECT pg_switch_xlog();" postgres
echo "compressing wal files to send"
cd  /usr/local/pgsql-test/archive_wal
tar -cvf wal.tar.gz -C /usr/local/pgsql-test/archive_wal *
#inviamoli all'atro server pnella cartella di exchange
echo "sending wal archive to devlam0"
rsync /usr/local/pgsql-test/archive_wal/wal.tar.gz postgres at devlam0:/home/postgres/exchange_bldlam/wal/wal.tar.gz<mailto:postgres at devlam0:/home/postgres/exchange_bldlam/wal/wal.tar.gz> </dev/null

Thanks in advance

Daniele Di Vito
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130409/76c31c31/attachment.html>

More information about the pgpool-general mailing list