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@xxxxxxxxxxxxxxxxxxxxxxxxx:/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@xxxxxxxxxxxxxxxxxxxxxxxxx:/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@xxxxxxxxxxxxxxxxxxxxxxxxx:/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. :)
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
