PostgreSQL pg_xlog and wal_keep_segments/max_wal_size
We are running postgresql on centos7 psql (PostgreSQL) 9.6.18
in the conf file wal_keep_segments was commented out which should revert to the default setting of 0, also max_wal_size was set to 2GB. The pg_xlog dir has 7000+ wal files and the size of the dir is 112GB. Some of the wal files date back to 2017.
to test to see if the default for wal_keep_segments was not working correctly we set it to 200, yet the number of wal files continues to increase
I dont understand why these settings arent working, is there a way that these limits are overruled by other settings?
Bogopo last edited by
Maximum size to let the WAL grow during automatic checkpoints. This is a soft limit; WAL size can exceed max_wal_size under special circumstances, like under heavy load, a failing archive_command, or a high wal_keep_segments setting.
This is the amount of WAL that triggers the creation of a checkpoint. Not related to
wal_keep_segmentsis the amount of WAL to keep in excess of what is needed for crash recovery. This is the minimum size of the
pg_xlogdirectory, not the maximum.
If postgresql does not remove old WAL segments, then they are needed for something. In addition to the needs of crash recovery and wal_keep_segments, these can be:
archive_modewithout working (or even not defined)
archive_command. Check these settings and
replication slot. Check https://www.postgresql.org/docs/9.6/view-pg-replication-slots.html system view.