Seeking datacenter PITR backup procedures [RESENDING]

From: "Joey K(dot)" <pguser(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Seeking datacenter PITR backup procedures [RESENDING]
Date: 2007-08-19 04:13:30
Message-ID: 467669b30708182113i459eb7bco998ca3dc98c1c31e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guy Rouillier 2007-08-19 04:19:45 Re: Writing most code in Stored Procedures
Previous Message Ron Johnson 2007-08-19 02:37:32 Re: Automating logins for mundane chores