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@xxxxxxx] 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.Grittner@xxxxxxxxxxxx] > Sent: Tuesday, July 15, 2008 12:24 PM > To: Campbell, Lance; pgsql-admin@xxxxxxxxxxxxxx > Subject: Re: [ADMIN] Backup and failover process > >>>> "Campbell, Lance" <lance@xxxxxxxxxxxx> 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 >
