I have one postgres 9.1 master and 2 slaves setup with streaming replication with WAL archive.
Now, I am trying to set up for PITR for at least a window of 1 week. This is to cover for any administrative errors (like dropping an important table by mistake). Here is what I understand of PITR
- We need a base backup of the database at a known point in time.
- We need a WAL archive from the time of base backup to the point of error.
Using the above 2 we can rebuild the database to any point, in between.
Database involved is large, approx 1TB, growing at 30 GB a week
I want to understand what is the best way to achieve fast base backup creation and low performance overhead on the running instances. I can think of following alternatives but not sure
- if there are any pros and cons to each
if there are other better alternatives
Is it better to take a base backup once every week and accumulate WAL until the next base backup OR take just one base backup (say now) and backup only WAL week-after-week.
In case we do base backup every week, is it better to use pg_basebackup or pg_start_backup + rsync + pg_stop_backup. Will the second option be significantly faster if I use the same destination disk/volume/folder week-after-week, because of rsync ?
Is it better to run base backup against the master or is it possible to take a disk snapshot of the postgres cluster after stopping one of the slaves temporarily and resuming the replication after the snapshot. Will this snapshot be usable to setup an instance in recovery mode in case I want to do PITR or will there be any data consistency or time line issues ?
Thanks for any insights.