Обсуждение: Removal of WAL logs
Hi List
I'm on 8.4.5 in WAL mode with a database that has a low transaction rate and will probably be running nightly backups.
Is there a recommendation for how many WAL logs I should keep on disk in pg_xlog and also how many to keep on external tape?
Are there any simple scripts/procedures that will mange the WAL logs and in particular delete them from pg_xlog when they are no longer needed so that they don't fill up the disk?
Thanks
Austen
On Wed, Jan 4, 2012 at 7:00 AM, Birchall, Austen <austen.birchall@metoffice.gov.uk> wrote: > Hi List > > I'm on 8.4.5 in WAL mode What do you mean by "WAL mode"? > with a database that has a low transaction rate and > will probably be running nightly backups. > > Is there a recommendation for how many WAL logs I should keep on disk in > pg_xlog and also how many to keep on external tape? For a master server (ignoring backup aspects for the moment), the main reason to allow more WAL files to accumulate in ./pg_xlog/ is to get some throughput increase, particularly during bulk data loads. See: http://www.postgresql.org/docs/current/static/wal-configuration.html specifically the paragraph beginning "There will always be at least one WAL segment file...". I'm not sure what you mean by "keep on external tape" bit -- are you using a cold/warm standby, or otherwise archiving your WAL files to tape? > Are there any simple scripts/procedures that will mange the WAL logs and in > particular delete them from pg_xlog when they are no longer needed so that > they don't fill up the disk? Your server should be recycling WAL files. You should never be deleting these files by hand. (For a standby server, you may need to be cleaning out copied WAL files once your standby has digested them, but that's a separate topic -- look up pg_archivecleanup). If you are seeing old WAL files build up in your ./pg_xlog/ directory, that might indicate some problem. Check your postmaster log file, especially for some problem with archive_command which might cause old files to be kept. Josh
Josh Thanks for this - I realised after I posted this that I was getting mixes up in terminology between WAL logs and WAL archiving. As management of WAL archiving was what I was after I will as you suggest look at pg_archivecleanup. Austen -----Original Message----- From: Josh Kupershmidt [mailto:schmiddy@gmail.com] Sent: 06 January 2012 04:48 To: Birchall, Austen Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Removal of WAL logs On Wed, Jan 4, 2012 at 7:00 AM, Birchall, Austen <austen.birchall@metoffice.gov.uk> wrote: > Hi List > > I'm on 8.4.5 in WAL mode What do you mean by "WAL mode"? > with a database that has a low transaction rate and will probably be > running nightly backups. > > Is there a recommendation for how many WAL logs I should keep on disk > in pg_xlog and also how many to keep on external tape? For a master server (ignoring backup aspects for the moment), the main reason to allow more WAL files to accumulate in ./pg_xlog/ is to get some throughput increase, particularly during bulk data loads. See: http://www.postgresql.org/docs/current/static/wal-configuration.html specifically the paragraph beginning "There will always be at least one WAL segment file...". I'm not sure what you mean by "keep on external tape" bit -- are you using a cold/warm standby, or otherwise archiving your WAL files to tape? > Are there any simple scripts/procedures that will mange the WAL logs > and in particular delete them from pg_xlog when they are no longer > needed so that they don't fill up the disk? Your server should be recycling WAL files. You should never be deleting these files by hand. (For a standby server, you may need to be cleaning out copied WAL files once your standby has digested them, but that's a separate topic -- look up pg_archivecleanup). If you are seeing old WAL files build up in your ./pg_xlog/ directory, that might indicate some problem. Check your postmaster log file, especially for some problem with archive_command which might cause old files to be kept. Josh