Recently I had some questions about doing backups and received very helpful replies. I have now put together a BAT file to do a routine backup, using pg_dumpall with the -g option to get the roles, and pg_dump with the custom format to get all the data.
I am now testing this process to make sure it is possible to recover data up to the last minute in the event of a catastrophic server failure.
I should mention that I'm running PostgreSQL 8.2 on a Windows computer (Win2K for testing) and that I am developing and testing this procedure because I do software development for clients using PG at my recommendation. They are small companies who don't use Unix. In fact, the PG server is likely to be one of the office workstations.
To test, I ran my backup procedure, added a row to a table, then pressed reset on the workstation/server to simulate the catastrophe.
When the computer booted, the server started automatically, so I shut it down. I then renamed the data folder to simulate complete loss of the computer, and I created a new cluster and database and did a restore of the roles and then the data. (My data had an invalid UTF8 character so, thanks to help given here, I extracted that table to plain text, ran iconv on it, and loaded the problem table.)
So now I have everything back except that row I added after doing the backup.
BACKGROUND INFO ENDS, QUESTIONS BEGIN
How to replay WAL files: Now that I have a freshly created and reloaded database, how do I replay the WAL files from the pretend crashed server, now in a renamed folder? I understand I create a recover.conf file and restart the server. But I don't understand the process. I am to put a copy command in the file to copy wal files from the renamed folder, but how does the server know what files to copy? Is something else needed in the recover.conf file? By the way, when I restarted the server, the file was renamed to recover.done but the missing row didn't appear.
On Windows, is it possible to relocate the wal files to another hard drive?
Does pg_dump do a checkpoint?
How do I make sure the wal files are not discarded before another backup is done?
How to release wal files after the backup is done?
The first two questions I could find no answer to. The others I admit I haven't researched very long as I was more interested in the first question.
Thanks
John
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
No comments:
Post a Comment