Backup a large postgres database for PITR 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.
There are several ways of doing what you're wanting.

I would recommend using a specific solution such as WAL-E, barman, or OmniPITR to make the logistics of managing basebackups and WAL archives easier on yourself, or using pg_basebackup. Some of these tools, depending on the versions, will allow you to back up off your replica, which will reduce load on your primary server, and not push data out of the filesystem cache, which are all very good things.

And as you expected, there are definitely pros and cons with your approaches.

For your first question, you have to consider that if you take a single basebackup and just keep archiving WAL segments, the time to restore will grow longer with each WAL segment you archive, as it takes a significant amount of time restore each file. 
So that is not ideal, and most people tend to take a backup on a schedule that allows them to meet recovery requirements, and sometimes keep multiple basebackups as their storage allotments allow. 
Additionally, with a very, very, very large WAL archive, sometimes things can happen with individual WAL segments (which is why you always test your backups), and if a WAL segment fails to restore, no following segments will restore, and you're out of luck.

A general rule I've heard and use myself is if I take weekly basebackups, keeping around 3 weeks of WAL segments and 2 basebackups allows me enough peace of mind to have at least one known-good backup, and a known-good set of WAL, and a fairly reasonable restore time. I am a huge believer in testing the backups I take regularly.

For your second question, either one works well, though, pg_basebackup is easier to use and requires less of your own assembly. If you use rsync in your own script with start and stop basebackup, you have to check the exit code of every command. If you don't and a command fails, you can end up with a corrupt basebackup that's useless. The above mentioned tools, along with pg_basebackup, take care of all of those pedantic details for you.

For your third question, in some cases, yes, you can make a backup off of a replica, instead of the primary. If you can manage it, doing the basebackup and archiving off of a replica is the way to go, because taking a basebackup generates a lot of I/O, and it affects your OS filesystem cache. Unfortunately, it is not supported until 9.2+ with pg_basebackup, so you'll need to use an external tool such as OmniPITR or to upgrade your version of PostgreSQL. So long as you have the WAL to go with it, there shouldn't be any timeline issues or consistency issues with the snapshot from the replica.

Finally, a lot of your questions and best practices are covered in the following talk from Magnus Hagander at PgCon 2013: PostgreSQL Backup Strategies

It is well worth the time spent to watch the video.

Hope that helps. =)