Re: WAL recovery

On Wed, 22 Feb 2006, Andy Shellam wrote:

> However, if I delete my PG data directory, restore the same base backup from
> yesterday, and begin recovery, it recovers right up until the last log file,
> which the previous roll-forward attempt fails.
> The log files were fully archived off the live server to begin with so I
> can't see it's that they've changed or anything.
>
> Is this scenario possible - that you can keep rolling forward over log files
> as long as necessary, or do you always have to start from a base backup?
> Nothing is changing on the spare, it's literally a sitting duck.

You must always start with a base backup when you begin the restore otherwise
the system does not know where to begin the recovery.  What I have done in the
past on a similar system is keep the secondary DB shutdown and make base
backups as often as possible so the recovery time is suitably short when the
secondary is needed.  Also a good idea to do test recovers on occassion to
make sure you don't have any corrupt WAL files.  If you use rsync, then you
can make the base backup pretty quickly depending on how much of your DB
changes.

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954