Thread: Un successful Restoration of DATA using WAL files
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 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?) and then l remove both tables...(to check the validity of my backups) @ 12:43 I restore full backup with... psql -U postgres postgres < test.sql @ this stage I took the full backup @ 12:40 pm so table "student" is restored properly & I can see it. Now... I made recovery.conf with contents restore_command = 'copy c:/archivedir/%f "%p"' recovery_target_time = '2009-10-19 12:42:00' I put it in C:\Program Files\PostgreSQL\8.4\data I restart the server... recovery.conf is changed to recovery.done & getting the following log... 2009-10-19 18:41:04.744 IST,,,4060,,4adc6568.fdc,1,,2009-10-19 18:41:04 IST,,0,LOG,00000,"database system was shut down at 2009-10-19 18:40:43 IST",,,,,,,, 2009-10-19 18:41:04.744 IST,,,4060,,4adc6568.fdc,2,,2009-10-19 18:41:04 IST,,0,LOG,00000,"starting archive recovery",,,,,,,, 2009-10-19 18:41:04.744 IST,,,4060,,4adc6568.fdc,3,,2009-10-19 18:41:04 IST,,0,LOG,00000,"restore_command = 'copy c:/mit/%f ""%p""'",,,,,,,, 2009-10-19 18:41:04.744 IST,,,4060,,4adc6568.fdc,4,,2009-10-19 18:41:04 IST,,0,LOG,00000,"recovery_target_time = '2009-10-19 18:38:46+05:30'",,,,,,,, 2009-10-19 18:41:04.931 IST,,,4060,,4adc6568.fdc,5,,2009-10-19 18:41:04 IST,,0,LOG,00000,"automatic recovery in progress",,,,,,,, 2009-10-19 18:41:04.963 IST,,,4060,,4adc6568.fdc,6,,2009-10-19 18:41:04 IST,,0,LOG,00000,"record with zero length at 0/C8000068",,,,,,,, 2009-10-19 18:41:04.963 IST,,,4060,,4adc6568.fdc,7,,2009-10-19 18:41:04 IST,,0,LOG,00000,"redo is not required",,,,,,,, 2009-10-19 18:41:05.135 IST,,,4060,,4adc6568.fdc,8,,2009-10-19 18:41:04 IST,,0,LOG,00000,"selected new timeline ID: 10",,,,,,,, 2009-10-19 18:41:05.478 IST,,,6032,"",4adc6569.1790,1,"",2009-10-19 18:41:05 IST,,0,LOG,00000,"connection received: host=127.0.0.1 port=4383",,,,,,,, 2009-10-19 18:41:05.494 IST,"postgres","postgres",6032,"127.0.0.1:4383",4adc6569.1790,2,"",2009-10-19 18:41:05 IST,,0,FATAL,57P03,"the database system is starting up",,,,,,,, 2009-10-19 18:41:06.760 IST,,,5080,"",4adc656a.13d8,1,"",2009-10-19 18:41:06 IST,,0,LOG,00000,"connection received: host=127.0.0.1 port=4384",,,,,,,, 2009-10-19 18:41:06.775 IST,"postgres","postgres",5080,"127.0.0.1:4384",4adc656a.13d8,2,"",2009-10-19 18:41:06 IST,,0,FATAL,57P03,"the database system is starting up",,,,,,,, 2009-10-19 18:41:08.838 IST,,,4060,,4adc6568.fdc,9,,2009-10-19 18:41:04 IST,,0,LOG,00000,"archive recovery complete",,,,,,,, but I cant see the table "person" created again with the help of WAL file restoration :( Where I am wrong ?? -- View this message in context: http://www.nabble.com/Un-successful-Restoration-of-DATA-using-WAL-files-tp25959142p25959142.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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
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 (<b>not</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."
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 > (<b>not</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 <bruce@momjian.us> http://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 -0000 2.143 --- doc/src/sgml/backup.sgml 22 Feb 2010 17:14:02 -0000 *************** *** 484,489 **** --- 484,499 ---- </itemizedlist> </para> + <note> + <para> + <application>pg_dump</application> and + <application>pg_dumpall</application> do not produce file-system-level + backups and cannot be used as part of a continuous-archiving solution. + Such dumps are <emphasis>logical</> 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 <application>tar</> or <application>cpio</>. 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 <application>tar</> or <application>cpio</> (not ! <application>pg_dump</application> or ! <application>pg_dumpall</application>). It is neither necessary nor desirable to stop normal operation of the database while you do this. </para>