Re: [GENERAL] Un successful Restoration of DATA using WAL files
Based on your suggestions, I have applied the attached patch to mention _not_ to use pg_dump or pg_dumpall in two places, and to briefly explain why. Thanks. --- Craig Ringer wrote: 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 Any comments from readers on the following suggestion of changes to the PITR docs at: http://www.postgresql.org/docs/current/interactive/continuous-archiving.html User misunderstandings on the pgsql-general mailing list suggest that a clear and prominent warning needs to be added to this page to prevent people from trying to combine a pg_dump base backup with WAL archiving. People are failing to understand that the base backup must be a file-system-level copy taken after calling pg_start_backup() . Suggested changes: maintains a write ahead log - maintains a block-level write ahead log in the first paragraph. we can combine a file-system-level backup - we can combine a file-system-level backup of the data directory (bnot/b a pg_dump backup) Also, somewhere in the introductory section, something like this would be good: IMPORTANT: WAL archiving and PITR cannot be used with an SQL-level base backup taken with pg_dump. See Making a Base Backup below for the correct method of backing up your database for WAL archiving and PITR. See Caveats for details. In Caveats: You can't use pg_dump to make a base backup, restore that to a different cluster or a new database in the original cluster, then apply archived WAL files to it. WAL archiving records a history of changes to the database cluster at a very low level, rather than recording anything like SQL commands. The WAL files essentially record sequences of writes of byte sequences to offsets within files in the cluster. A new cluster (or a new database created in the original cluster by CREATE DATABASE) will have a different layout in its files, so the WAL archives don't make any sense to apply to the new cluster. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/backup.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v retrieving revision 2.143 diff -c -c -r2.143 backup.sgml *** doc/src/sgml/backup.sgml 22 Feb 2010 11:47:30 - 2.143 --- doc/src/sgml/backup.sgml 22 Feb 2010 17:14:02 - *** *** 484,489 --- 484,499 /itemizedlist /para + note +para + applicationpg_dump/application and + applicationpg_dumpall/application do not produce file-system-level + backups and cannot be used as part of a continuous-archiving solution. + Such dumps are emphasislogical/ and do not contain enough + information to used by WAL reply. +/para + /note + para As with the plain file-system-backup technique, this method can only support restoration of an entire database cluster, not a subset. *** *** 744,750 listitem para Perform the backup, using any convenient file-system-backup tool ! such as applicationtar/ or applicationcpio/. It is neither necessary nor desirable to stop normal operation of the database while you do this. /para --- 754,762 listitem para Perform the backup, using any convenient file-system-backup tool ! such as applicationtar/ or applicationcpio/ (not ! applicationpg_dump/application or ! applicationpg_dumpall/application). It is neither necessary nor desirable to stop normal operation of the database while you do this. /para -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your
Re: [GENERAL] Un successful Restoration of DATA using WAL files
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Un successful Restoration of DATA using WAL files
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 Any comments from readers on the following suggestion of changes to the PITR docs at: http://www.postgresql.org/docs/current/interactive/continuous-archiving.html User misunderstandings on the pgsql-general mailing list suggest that a clear and prominent warning needs to be added to this page to prevent people from trying to combine a pg_dump base backup with WAL archiving. People are failing to understand that the base backup must be a file-system-level copy taken after calling pg_start_backup() . Suggested changes: maintains a write ahead log - maintains a block-level write ahead log in the first paragraph. we can combine a file-system-level backup - we can combine a file-system-level backup of the data directory (bnot/b a pg_dump backup) Also, somewhere in the introductory section, something like this would be good: IMPORTANT: WAL archiving and PITR cannot be used with an SQL-level base backup taken with pg_dump. See Making a Base Backup below for the correct method of backing up your database for WAL archiving and PITR. See Caveats for details. In Caveats: You can't use pg_dump to make a base backup, restore that to a different cluster or a new database in the original cluster, then apply archived WAL files to it. WAL archiving records a history of changes to the database cluster at a very low level, rather than recording anything like SQL commands. The WAL files essentially record sequences of writes of byte sequences to offsets within files in the cluster. A new cluster (or a new database created in the original cluster by CREATE DATABASE) will have a different layout in its files, so the WAL archives don't make any sense to apply to the new cluster. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
