Re: PostgreSQL Point In Time Recovery

On 10/23/2013 9:10 PM, Jayadevan M wrote:
> I went through
> http://www.postgresql.org/docs/9.3/static/continuous-archiving.html
> and set up the archiving process. With this approach, if my database
> crashes after a couple of weeks after the base backup is taken,
> recovering would mean replaying the WAL logs for about 2 weeks, right?
> To avoid that, what is the standard process followed - take a base
> backup every day or once a week?

before you decide on what interval is appropriate for new base backups,
its probably a good idea to test how long the restore actually takes on
your hardware, with your WAL volume... then decide. restoring the
base backup files takes some time, then playing the WAL takes more time,
you need to decide what your acceptable tolerance for restore time is,
and decide on backup intervals appropriately.

I like to keep two sets of base backups with their WAL logs, so I wait
til the newest is done before purging the one done two intervals ago.

--
john r pierce 37N 122W
somewhere on the middle of the left coast