Home / server / Questions / 476428
In Process
milosgajdos
Asked: 2013-02-08 06:02:10 +0800 CST

Postgres WAL files clean up

  • 772

Is there any way how I can clean up postgres WAL files ON MASTER SERVER - SLAVE is OK, but master has wal keep segment set to 2000 and this is causing disk space usage to grow. I'm looking for a way how to clean up that disk space on MASTER without breaking anything. I know that setting keep segments to 2000 is madness but that was not set by me.

postgresql

3 Answers

  1. Address the underlying cause and the problem will go away (your underlying problem being someone who didn't know what they were doing and didn't read the Postgres manual set wal_keep_segments to an insane value):

    1. Reduce wal_keep_segments to a sane value.
      (10 is usually sane. 100 is not unreasonable).
    2. Restart the Postgres server.
    • 2

  2. An appropriate value for wal_keep_segments will depend on a number of variables. No one can tell you what to set that to. I've set our databases to 64, as 64 is the average number of WAL files written during a busy period over 3 hours. That would mean I have 3 hours to fix any issue between the Primary and the Standby during this time. If I go past 3 hours I would have to either re-silver the Standby or start transferring WAL files from the Archive directory.

    • 0

  3. I ran into this problem on a preproduction server that used to be backed up and is no longer. It turns out the configuration had been changed to:

    archive_command = ''
archive_mode    = on

    ...presumably to remove the rsync command. This resulted in the pg_xlog growing endlessly. You cannot change archive_mode to off, this requires an instance restart.

    I solved the problem by changing archive_command = '/bin/true', did a select pg_reload_conf(); and the directory was cleared over the next couple of checkpoints.

    • 0