PostgreSQL: 8.2
I am about to change my backup and failover procedure from dumping a full file SQL dump of our data every so many minutes to using WAL files. Could someone review the below strategy to identify if this strategy has any issues?
1) On the primary server, all WAL files will be written to a backup directory. Once a night I will delete all of the WAL files on the primary server from the backup directory. I will create a full file SQL dump of the database and put it into the same backup folder that the WAL files are put in. The backup directory will be rsynced to the failover server. This will cause the failover server to delete all of the WAL files it has copies of each night.
2) On the primary server, I will then check periodically with cron during the day to see if there is a new WAL file. If there is a new WAL file I will then copy it to the fail over server.
3) At the end of the day I will repeat step #1.
In the event of a failure a script is ran that converts the failover server to the primary server. After starting PostgreSQL the server would load the full file SQL dump. The server would then apply all of the WAL files it has in the backup directory.
Is there any problems with the process I am considering? My only concern is in step one. If I create a full file SQL dump how do I know that some of the transactions have not already been applied by the first WAL file that is created each night? What will happen if I try to restore from the first WAL file? Will PostgreSQL some how know that some of the transactions have already been applied from the first WAL file? Will it just ignore those transactions? Or will PostgreSQL just fail to reload the WAL file?
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu <http://webservices.uiuc.edu>
My e-mail address has changed to lance(at)illinois(dot)edu