Re: archive folder maintenance

Using pgarchivecleanup command in archive_cleanup_command within
recovery.conf file on slave, the %r part will automatically get the latest
.backupfile.

Another way to automate it, an excellent tool that has an option for
automatically removing replayed files is OmniPITR
https://github.com/omniti-labs/omnipitr. In this case, you can use command
'omnipitr-cleanup' in the archive_cleanup_command.

You can read more here -
http://www.postgresql.org/docs/9.0/static/archive-recovery-settings.html#ARCHIVE-CLEANUP-COMMAND

Hope this helps

Payal Singh,
OmniTI Computer Consulting Inc.
Junior Database Administrator,
Phone: 240.646.0770 x 253

On Wed, Jan 22, 2014 at 8:45 AM, Keith Ouellette <Keith(dot)Ouellette(at)airgas(dot)com
> wrote:

> Payal,
>
>
>
> Thank you. This is the master with two standby replicas. The latest
> backup is the file in the archive directory ending in .backup right? I
> should be able to delete anything dated earlier? Is there a configuration
> in PostgreSQL to automatically do that? The archive_timeout is currently
> commented out, so I guess whatever the default is.
>
>
>
> Thank you,
>
> Keith
>
>
> ------------------------------
> *From:* Payal Singh [payal(at)omniti(dot)com]
> *Sent:* Tuesday, January 21, 2014 10:49 PM
> *To:* Keith Ouellette
> *Cc:* pgsql-novice(at)postgresql(dot)org
> *Subject:* Re: [NOVICE] archive folder maintenance
>
> Is this a standalone server? What is the value of archive_timeout?
>
> It is generally safe to delete WAL files older than a day or two. More
> specifically, all WAL files older than the latest base backup are safe to
> selete. A simple bash script will do in this case.
>
> You can also have alook at
> http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html tool
> which you can straightaway use.
>
>
> Payal Singh,
> OmniTI Computer Consulting Inc.
> Junior Database Administrator,
> Phone: 240.646.0770 x 253
>
>
> On Tue, Jan 21, 2014 at 9:16 PM, Keith Ouellette <
> Keith(dot)Ouellette(at)airgas(dot)com> wrote:
>
>> We are running PostgreSQL 9.1 with WAL. I am noticing the archive
>> directory just keeps growing. It is currently at 392GB. What is the proper
>> maintenance to keep that under control? It does not seem necessary to have
>> that much archived. We are doing regular backups (pgdump). Any suggestions.
>> The WAL configuration is the following:
>>
>>
>>
>> wal_level = hot_standby
>>
>> archive_mode = on
>>
>> archive_command = 'cp %p /opt/PostgreSQL/9.1/data/archive/%f'
>>
>> max_wal_senders = 2
>>
>> wal_keep_segments = 1000 << I also think this is unnecessary as well
>> should be much less in my opinion but shouldn't this deep 1000 16MB WAL
>> files or 16GB?
>>
>>
>>
>>
>>
>> Any guidance would be greatly appreciated
>>
>>
>>
>> Thanks,
>>
>> Keith
>>
>
>