Обсуждение: WAL backup
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». For the continuous archiving I do Modifying postgresql.conf with : archive_mode = on archive_command = 'cp -i %p /databases/Archives/%f </dev/null' restart postgresql after that I make a big backup: psql -c "select pg_start_backup('complete_backup');" rsync -av /databases/pgsql /databases/Archives psql -c "select pg_stop_backup();" 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» ? Regards. -- Albert SHIH SIO batiment 15 Observatoire de Paris Meudon 5 Place Jules Janssen 92195 Meudon Cedex Téléphone : 01 45 07 76 26/06 86 69 95 71 Heure local/Local time: Mar 28 jul 2009 09:23:23 CEST
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
Le 28/07/2009 à 06:05:07-0600, Greg Spiegelberg a écrit > 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. OK. Thanks you. At this moment I don't have tablespace ;-) > > 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. OK. For me that should be OK because my database don't have lot of modification in time. > > 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. > Thanks again for your help. Regards. -- Albert SHIH SIO batiment 15 Observatoire de Paris Meudon 5 Place Jules Janssen 92195 Meudon Cedex Téléphone : 01 45 07 76 26/06 86 69 95 71 Heure local/Local time: Mar 28 jul 2009 15:18:25 CEST