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
