Re: WAL backup

On Tue, Jul 28, 2009 at 1:46 AM, Albert Shih <Albert(dot)Shih(at)obspm(dot)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