Re: Un successful Restoration of DATA using WAL files - Mailing list pgsql-general

On Mon, 2009-10-19 at 07:18 -0700, Mitesh51 wrote:
> I am unable to restore data with the use of WAL files by following procedure.
>
> I have done following changes in postgres.conf to enable WAL archiving...
>
> archive_mode = on        # allows archiving to be done
> archive_command = 'copy "%p" "C:\\archivedir\\%f"'
>
> I have one database(built in) postgres. I create one table student in it.
> and take full backup(only of a single database & I am not copying complete
> data dir..) @ 12:40 pm with the
>
> pg_dump.exe -h localhost -p 5432 -U postgres -f C:\test.sql postgres

Whoops. You can't combine a pg_dump -style base backup with WAL
archiving. You *MUST* use the pg_start_backup() and pg_stop_backup()
functions combined with a file-system level copy of the database
directory.

The reason the WAL files can't just be applied to a database restored
from pg_dump is that they're block-level logs of write activity. A newly
restored database from a pg_dump backup will have a different block
layout, so the WAL files make no sense with the newly restored database.
Additionally, you're probably restoring the database to a different
cluster, which the WAL files won't make sense for.

It's a really, REALLY good thing you're smart enough to test your
backups before relying on them :-)

I strongly suggest re-reading the PITR backup documentation.

Personally, I recommend taking a periodic dump backup with pg_dump to
protect yourself against undetected corruption of the catalog or table
files, which PITR-based backups won't help you with. There's nothing
like discovering that your backup copies of your cluster are corrupt
too :-(

In *addition* to the pg_dump backusp, enable WAL archiving and take a
PITR base backup (using pg_start_backup() and pg_stop_backup() as per
the PITR docs). That way if you have a failure such as an accidental
"DROP DATABASE" you can recover your cluster up to a few minutes before
the mistake. You'll want to periodically take a new base backup so that
you're not faced with storing terabytes of WAL archives ... and the days
or weeks it could take to replay those WAL archives when you need to
restore the backup. How often depends on your write load - how fast your
WAL archives build up.

> After taking full backup...
>
> I create 1 table named "person" @ 12:41 pm  in the postgres database(for
> testing purpose only).
> (Now it should be recorded in WAL files...Am I write here?)

Sort-of. The block writes to the catalog, the table layout, etc are
indeed recorded in the WAL, but it's not recording SQL like "CREATE
TABLE person();", it's recording "bytes 0x0902ff12 written to offset
0x12312" or the like.

> but I cant see the table "person" created again with the help of WAL file
> restoration :(

Personally, I'm surprised you got anything but an error when trying
this. Perhaps it's not even seeing the WAL files from the old unrelated
cluster at all? If it did see them and try to use them I'd expect an
error reporting that the WAL archives don't make any sense for your
cluster.

--
Craig Ringer