cluster with a logical replication slot not recycling WAL files

From: "S(dot) Bob" <sbob(at)quadratum-braccas(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: cluster with a logical replication slot not recycling WAL files
Date: 2019-06-03 19:49:10
Message-ID: 3cb0b58e-71bf-d123-05d9-c2c904e683eb@quadratum-braccas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all;

I setup a 9.6 cluster with the following:

archive_mode = on

archive_command = 'cp %p /var/lib/pgsql/9.6/wal/%f'

archive_timeout = 60

max_replication_slots = 5

wal_level = logical

Then I used pgbench to initialize the pgbench tables

Then I started the cluster, connected to psql and created a logical
replication slot:

select * from pg_create_logical_replication_slot ('slot_a',
'test_decoding');

Next I generated some pgbench traffic and retrieved the traffic via :

select * from pg_logical_slot_get_changes ('slot_a', NULL, NULL);

The pg_xlog dir was recycling wal files as expected, for some time there
were no more that about 5 wal files in pg_xlog. Then for some reason the
cluster stopped removing wal files from pg_xlog.

There are no additional transactions to consume:

postgres=#
select * from pg_logical_slot_get_changes ('slot_a', NULL, NULL);
 location | xid | data
----------+-----+------
(0 rows)

pg_replication_slots shows this:postgres=# select * from
pg_replication_slots ;
-[ RECORD 1 ]-------+--------------
slot_name           | slot_a
plugin              | test_decoding
slot_type           | logical
datoid              | 13325
database            | postgres
active              | f
active_pid          |
xmin                |
catalog_xmin        | 515517
restart_lsn         | 1/D8000028
confirmed_flush_lsn | 1/E6000060

pg_xlog is now 593M with 36 wal files:

$ du  -sh pg_xlog
593M    pg_xlog

$ ls -1 pg_xlog/; ls -1 pg_xlog/0* | wc -l
0000000100000001000000CA
0000000100000001000000CB
0000000100000001000000CC
0000000100000001000000CD
0000000100000001000000CE
0000000100000001000000CF
0000000100000001000000D0

...

0000000100000001000000E9
0000000100000001000000EA
0000000100000001000000EB
0000000100000001000000EC
0000000100000001000000ED
archive_status
36

I don't see any reason for it to keep these files around.

Archivig is working, it's only 1 - 2 files behind:

$ ls -1 ../wal
0000000100000001000000BD
0000000100000001000000BE
0000000100000001000000BF
0000000100000001000000C0
0000000100000001000000C1

...

0000000100000001000000E6
0000000100000001000000E7
0000000100000001000000E8
0000000100000001000000E9
0000000100000001000000EA
0000000100000001000000EB

How do I get the cluster to begin removing old wal files?

Thanks in advance

Browse pgsql-admin by date

  From Date Subject
Next Message Pavan Kumar 2019-06-04 20:00:24 postgres source code installation vs rpm based installation
Previous Message Alex Williams 2019-06-03 15:43:41 Re: centos 7 / pgadmin 4.7