Please have a look at my PITR and verify script....

Hello List,

i wrote a little and simple Script to do AND verify
Point-In-Time-Recovery automatically. Since i am not a Postgres or
Bash Guru, i would like to ask you to have a look at it. Just to check
if i made any logic mistake.

I use this script for about 2 Month now, and it seems to do its job
pretty well! If its cool, i could put it on sourceforge...if not, then
i will crawl back into my little dark admin room... :)

What do you think? Here we go...

Here is what i basically do in a nutshell:
===========================================
1.) On my Live Postgres Server:
1.1) Here I write a timestamp to the Database every second (crontab)
1.2) I do Base-Backups every 2nd Day (crontab) and transfer them to
the Standby-System with rsync
1.3) archive_command = '/usr/bin/rsync -a -F /data/pgsql/%p
pgsql@standbyserver.example.com:/restore/walfiles/'

2.) On my StandBy System:
2.1) Restore Postgres Database based on Base-Backup and WAL Files every 6h
2.2) Monitor the PITR with a self made Nagios Script. It checks how
old the last timestamp is which was written by the crontab in the
Live-System


Here is the whole magic in detail:
===================================
1.) On my Live Postgres Server:
1.1) Here I write a timestamp to the Database every second (crontab)
     * * * * * /bin/echo "INSERT INTO monitoring.stamps (stamp) VALUES
(NOW());" | /usr/local/bin/psql -U pgsql foo_dev
1.2) I do Base-Backups every 2nd Day (crontab) and transfer them to
the Standby-System with rsync
     0 4 */2 * * /root/backup-postgres.sh  (too big for this mail.
See: http://pastebin.com/f24f18c4d)
1.3) archive_command = '/usr/bin/rsync -a -F /data/pgsql/%p
pgsql@standbyserver.example.com:/restore/walfiles/'

2.) On my StandBy System:
2.1) Restore Postgres Database based on Base-Backup and WAL Files every 6h
     15 */6 * * * /usr/local/bin/restore-PITR --auto (too big for this
mail. See: http://pastebin.com/f524f7c5a)
2.2) Monitor the PITR with a self made Nagios Script. It checks how
old the last timestamp is which was written by the crontab in the
Live-System
     /usr/local/sbin/check_PITR_verify 24000  (too big for this mail.
See: http://pastebin.com/f6dc4fdcb)

3.) If the Standby System is down for some time, just rsync all the
wal files from the Live System to the Standby System, and thats it.
     rsync --progress --exclude=archive_status -avz
/data/pgsql/pg_xlog/*
pgsql@standbyserver.example.com:/restore/walfiles/

Cheers,
Mario


p.s. I know that there are many PITR script out there, but i found
none which was that simple (tar base files, rsync, thats it!) and
which i full understood. :)