Обсуждение: pg_archivecleanup - Increase time files are deleted

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.

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

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. 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general