Backup and failover process

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

My e-mail address has changed to lance@xxxxxxxxxxxx

 


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux