Re: Recovery/Restore and Roll Forward Question.

Bruce McAlister wrote:
> Hi All,
>
> Is it at all possible to "roll forward" a database with archive logs
> when it has been recovered using a dump?
>
> Assuming I have the archive_command and archive_timeout parameters set
> on our "live" system, then I follow these steps:
>
> [1] pg_dump -d database > /backup/database.dump,
> [2] initdb new instance on recovery machine,
> [3] psql -f ./database.dump,
> [4] shutdown new recovered db,
> [5] create recovery.conf,
> [6] copy WAL's from time of backup till time of recovery to temp dir
> [7] start postgresql

No. WALs track disk blocks not table-rows, so you need a file-level
backup of the original installation.

> In my mind I think I will have some problems somewhere along the way,
> however I don't know enough about the internals of PostgreSQL to
> actually see if there are additional steps I need to follow.
>
> In our environment it takes approx 2 hours to perform a PIT backup of
> our live system:
>
> [1] select pg_start_backup('labe;')
> [2] cpio & compress database directory (exclude wals)
> [3] select pg_stop_backup()
>
> However, if we perform a plain dump (pg_dump/pg_dumpall) we can dump the
> whole lot in 15 minutes. For us this is more efficient.

It sounds like there's something strange with your setup if it's quicker
for pg_dump to read your data than cp. Do you have *lots* of indexes, or
perhaps a lot of dead rows? What's the bottleneck with cpio+compress -
cpu/disk/network?

> The question is, how can we roll forward from our time of pg_dump, to
> our most recent WAL (in case of failure - touch wood).

Can't be done I'm afraid.

--
   Richard Huxton
   Archonet Ltd