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

Поиск
Список
Период
Сортировка

How to manage WAL

От
Albert Shih
Дата:
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

Re: How to manage WAL

От
"Kevin Grittner"
Дата:
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

Re: How to manage WAL

От
Albert Shih
Дата:
 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

Re: How to manage WAL

От
Alvaro Herrera
Дата:
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.

Re: How to manage WAL

От
Albert Shih
Дата:
 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