On Tue, Jul 28, 2009 at 1:46 AM, Albert Shih
<Albert.Shih@obspm.fr> wrote:
Hi all
I'm not sure I understand
http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html
My problem:
I've huge database ~ 1To (soon 2 To) and I need backup.
I can use pgdump because it's too long to do the backup.
So I like to use «continous-archiving».
*SNIP*
before this moment I use «continuous-archiving» by put in my crontab
something like (every hours)
rsync -av /databases/pgsql /databases/Archives
is that correct ?
And more important : How long I can keep this the rsync ? I mean after how
long it's good to make a new «big backup» ?
Up to the rsync in your crontab your configuration looked good. Lose the cron'd rsync.
The first rsync executed between pg_start_backup() and pg_stop_backup() creates the baseline / "full" backup of the database. Be sure you get tablespace directories outside of the default PGDATA! The archive_command adds the WAL logs as needed after and are the "incrementals".
That's all you need for the backups.
How often do you need to create the baseline? Depends how many WAL logs are being created and how quickly you want the recovery to be. I have a 800 GB database and in one week over 4,000 WAL logs are created. This database setup takes about 16-24 hours to recover. but at least 8 hours of that is restoring the "full" and the remainder is applying the WAL logs.
FWIW, I send all backup data (baseline & archived WALs) to a server which is responsible for backing it up to tape. This allows the database server to be a database server without busying itself with actual backups. The standby server, eventually, will be used as a warm standby in case the primary crashes.
Greg