> - replication slot (you said that is not it)
The replication slot is the same on master:
postgres=# select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+--------------+---------------------
stanby_slot | | physical | | | f | t | 94341 | | | 145/F6ECC190 |
(1 row)
$ grep slot ${PGDATA}/recovery.done
primary_slot_name = 'stanby_slot'
and on slave:
postgres=# select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+--------------+---------------------
stanby_slot | | physical | | | f | f | | | | 13E/981E2DD0 |
(1 row)
$ grep slot ${PGDATA}/recovery.conf
primary_slot_name = 'stanby_slot'
postgres=# show wal_keep_segments;
wal_keep_segments
-------------------
32
(1 row)
but on slave:
$ ls pg_wal/ | wc -l
1892
The replication slot is the same on master:
postgres=# select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+--------------+---------------------
stanby_slot | | physical | | | f | t | 94341 | | | 145/F6ECC190 |
(1 row)
$ grep slot ${PGDATA}/recovery.done
primary_slot_name = 'stanby_slot'
and on slave:
postgres=# select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+--------------+---------------------
stanby_slot | | physical | | | f | f | | | | 13E/981E2DD0 |
(1 row)
$ grep slot ${PGDATA}/recovery.conf
primary_slot_name = 'stanby_slot'
postgres=# show wal_keep_segments;
wal_keep_segments
-------------------
32
(1 row)
but on slave:
$ ls pg_wal/ | wc -l
1892
вт, 16 мар. 2021 г. в 15:53, Laurenz Albe <laurenz.albe@xxxxxxxxxxx>:
On Tue, 2021-03-16 at 15:31 +0200, Andrew Anderson wrote:
> вт, 16 мар. 2021 г. в 14:21, Laurenz Albe <laurenz.albe@xxxxxxxxxxx>:
> > On Tue, 2021-03-16 at 09:49 +0200, Andrew Anderson wrote:
> > > 2021-03-16 09:44:03.997 EET [97581] [] [] [] []DEBUG: attempting to remove WAL segments older than log file 000000000000013E00000097
> >
> > That was the entry I was hoping to see.
> >
> > So, are all older WAL segments deleted or recycled?
> > Is that a reasonably recent WAL segment?
>
> but there is files with very old time of creation:
> $ ls -lart pg_wal/
> total 30933220
> -rw------- 1 postgres postgres 299 Dec 30 05:44 000000020000013E00000097.00000108.backup
> -rw------- 1 postgres postgres 16777216 Dec 30 06:12 000000010000000000000001
> -rw------- 1 postgres postgres 16777216 Dec 30 06:12 000000020000013E00000098
> -rw------- 1 postgres postgres 44 Dec 30 06:29 00000002.history
> -rw------- 1 postgres postgres 16777216 Dec 30 06:29 000000020000013E00000099
> -rw------- 1 postgres postgres 16777216 Dec 30 09:01 000000020000013E0000009A
> -rw------- 1 postgres postgres 16777216 Dec 30 09:57 000000020000013E0000009B
> ..... till now .....
> -rw------- 1 postgres postgres 16777216 Mar 16 12:59 0000000200000145000000F2
> -rw------- 1 postgres postgres 16777216 Mar 16 13:50 0000000200000145000000F3
> -rw------- 1 postgres postgres 16777216 Mar 16 14:39 0000000200000145000000F4
> drwx------ 2 postgres postgres 118784 Mar 16 14:39 archive_status
> -rw------- 1 postgres postgres 16777216 Mar 16 15:19 0000000200000145000000F6
> drwx------ 3 postgres postgres 94208 Mar 16 15:19 .
> -rw------- 1 postgres postgres 16777216 Mar 16 15:25 0000000200000145000000F5
> drwx------ 19 postgres postgres 4096 Mar 16 15:25 ..
>
> and on master current WAL-file:
> postgres=# select pg_walfile_name(pg_current_wal_lsn());
> pg_walfile_name
> --------------------------
> 0000000200000145000000F6
> (1 row)
These things hold up deletion of WAL segments:
- checkpoint (not the problem here)
- replication slot (you said that is not it)
- archiver failure (also doesn't seem to be the problem)
- wal_keep_segments / wal_keep_size: what is your setting?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
