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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
