Обсуждение: How to manage WAL

Hi all

I would like to known what's best way to manage the WAL.

Let's me explain :

    at beginning I do something like

        psql -c "select pg_start_backup('complete');"
        rsync /pgsql backup_server:
        psql -c "select pg_stop_backup();"

and I backup /WAL directory but after sometime the /WAL directory
become big. So can I do something like

    postgresql stop
    rm /WAL/*
    postgresql start
    psql -c "select pg_start_backup('complete');"
    rsync /pgsql backup_server:
    psql -c "select pg_stop_backup();"

and continu to backup /WAL ?

Regards.

JAS

--
Albert SHIH
SIO batiment 15
Observatoire de Paris Meudon
5 Place Jules Janssen
92195 Meudon Cedex
Heure local/Local time:
Jeu 11 fév 2010 23:28:31 CET

Albert Shih <Albert.Shih@obspm.fr> wrote:

> I would like to known what's best way to manage the WAL.

You should design a policy which meets your particular needs.  We
keep two PITR base backups and all the WAL files from before the
start of the first (based on the information in the backup file)
through current.  We also save monthly archival PITR base backups
with just the WAL files needed to restore them (again, based on the
backup files).  Your needs may be different.

-Kevin

 Le 11/02/2010 à 17:00:33-0600, Kevin Grittner a écrit
> Albert Shih <Albert.Shih@obspm.fr> wrote:
>
> > I would like to known what's best way to manage the WAL.
>
> You should design a policy which meets your particular needs.  We
> keep two PITR base backups and all the WAL files from before the
> start of the first (based on the information in the backup file)
> through current.  We also save monthly archival PITR base backups
> with just the WAL files needed to restore them (again, based on the
> backup files).  Your needs may be different.

Hum...I'm not sure I really understand what you doing.

Maybe I'm doing everything wrong but this is what I did :

    t=0      SELECT pg_start_backup('label');
        rsync /pgsql backup_server:/
        SELECT pg_stop_backup();

    t> 1     cron with rsync /WAL backup_server:

but after some time the /WAL become big. So is it a solution to
stop the database, delete everything in /WAL and begin a new cycle (t=0 select etc...) ?

Regards.

JAS


--
Albert SHIH
SIO batiment 15
Observatoire de Paris Meudon
5 Place Jules Janssen
92195 Meudon Cedex
Heure local/Local time:
Ven 12 fév 2010 00:28:29 CET

Albert Shih wrote:
>  Le 11/02/2010 à 17:00:33-0600, Kevin Grittner a écrit
> > Albert Shih <Albert.Shih@obspm.fr> wrote:
> >
> > > I would like to known what's best way to manage the WAL.
> >
> > You should design a policy which meets your particular needs.  We
> > keep two PITR base backups and all the WAL files from before the
> > start of the first (based on the information in the backup file)
> > through current.  We also save monthly archival PITR base backups
> > with just the WAL files needed to restore them (again, based on the
> > backup files).  Your needs may be different.
>
> Hum...I'm not sure I really understand what you doing.
>
> Maybe I'm doing everything wrong but this is what I did :
>
>     t=0      SELECT pg_start_backup('label');
>         rsync /pgsql backup_server:/
>         SELECT pg_stop_backup();
>
>     t> 1     cron with rsync /WAL backup_server:
>
> but after some time the /WAL become big. So is it a solution to
> stop the database, delete everything in /WAL and begin a new cycle (t=0 select etc...) ?

You could do that if you wanted, but there's a window during which you
have no backup at all.  Perhaps it's better to take a second base
backup, start archiving to a different directory /WAL2, and when the
second base backup is done you can delete /WAL

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

 Le 11/02/2010 à 20:39:30-0300, Alvaro Herrera a écrit
> Albert Shih wrote:
> >  Le 11/02/2010 à 17:00:33-0600, Kevin Grittner a écrit
> > > Albert Shih <Albert.Shih@obspm.fr> wrote:
> > >
> > > > I would like to known what's best way to manage the WAL.
> > >
> > > You should design a policy which meets your particular needs.  We
> > > keep two PITR base backups and all the WAL files from before the
> > > start of the first (based on the information in the backup file)
> > > through current.  We also save monthly archival PITR base backups
> > > with just the WAL files needed to restore them (again, based on the
> > > backup files).  Your needs may be different.
> >
> > Hum...I'm not sure I really understand what you doing.
> >
> > Maybe I'm doing everything wrong but this is what I did :
> >
> >     t=0      SELECT pg_start_backup('label');
> >         rsync /pgsql backup_server:/
> >         SELECT pg_stop_backup();
> >
> >     t> 1     cron with rsync /WAL backup_server:
> >
> > but after some time the /WAL become big. So is it a solution to
> > stop the database, delete everything in /WAL and begin a new cycle (t=0 select etc...) ?
>
> You could do that if you wanted, but there's a window during which you
> have no backup at all.  Perhaps it's better to take a second base
> backup, start archiving to a different directory /WAL2, and when the
> second base backup is done you can delete /WAL

Thanks for you help and advise. But for me that's not be a problem because
I can stop the database when I want. And my database is huge (~800go) and
important but not critical. The critical database is very small (~ 1Go) and
for this I use pgdump.

Regards.

JAS
--
Albert SHIH
SIO batiment 15
Observatoire de Paris Meudon
5 Place Jules Janssen
92195 Meudon Cedex
Heure local/Local time:
Ven 12 fév 2010 12:38:53 CET