Re: pg_archivecleanup - Increase time files are deleted

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Sun, Jun 26, 2016 at 5:14 PM, Patrick B <patrickbakerbr@xxxxxxxxx> wrote:

Hi guys,

I've got one MASTER and three SLAVES servers, running PostgreSQL 9.2.

I had a problem with one of the masters, and because the wal_files aren't kept for a long time, I had to re-sync all the DB.

If the wal_files were being kept longer than they are now, I wouldn't have to re-sync (pg_basebackup) all the DB.

So, I'd like to have the wal_files stored for 24h, and then the pg_archivecleanup could do its job and delete the files....

How can I increase that?

These are my wal_files now: (got from a slave)

-rw------- 1 postgres postgres 16777216 Jun 26 21:04 00000002000013CA000000CA
-rw------- 1 postgres postgres 16777216 Jun 26 21:05 00000002000013CA000000CB
-rw------- 1 postgres postgres 16777216 Jun 26 21:05 00000002000013CA000000CC
-rw------- 1 postgres postgres 16777216 Jun 26 21:06 00000002000013CA000000CD
-rw------- 1 postgres postgres 16777216 Jun 26 21:07 00000002000013CA000000CE
-rw------- 1 postgres postgres 16777216 Jun 26 21:08 00000002000013CA000000CF
-rw------- 1 postgres postgres 16777216 Jun 26 21:09 00000002000013CA000000D0
-rw------- 1 postgres postgres 16777216 Jun 26 21:09 00000002000013CA000000D1
-rw------- 1 postgres postgres 16777216 Jun 26 21:10 00000002000013CA000000D2
-rw------- 1 postgres postgres 16777216 Jun 26 21:10 00000002000013CA000000D3
-rw------- 1 postgres postgres 16777216 Jun 26 21:11 00000002000013CA000000D4
-rw------- 1 postgres postgres 16777216 Jun 26 21:12 00000002000013CA000000D5

>...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 on the master and reload the postgresql.conf.

https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS

Please, in the future, remember to state your PostgreSQL version and O/S as options can change.

--

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