From: | ml ml <mliebherr99(at)googlemail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Please have a look at my PITR and verify script.... |
Date: | 2009-08-19 17:39:05 |
Message-ID: | bae225f0908191039g462829fh4cac1bb94879785@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(at)standbyserver(dot)example(dot)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(at)standbyserver(dot)example(dot)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(at)standbyserver(dot)example(dot)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. :)
From | Date | Subject | |
---|---|---|---|
Next Message | Dick Visser | 2009-08-19 19:23:22 | Background triggers? |
Previous Message | Scott Marlowe | 2009-08-19 16:32:21 | Re: vacuum on empty table takes very long |