PostgreSQL pg_xlog and wal_keep_segments/max_wal_size

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

max_wal_size:

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 pg_xlog maximum size.

wal_keep_segments is the amount of WAL to keep in excess of what is needed for crash recovery. This is the minimum size of the pg_xlog directory, 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:

• active archive_mode without working (or even not defined) archive_command. Check these settings and pg_stat_archiver system view.

• inactive replication slot. Check pg_replication_slots system view.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0