Обсуждение: 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

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