Re: pg_archivecleanup - Increase time files are deleted

On Sun, Jun 26, 2016 at 7:39 PM, Ian Barwick <ian(at)2ndquadrant(dot)com> wrote:

> On 06/27/2016 06:33 AM, Patrick B wrote:
>
>> ...I'd like to have the wal_files stored for 24h, and then the
>>>>
>>> pg_archivecleanup could do its job and delete the files..
>>>
>>>
>>> *You cannot do that, but you can change the wal_keep_segments
>>> <
>>> https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS
>>> >
>>> on the master and reload the postgresql.conf.*
>>> *
>>> https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS
>>> <
>>> https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS
>>> >*
>>>
>>>
>> ok.. my current wal_keep_segments is:
>>
>> wal_keep_segments = 256
>> That means there will be at least 256 files before postgres delete them?
>>
>>>
>>> Please, in the future, remember to state your *PostgreSQL version and
>>> O/S*
>>> as options can change.
>>>
>>>
>>> Ok.. my mistake.. I'm running PostgreSQL 9.2 in a Centos 6.7 64 bits.
>>
>
> FYI PostgreSQL 9.4 and later provide "replication slots", which will
> ensure that all WAL
> files are kept until no longer required by standbys:
>
>
> https://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION-SLOTS
>
> This removes the need for guesswork on how much WAL to retain, though you
> do then need to be
> careful that all standbys are actually consuming WAL otherwise files will
> be retained for ever
> (or until disk space runs out, whichever comes first).
>
> Regards
>
> Ian Barwick
>
>
> --
> Ian Barwick http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Patrick

>So I've made a bash script to do the work for me.
>The script will only delete the wal_files older than 24h ( -mmin +1440 )

It may be working, but that is not the supported method for wal file
retention. The correct thing to do is just
incrtease the wal_keep_segments as per the documentation
https://www.postgresql.org/docs/9.2/static/runtime-config-replication.html

>FYI PostgreSQL 9.4 and later provide "replication slots", which will
ensure that all WAL
>files are kept until no longer required by standbys:

*replication slots will not work, as you are on 9.2.*

*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.