Skip site navigation (1) Skip section navigation (2)

Re: Removal of WAL logs

From: "Birchall, Austen" <austen(dot)birchall(at)metoffice(dot)gov(dot)uk>
To: "Josh Kupershmidt" <schmiddy(at)gmail(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Removal of WAL logs
Date: 2012-01-06 09:53:23
Message-ID: A2E53AF8AE1C284C8F8B648653C0920B1D6A5775@EXXMAIL02.desktop.frd.metoffice.com (view raw or flat)
Thread:
Lists: pgsql-novice
 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(at)gmail(dot)com] 
Sent: 06 January 2012 04:48
To: Birchall, Austen
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Removal of WAL logs

On Wed, Jan 4, 2012 at 7:00 AM, Birchall, Austen
<austen(dot)birchall(at)metoffice(dot)gov(dot)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

In response to

pgsql-novice by date

Next:From: Jean-Yves F. BarbierDate: 2012-01-08 17:29:24
Subject: How to retrieve the output of an external program?
Previous:From: Josh KupershmidtDate: 2012-01-06 04:47:44
Subject: Re: Removal of WAL logs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group