Thread: Backup Strategy Advise
Hi: I need some advise on how best to backup a PG DB.
PG 9.5.2 on RHEL6
The requirement is to be able to restore the DB after catastrophic failure and lose no more than the last 15 minutes worth of data. Also, we would like to be able to do the backups on-line (no down time). There is no need for PITR other than the point in time being the latest possible.
Typically, I would think doing a weekly full backup, daily incremental backups and turn on journaling to capture what goes on since the last backup. When DB recovery is needed, restore up to the last daily, then reply the journal to restore up to the last time the journal was flushed to disk (checkpoint=15 minutes). I'm not quite sure if something like this is possible with PG. I've read about the WAL file and wonder if it could be used together with the on-line logical backups (pg_dump) to achieve the 15 minute requirement without needing downtime for physical backups..
Any advise?
Thanks in Advance.
On 04/24/2018 07:50 AM, David Gauthier wrote: > Hi: I need some advise on how best to backup a PG DB. > PG 9.5.2 on RHEL6 > > The requirement is to be able to restore the DB after catastrophic > failure and lose no more than the last 15 minutes worth of data. Also, > we would like to be able to do the backups on-line (no down time). > There is no need for PITR other than the point in time being the latest > possible. > > Typically, I would think doing a weekly full backup, daily incremental > backups and turn on journaling to capture what goes on since the last > backup. When DB recovery is needed, restore up to the last daily, then > reply the journal to restore up to the last time the journal was flushed > to disk (checkpoint=15 minutes). I'm not quite sure if something like > this is possible with PG. I've read about the WAL file and wonder if it Pretty sure that won't work. Once you do the restore from the last daily you will change the Postgres files to a state different from the ones captured by the journaling. Replaying the journal will result in a mixture of old and new. > could be used together with the on-line logical backups (pg_dump) to > achieve the 15 minute requirement without needing downtime for physical > backups.. > > Any advise? Take a look here: https://www.postgresql.org/docs/9.5/static/continuous-archiving.html > > Thanks in Advance. -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Apr 24, 2018 at 10:50 AM, David Gauthier <davegauthierpg@gmail.com> wrote:
Typically, I would think doing a weekly full backup, daily incremental backups and turn on journaling to capture what goes on since the last backup.
This is almost the whole concept of the streaming replication built into postgres, except you are not applying the stream but archiving it. If you have atomic file system snapshots, you can implement this strategy along the lines of marking the DB snapshot for binary backup, snapshot the file system, then copy that snapshot file system off to another system (locally or off-site), meanwhile you accumulate the log files just as you would for streaming replication. Once the copy is done, you can release the file system snapshot and continue to archive the logs similarly to how you would send them to a remote system for being applied. You just don't apply them until you need to do the recovery.
Or just set up streaming replication to a hot-standby, because that's the right thing to do. For over a decade I did this with twin servers and slony1 replication. The cost of the duplicate hardware was nothing compared to not having downtime.