Re: Too many WAL archive files

From: Jorge Torralba <jorge(dot)torralba(at)gmail(dot)com>
To: Matheus de Oliveira <matioli(dot)matheus(at)gmail(dot)com>
Cc: Keith Ouellette <Keith(dot)Ouellette(at)airgas(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Too many WAL archive files
Date: 2013-09-15 00:25:16
Message-ID: CACut7uTPanSMHsmkoKfAuXZUX1mLh2OSxVTQ4LCSNiU=0mDthw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

You should consider using the cleanup command in your recovery.conf file,
this will make sure that wal files no longer needed by the secondary server
are eliminated.

If you need the wal files for PITR, yo could also setup your archive
command to rchive the wal files to two different locations. a shared
location where the secondary can read if it needs to catch up and a keep
location which is used by the primary just for PITR which you could then
clean up after each base backup.

In case you ask, if this is a unix box, you can separatethe two commands
for archive_command with && for example

comand 1 && command 2

JT

On Sat, Sep 14, 2013 at 8:55 AM, Matheus de Oliveira <
matioli(dot)matheus(at)gmail(dot)com> wrote:

>
> On Sat, Sep 14, 2013 at 11:19 AM, Keith Ouellette <
> Keith(dot)Ouellette(at)airgas(dot)com> wrote:
>
>> My company is using PostgreSQL 9.1 for one of our applications. We have
>> it set up replicating between two sites using WAL and Pacemaker to manage
>> the cluster and failover. I have noticed that archive folder is growing
>> very large 16G out of the 19G that the data directory is in total. We have
>> the wal_keep_segments = 1000 set in the postgresql.conf file.
>>
>>
>>
>> I thought that meant to keep the last 1000 files in the event that it was
>> needed to catch up after falling behind.
>>
>
> No. PostgreSQL will always keep at least (can be a little more)
> wal_keep_segments files in any situation. So, wal_keep_segments is set to
> 1000, and each wal file has 16MB, doing the math it means PostgreSQL will
> use at least 15.625GB (~16GB) for wal files, it is what you have. If you
> don't have enough space for this, you should set keep wal_keep_segments to
> a lower value.
>
> I am noticing that I have 12000 files in the archive directory. I know
>> that includes the .backup files as well, but that is a smaller portion of
>> the files in that directory.
>>
>
> Unless you have a lot of .backup files, there is no way 12000 wal file
> will use only 16GB, it would be 187.5GB, so this number seems wrong.
>
> Also check if you have set up archiving and if it is working, because if
> archive_command fails, PostgreSQL will keep the "failed on archive" files
> on pg_xlog path and keep trying it.
>
> Best regards,
> --
> Matheus de Oliveira
> Analista de Banco de Dados
> Dextra Sistemas - MPS.Br nível F!
> www.dextra.com.br/postgres
>
>

--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential
information. If you are not the intended recipient, please do not print,
copy, retransmit, disseminate or otherwise use the information. Please
indicate to the sender that you have received this email in error and
delete the copy you received. Thank You.

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Rural Hunter 2013-09-15 01:05:34 Re: wrong database name in error message?
Previous Message Kevin Grittner 2013-09-14 17:06:08 Re: wrong database name in error message?