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 [EMAIL PROTECTED]
