Re: WAL logs clog-up disk space

James David Smith wrote:
> I'm currently using a PostgreSQL 9.0 installation on a CentOS 5 linux
> machine. I'm not the administrator of the machine, however the person
> that is doesn't know very much about PostgreSQL unfortunately. So I'm
> trying to work with them to sort out a problem I'm having. What seems
> to be the problem, is that periodically, a folder where some WAL logs
> are stored gets full i.e. the disk partition runs out of space. I've
> looked into the pg_conf file and have found this:
>
> archive_mode = on
> archive_command = 'cp %p /var/lib/pgsql/9.0/data/pg_wal/%f'
>
> The folder 'var' is on a partition with about 20GB of space. Once
> every few weeks or so, this gets full, and I can't use PostgreSQL. The
> administrator then runs something like the below to clean the folder
> out:
>
> pg_archivecleanup -d /var/lib/pgsql/9.0/data/pg_wal
> 000000010000001600000016.00000017.backup
>
> Could someone suggest a more permanent solution instead please? Is
> there a better way to configure this?

Yes, there are several ways.

First, do you need to be able to restore the database to any given
point in the past?

If not, and you do your backups with pg_dump, you can simply
set archive_mode=off and restart the database.

Otherwise you should make sure that all archived WALs older
than your oldest backup are deleted.

You can use cron jobs or something else for that.

Maybe you want to have a look at http://www.pgbarman.org/

Yours,
Laurenz Albe