Thread: WAL files not being recycled

Scott Moynes wrote:
> I have a PostgreSQL server that is not recycling WAL files. Log files are
> continually created and no old log files are ever removed.
>
> Running PostgreSQL v 9.4.8 with archive settings:
>
>     archive_mode = on
>     archive_command = /bin/true
>
> Checkpoint logging is enabled and does not record any logs being recycled:

See the wal_keep_segments option, as well as existance of any
replication slot (select * from pg_replication_slots).

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 05/16/2016 03:33 PM, Scott Moynes wrote:
> I have a PostgreSQL server that is not recycling WAL files. Log files
> are continually created and no old log files are ever removed.
>
> Running PostgreSQL v 9.4.8 with archive settings:
>
>     archive_mode = on
>     archive_command = /bin/true
>
> Checkpoint logging is enabled and does not record any logs being recycled:
>
>     2016-05-16 00:05:37 EDT -  LOG:  checkpoint starting: xlog
>     2016-05-16 00:09:51 EDT -  LOG:  checkpoint complete: wrote 38574
> buffers (29.4%); 0 transaction log file(s) added, 0 removed, 0 recycled;
> write=252.921 s, sync=1.340 s, total=254.312 s; sync files=48,
> longest=1.101 s, average=0.027 s
>     2016-05-16 00:10:37 EDT -  LOG:  checkpoint starting: time
>     2016-05-16 00:14:11 EDT -  LOG:  checkpoint complete: wrote 43786
> buffers (33.4%); 0 transaction log file(s) added, 0 removed, 0 recycled;
> write=206.271 s, sync=7.428 s, total=213.765 s; sync files=82,
> longest=3.821 s, average=0.090 s
>     ...
>     2016-05-16 07:32:27 EDT -  LOG:  checkpoint complete: wrote 7617
> buffers (5.8%); 1 transaction log file(s) added, 0 removed, 0 recycled;
> write=269.3
>
> Control data records:
>
>     pg_control last modified:             Mon 16 May 2016 06:23:07 PM EDT
>     Latest checkpoint location:           1EB/1F008FE8
>     Prior checkpoint location:            1EB/1E0009E0
>     Latest checkpoint's REDO location:    1EB/1F008FB0
>
> There is a single replication client:
>
>     pid              | 23287
>     usesysid         | 10
>     usename          | postgres
>     application_name | walreceiver
>     client_addr      | 172.16.56.246
>     client_hostname  |
>     client_port      | 58070
>     backend_start    | 2016-05-14 03:27:33.523611+00
>     backend_xmin     |
>     state            | streaming
>     sent_location    | 1EB/1F042760
>     write_location   | 1EB/1F042760
>     flush_location   | 1EB/1F042760
>     replay_location  | 1EB/1F037F40
>     sync_priority    | 0
>     sync_state       | async
>
>
> Does anyone have suggestions what else to check? What can cause WALs not
> to be recycled or removed?

http://www.postgresql.org/docs/9.4/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-MASTER

"wal_keep_segments (integer)

     Specifies the minimum number of past log file segments kept in the
pg_xlog directory, in case a standby server needs to fetch them for
streaming replication. Each segment is normally 16 megabytes. If a
standby server connected to the sending server falls behind by more than
wal_keep_segments segments, the sending server might remove a WAL
segment still needed by the standby, in which case the replication
connection will be terminated. Downstream connections will also
eventually fail as a result. (However, the standby server can recover by
fetching the segment from archive, if WAL archiving is in use.)

     This sets only the minimum number of segments retained in pg_xlog;
the system might need to retain more segments for WAL archival or to
recover from a checkpoint. If wal_keep_segments is zero (the default),
the system doesn't keep any extra segments for standby purposes, so the
number of old WAL segments available to standby servers is a function of
the location of the previous checkpoint and status of WAL archiving.
This parameter can only be set in the postgresql.conf file or on the
server command line.
"

>
> Thanks in advance.
>
> --
> Scott Moynes
> VM Farms


--
Adrian Klaver
adrian.klaver@aklaver.com

On 17/05/16 00:54, Scott Moynes wrote:
> wal_keep_segments is set to 32.
>
> Here is the replication slot:
>
>     slot_name    |
> n6lbb2vmohwuxoyk_00018732_f58b5354_79ad_4e6e_b18b_47acb1d7ce1f
>     plugin       | test_decoding
>     slot_type    | logical
>     datoid       | 18732
>     database     | omitted
>     active       | f
>     xmin         |
>     catalog_xmin | 84884044
>     restart_lsn  | 1B3/4300CA60
>
> Can an inactive replication slot cause WALs to be retained?

Absolutely!  That's what they're for.

If you don't expect this slot to re-become active, you should drop it.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support