Re: Backup "Best Practices" - Mailing list pgsql-general

On Wed, Nov 23, 2016 at 10:16 AM, Israel Brewster <israel@ravnalaska.net> wrote:

I was wondering if someone could inform me about, or point me to an online article about best practices for backing up a postgresql database cluster? At the moment, I have two servers running with streaming replication for failover purposes, and then I do nightly database dumps for recovery/backup purposes. However, I feel this approach is lacking in a couple areas I can think of:

- If a total failure occurs towards the end of the day, we could potentially loose a whole days worth of data.

Why wouldn't the streaming replica salvage that?  Are they expected to fail together?  Is the NFS share onto which you want to store your basebackup and WAL also expected to fail together with them?

 

Similar argument for user error - there is no way to "undo" a catastrophic user data error without going all the way back to the previous day

- Less-than-ideal recovery under some scenarios. Since each database in the cluster is dumped individually, this is good should only *one* database need to be restored, but could get tedious should the entire cluster need to be restored.

To mitigate these issues, I am thinking of supplementing the individual dumps with a full base backup and WAL archiving to a NFS share. This should enable (relatively) quick/easy recovery from backup, plus the ability to do PIT Recovery. I do have a few questions with this approach, however:

- How do I figure out how often I should take a full base backup? I know this will depend on the amount of traffic my database is doing, and how long I am willing to wait for WAL files to be replayed - the more WAL files needing replayed, the longer recovery will take - but is there some rule of thumb that I can use to calculate how often I need a new base backup? Perhaps based on the number of WAL files?

You have to try it and see.  Different types of wal records will take different amounts of time to re-play, so there is no rule of thumb. It would depend on the type of traffic you have in your database.  And it could be limited by a single CPU, or by IO.  If the restore_command needs to restore the WAL from a remote server, it is very likely to be limited by the latency of doing that.  In fact, this is often the bottleneck even if it is restoring from the local server, at least if archival is often driven by archive_timeout.

When I need to re-clone production to get a fresh server to use for dev or testing, I do so using almost exactly the same method I would use for restoring production from a disaster (restore from most recent basebackup, then recovery from WAL archive).  So I know how long it takes for the recovery to happen based on true experience, and I take a new basebackup when that length of time starts to annoy me.

 

- What is the "best" (or just a good) method of keeping the WAL archives under control? Obviously when I do a new basebackup I can "cleanup" any old files that said backup doesn't need,

You have said you might be interested in doing PITR. So you want to delay the cleanup so as to not compromise that ability.  You need to develop a policy on how far back you want to be able to do a PITR.

 

but how do I know what those are?

pg_archivecleanup -n /mnt/server/archiverdir 000000010000000000000010.00000020.backup

 

- Should I be looking at any other backup methods in addition to/instead of the basebackup/WAL archive scheme?

You may want to consider pg_receivexlog to maintain your WAL archive, rather than archive_command.  That way you don't have to worry about the trades off caused by setting archive_timeout.  But unless you use it with a replication slot, it is not very safe as the pg_receivexlog could stop working and your database would happy run along without protection.  Also, it is hard to be sure you are reliably issuing an fsyncs over NFS, so with archive_command over NFS there is always the risk your WAL data is not actually reaching disk in a timely fashion.  So if you can run pg_receivexlog running on the NFS-host machine pointed to the local storage, not looping back over NFS, that is safer.

Cheers,

Jeff