Greetings,
We have several web applications with Pg 8.2.x running on isolated servers (~25). The database size on each machines (du -h pgdata) is ~2 GB. We have been using nightly filesystem backup (stop pg, tar backup to ftp, start pg) and it worked well.
We would like to move to PITR backups since the database size will increase moving forward and our current backup method might increase server downtimes.
We have a central ftp backup server (yes, ftp :-) which we would like to use for weekly full and daily incremental PITR backups.
After reading the docs, PITR is still fuzzy. Our ideas for backup are (do not worry about the syntax),
** START **
tmpwal = "/localhost/tmp" # tmp space on server 1 for storing wal files before ftp
Configure $pgdata/postgresql.conf archive_command = "cp %p $tmpwal/%f"
Day 1:
% psql pg_start_backup(); tar pgdata.tar --exclude pg_xlog/ pgdata
% psql pg_stop_backup()
% ftp put pgdata.tar ftpserver:/server1/day1/pgdata
% ftp put $tmpwal/* ftpserver:/server1/day1/wal
% rm -f $tmpwal/* pgdata.tar
Day 2:
% ftp put $tmpwal/* ftpserver:/server1/day2/wal
% rm -f $tmpwal/*
Day 3:
...
...
Day 7:
% rm -f $tmpwal/*
Start over
Recovery on server1 (skeleton commands),
% rm -f $tmpwal/*
% mv pgdata pgdata.hosed
% ftp get ftpbackup:/server1/day1/pgdata
.tar .
% tar -xvf pgdata.tar
% ftp get ftpbackup:/server1/day1/wal/* $tmpwal
% ftp get ftpbackup:/server1/day2/wal/* $tmpwal
.....
.....
% cp -r pgdata.hosed/pg_xlog pgdata/
% echo "cp $tmpwal/%f %p" > pgdata/recovery.conf
% start pg (recovery begins)
** END **
Assumptions:
a. After pg_stop_backup(), Pg immediately recycles log files and hence wal logs can be copied to backup. This is a clean start.
b. New wal files since (a) are incremental backups
We are not sure if WAL log filenames are unique and possibly overwrite older wal files during recovery.
I'm seeking suggestions from others with experience performing PostgreSQL PITR backups from multiple servers to a central backup server.
Thanks in advance,
Joey Krane
