Richard's Diary

Sunday, December 14, 2008

Restoring a PostgreSQL Backup If you look at pg_log you'll see that the first WAL file was recycled (since it was archived) to save disk space. That is, the 16MB file no longer exists but it's former presence is noted as a new file called "WALfile.startlocation.backup" that is just a few hundred bytes. The archive_status subdirectory shows the same filename.done and 0 bytes. The space is given to another WAL log. Because checkpoint_segments was lowered, the system keeps a few WAL logs (preallocated space) handy in anticipation of increased space usage. http://www.postgresql.org/docs/8.3/static/wal-configuration.html

As soon as the backup was done, a checkpoint was created to close out the old WAL log and start a new WAL log so that the backup DB and associated archived WAL logs are as fresh as possible.

Sample recovery file in /usr/share/postgresql/recovery.conf.sample
restore_command = 'cp /var/log/postgresqlwal/%f "%p"'
Use log_restartpoints=true
WAL segments that cannot be found in the archive will be sought in pg_xlog/; this allows use of recent un-archived segments. However segments that are available from the archive will be used in preference to files in pg_xlog/.
The server will go into recovery mode and then commence normal database operations.

I thought that would mean the WAL logs would be written to and corrupt them, which is why you want to make a backup of them BEFORE beginning recovery. Not true, just read about timelines: "Whenever an archive recovery is completed, a new timeline is created to identify the series of WAL records generated after that recovery. The timeline ID number is part of WAL segment file names, and so a new timeline does not overwrite the WAL data generated by previous timelines.")

© 2010 Picky Ricky, Inc. originalblog