Backup a large postgres database for PITR

Question

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

  1. 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.

  2. 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 ?

  3. 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.

Answer 1

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. =)

Answer 2

A database in the realm of 1TB, growing by > 300GB/year strongly suggests a backup strategy using rsync. Similarly pgBackRest achieves high efficiency by copying new or modified files.

In he scenario you describe I would also recommend implementing delayed replication by setting recovery_min_apply_delay on one of the standby servers. This gives you the ability to recover from administrative errors without having to worry about the cost of taking snapshots or verifying the consistency of transaction logs.