Re: Backup and failover process

Is this a correct understanding?

When restoring using archiving, it is only possible to restore to a
database using WAL files if the database you are restoring to was
created before the first WAL file you wish to apply was created.

So based on the above, if one were to create a backup of a database
using pg_dump and then at a later time restore the database using the
SQL backup, any WAL files that were created after the pg_dump was
created would not work because the database would see itself as existing
after the WAL files.

Is this correct?

-----Original Message-----
From: Evan Rempel [mailto:erempel(at)uvic(dot)ca]
Sent: Tuesday, July 15, 2008 9:46 PM
To: Campbell, Lance
Subject: Re: [ADMIN] Backup and failover process

You can not mix WAL recovery/restore and pg_dump restores. To restore a
pg_dump, you
require a fully functioning postgresql server, which makes its own WAL
files. After the
restore of the pg_dump, you can not interject the WAL archive files.

The WAL archive files can only be used to roll-forward from known
checkpoints (known by
the "recovery mode" internal to postgresql.

You can use pg_dump to get specific snapshots of databases, but you can
not roll transactions
forward from the pg_dump using WAL files.

We use pg_dump to get daily snapshots of databases so that if a user
accidentally
breaks their database, we can upon request, restore to the previous
nights backup.

We also use filesystem backups and WAL files to allow us to recover to
any point in
time given a disaster.

It would be VERY nice to use filesystem backups and WAL files to recover
a SINGLE database,
but that is currently on my wish list.

Evan Rempel.

Campbell, Lance wrote:
> Kevin,
> I have read this documentation. I still does not answer my basic
> question. What happens if you take an SQL snapshot of a database
while
> creating WAL archives then later restore from that SQL snapshot and
> apply those WAL files? Will there be a problem if the transactions
> within the newest WAL file after the SQL snapshot was taken cause
> problems when they are applied? I would assume yes but I wanted to
> check if there was some type of timestamp that would prevent an issue
> from occurring?
>
> Thanks,
>
> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
> Sent: Tuesday, July 15, 2008 12:24 PM
> To: Campbell, Lance; pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] Backup and failover process
>
>>>> "Campbell, Lance" <lance(at)illinois(dot)edu> wrote:
>> PostgreSQL: 8.2
>> I am about to change my backup and failover procedure from dumping a
> full
>> file SQL dump of our data every so many minutes
>
> You're currently running pg_dump every so many minutes?
>
>> to using WAL files.
>
> Be sure you have read (and understand) this section of the docs:
>
> http://www.postgresql.org/docs/8.2/interactive/backup.html
>
> -Kevin
>