Re: WAL logs clog-up disk space

Dear Laurenz,

Thanks for the reply. Answers below

On 27 November 2013 15:18, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> 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?

No. Although a certain amount of time in the past would be useful.
Maybe 2-3 days if that is possible?

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

Yes, we could do this. I know how to use pg_dump so we could automate
that I guess.

> 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/

I've not seen this before. I'll check it out.

> Yours,
> Laurenz Albe