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