Re: [GENERAL] WAL files not being recycled

[Vik Fearing]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WAL files not being recycled

[Scott Moynes]

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?

On Mon, May 16, 2016 at 3:44 PM, Alvaro Herrera 
wrote:

> 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 Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>



-- 
Scott Moynes
VM Farms

Re: [GENERAL] WAL files not being recycled

[Adrian Klaver]

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WAL files not being recycled

[Alvaro Herrera]

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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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

[GENERAL] WAL files not being recycled

[Scott Moynes]

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?

Thanks in advance.

-- 
Scott Moynes
VM Farms