BACKGROUND INFO BEGINS
Recently I had some questions about doing backups and received very helpful replies. I have now put together a BAT file
todo a routine backup, using pg_dumpall with the -g option to get the roles, and pg_dump with the custom format to get
allthe 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
catastrophicserver failure.
I should mention that I'm running PostgreSQL 8.2 on a Windows computer (Win2K for testing) and that I am developing and
testingthis procedure because I do software development for clients using PG at my recommendation. They are small
companieswho 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
thecatastrophe.
When the computer booted, the server started automatically, so I shut it down. I then renamed the data folder to
simulatecomplete loss of the computer, and I created a new cluster and database and did a restore of the roles and then
thedata. (My data had an invalid UTF8 character so, thanks to help given here, I extracted that table to plain text,
raniconv 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
thepretend crashed server, now in a renamed folder? I understand I create a recover.conf file and restart the server.
ButI don't understand the process. I am to put a copy command in the file to copy wal files from the renamed folder,
buthow does the server know what files to copy? Is something else needed in the recover.conf file? By the way, when I
restartedthe 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
interestedin the first question.
Thanks
John
