On 11/23/2016 10:16 AM, Israel Brewster wrote:
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:
if you do the base backup periodically (daily? weekly? monthly? kinda depends on your workload, data volume, and available space on your WAL archive server)
- 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?
frequency of doing this is entirely dependent on your data volume, how long the backups take, and your tolerance for restore times. wal archive recoveries are generally quite fast, but of course if there's millions of wal files to recover, that would take a long time. note, too, 9.6 has some major performance enhancements in how checkpoints are written, which should speed things up overall.
- 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, but how do I know what those are? - Should I be looking at any other backup methods in addition to/instead of the basebackup/WAL archive scheme?
I would keep at least 2 base backups and all wal files since the start of the oldest base backup. when you create a new base backup, delete the oldest, and all wal archive files up to the start of the following one. the reason for keeping at least two is so you always have a recovery point if something catastrophic happens during the backup process.
note that having base backups plus wal archives allows PiTR too, point in time recovery. say you discover a bad script updated a ton of bad data into your database last tuesday at 3pm. you can restore the preceding base backup, then recover up to tuesday just before this event.
-- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
