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

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