1

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?

2
  • They are probably being kept to satisfy a replication slot, or because archiving is on but the archive command is failing. figure out which is the case and fix it.
    – jjanes
    Commented Mar 8, 2022 at 20:30
  • 1
    Hi, and welcome to dba.se! Just to let you know, 9.6 is EOL - you should possibly consider upgrading.
    – Vérace
    Commented Mar 9, 2022 at 6:05

1 Answer 1

3

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.

1
  • Thank you we had a inactive replication slot
    – George
    Commented Mar 8, 2022 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.