Re: stale WAL files?

On 3/28/19 7:30 AM, Michael Paquier wrote:
> On Tue, Mar 26, 2019 at 09:50:37AM -0600, Rob Sargent wrote:
>> No, sorry I should have said that up front. We’re simple folk.
> What is the WAL position (LSN)
postgres=# select * from pg_current_wal_flush_lsn();
  pg_current_wal_flush_lsn
--------------------------
  CEA/E57EAA8
(1 row)

postgres=# select * from pg_current_wal_insert_lsn();
  pg_current_wal_insert_lsn
---------------------------
  CEA/E57EAA8
(1 row)

postgres=# select * from pg_current_wal_lsn();
  pg_current_wal_lsn
--------------------
  CEA/E57EAA8
(1 row)

> where Postgres is writing to and what
> is the set of WAL segments in pg_wal (or pg_xlog if that's a server
> older than 10)?

  select version();
version
---------------------------------------------------------------------------------------------------------
  PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

This is pg10 so it's pg_wal.  ls -ltr


-rw-------. 1 postgres postgres 16777216 Mar 16 16:33 
0000000100000CEA000000B1
-rw-------. 1 postgres postgres 16777216 Mar 16 16:33 
0000000100000CEA000000B2

  ... 217 more on through to ...

-rw-------. 1 postgres postgres 16777216 Mar 16 17:01 
0000000100000CEA000000E8
-rw-------. 1 postgres postgres 16777216 Mar 16 17:01 
0000000100000CEA000000E9
-rw-------. 1 postgres postgres 16777216 Mar 28 09:46 
0000000100000CEA0000000E

> Please double-check the configuration value of
> wal_keep_segments,
#wal_keep_segments = 0          # in logfile segments, 16MB each
> and as mentioned upthread, could you make sure that
> you have no replication slots active?

This could be part of the problem?

#max_replication_slots = 10     # max number of replication slots

but

   select * from pg_replication_slots;
  slot_name | plugin | slot_type | datoid | database | temporary | 
active | active_pid | xmin | catalog_xmin | restart_lsn | 
confirmed_flush_lsn

-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)


> This can be done simply by
> querying pg_replication_slots.  Please note as well that checkpoints
> are server-wide, so there is no point to run them on all databases.
> Only one command will be effective for all databases.
I suspected as much, but there weren't many dbs so I went all in.
> --
> Michael