I have what I have sometimes called a 'tepid spare' backup. Once a week I copy the physical files over to another system (actually to two of them) and every few hours I make sure the archived WAL log files are in sync (using rsync.)
Anyway, here's the cookbook guide I wrote for updating one of the backup servers, which I have used several times, so I don't have to rediscover the steps each time. I think it has sufficient detail that someone other than me could follow them. Obviously it would have to be changed to apply to your file organization scheme. Enjoy! -- Mike Nolan How to restore the PostgreSQL low level backup tar files and WAL files to do a point-in-time recovery (PITR) files on a backup server. This note will explain how to perform a point-in-time recovery of the PostgreSQL database using the low-level backup files and the archived WAL (write-ahead log) files. (These steps should work on either server, with any differences in actual file locations dealt with using symbolic links.) The low level backup files and WAL log files should already be present on the backup servers. The low level tar files files are copied when they are created (usually early on a Tuesday morning each week) and the WAL log files are rsync'ed to both servers every few hours so that both backup servers should have files enabling them to be restored to a state that is no more than a few hours out of sync with the live database server. (In the future, we hope to keep one of the backup servers in full real-time synchronization with the live server, using a different technique.) The steps given below will generally take 3-5 hours to run, depending on how many archived log files need to be processed when the PostgreSQL server is restarted. These instructions assume some familiarity with Unix/Linux system administration tools, including the vi editor, and with database administration procedures, though not necessarily detailed knowledge of PostgreSQL. For more details, see chapter 23 of the PostgreSQL documentation, especially section 23.3. The documentation for version 8.2 is at http://www.postgresql.org/docs/8.2/static/backup.html 1. su to root on the backup server that the restore will be performed on 2. su to postgres (su - postgres) 3. Shut down the Postgresql server running on the backup server, if any pg_ctl stop (Use 'ps ax' to make sure the server is stopped.) 4. cd to /disk1/postgres/data and copy two files to /tmp cd /disk1/postgres/data cp pg_hba.conf /tmp cp recovery.done /tmp 5. Delete the entire contents of the /disk1/postgres/data directory tree. MAKE ABSOLUTELY SURE YOU ARE IN THE /disk1/postgres/data directory! cd /disk1/postgres/data pwd rm -rf * 6. Restore the tar file for the low-level backup from the live server tar xvf /usr/local/pgsql/tardir/pg_part1.tar (This restore may take 2-3 hours) 7. Remove the PostgreSQL log file and the WAL files that were restored from the tar archive rm log.out cd pg_xlog rm 00* (do not remove the archive_status subdirectory) 8. Copy the pg_hba.conf file back from the /tmp directory cd /disk1/postgres/data cp /tmp/pg_hba.conf . (This is necessary to keep this database server configured for restricted access. If more expansive access is needed the restored pg_hba.conf file may be more appropriate.) 9. Copy the recovery configuration file from the /tmp directory (changing the file name) cp /tmp/recovery.done recovery.conf (This file may be edited to do a point-in-time recovery other than to the end of the PITR log files, see the PostgreSQL documentation for details.) 10. Delete the entire contents of the /disk2/postgres/data directory tree. MAKE ABSOLUTELY SURE YOU ARE IN THE /disk2/postgres/data directory! cd /disk2/postgres/data pwd rm -rf * 11. Restore the 2nd tar archive tar xvf /usr/local/pgsql/tardir/pg_part2.tar (This restore may take around a half hour) 12. Go to the directory where the WAL files have been archived on the server and remove all files older than the file matching the last .backup file. The fastest way to do this is as follows: cd /usr/local/pgsql/archivedir ls -1 > files This will produce a file listing all files in the archivedir directory. Now edit that file (using vi) to take out all lines after the latest 'backup' file (including the file just ahead of that backup file, since it is actually the first file that needs to be processed. (Note: These ones after the latest backup file are the files we're NOT going to delete, so we don't want those file names in this text file.) For example, the directory list may look like this: 00000001000000960000000A 00000001000000960000000B 00000001000000960000000C 00000001000000960000000C.004027C0.backup 00000001000000960000000D There will generally be several files with the name ending in 'backup', find the most recent one (ie, the last one in the file.) In this case, the fie with the name ending in '000C' needs to be restored, along with all subsequent files, so in this case we would delete all lines from the one ending in '000C' to the end of the edit buffer, then edit the lines to change the lines into 'rm' commands, ie, something like this: rm 00000001000000960000000A rm 00000001000000960000000B Save the edited file, then execute it sh -x files 13. You are now ready to restart PostgreSQL and have it process all the archived log files: pg_ctl -l /usr/local/pgsql/data/log.out start 14. While the WAL files are being processed, a 'ps -ax' display will have lines in it that look something like this: 28039 pts/0 S 0:00 /disk1/postgres/pgsql/bin/postgres 28040 ? Ds 0:00 postgres: startup process You can also look at the /usr/local/pgsql/data/log.out file to see which WAL file it is currently processing: tail /usr/local/pgsql/data/log.out Once all the WAL files have been processed (which could take an hour or longer, depending on how many WAL files need to be processed, 'ps -ax' will look more like this: 28039 pts/0 S 0:00 /disk1/postgres/pgsql/bin/postgres 28310 ? Ss 0:00 postgres: writer process 28311 ? Ss 0:00 postgres: archiver process 28312 ? Ss 0:00 postgres: stats collector process Once this happens, the backup database server is ready for use. However, it will only be as current as the most recent WAL file that was processed, and in order to bring it to a more current state all of the steps given above have to be performed again.
