pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to manage WAL
5+ messages / 3 participants
[nested] [flat]
* How to manage WAL
@ 2010-02-11 22:32 Albert Shih <Albert.Shih@obspm.fr>
2010-02-11 23:00 ` Re: How to manage WAL Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 5+ messages in thread
From: Albert Shih @ 2010-02-11 22:32 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to manage WAL
2010-02-11 22:32 How to manage WAL Albert Shih <Albert.Shih@obspm.fr>
@ 2010-02-11 23:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-02-11 23:32 ` Re: How to manage WAL Albert Shih <Albert.Shih@obspm.fr>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2010-02-11 23:00 UTC (permalink / raw)
To: Albert Shih <Albert.Shih@obspm.fr>; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to manage WAL
2010-02-11 22:32 How to manage WAL Albert Shih <Albert.Shih@obspm.fr>
2010-02-11 23:00 ` Re: How to manage WAL Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-02-11 23:32 ` Albert Shih <Albert.Shih@obspm.fr>
2010-02-11 23:39 ` Re: How to manage WAL Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 5+ messages in thread
From: Albert Shih @ 2010-02-11 23:32 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to manage WAL
2010-02-11 22:32 How to manage WAL Albert Shih <Albert.Shih@obspm.fr>
2010-02-11 23:00 ` Re: How to manage WAL Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-02-11 23:32 ` Re: How to manage WAL Albert Shih <Albert.Shih@obspm.fr>
@ 2010-02-11 23:39 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-02-12 11:42 ` Re: How to manage WAL Albert Shih <Albert.Shih@obspm.fr>
0 siblings, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2010-02-11 23:39 UTC (permalink / raw)
To: Albert Shih <Albert.Shih@obspm.fr>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to manage WAL
2010-02-11 22:32 How to manage WAL Albert Shih <Albert.Shih@obspm.fr>
2010-02-11 23:00 ` Re: How to manage WAL Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-02-11 23:32 ` Re: How to manage WAL Albert Shih <Albert.Shih@obspm.fr>
2010-02-11 23:39 ` Re: How to manage WAL Alvaro Herrera <alvherre@commandprompt.com>
@ 2010-02-12 11:42 ` Albert Shih <Albert.Shih@obspm.fr>
0 siblings, 0 replies; 5+ messages in thread
From: Albert Shih @ 2010-02-12 11:42 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-02-12 11:42 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-11 22:32 How to manage WAL Albert Shih <Albert.Shih@obspm.fr>
2010-02-11 23:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-02-11 23:32 ` Albert Shih <Albert.Shih@obspm.fr>
2010-02-11 23:39 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-02-12 11:42 ` Albert Shih <Albert.Shih@obspm.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox