Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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.
share|improve this answer

I was in a similar situation. After reducing wal_keep_segments and restarting postgres, the old logs weren't going away. I ended up using the following commands to delete all old logs:

pg_datadir=/path/to/pgsql/data

last_wal_file=$(pg_controldata $pg_datadir | grep "Latest checkpoint's REDO WAL file" | awk '{print $NF}')

find $pg_datadir/pg_xlog -maxdepth 1 -type f \
  \! -name $last_wal_file \
  \! -newer $pg_datadir/pg_xlog/$last_wal_file \
  -exec rm {} \;

This will delete any logs older than the last checkpoint (what postgres is actively using).
This will probably delete a lot more than what wal_keep_segments is set to, so make sure the files have been picked up by replicas, archives, etc. Or just use the command as a starting point to determine what is safe to delete, and come up with your own strategy.

You should also run it without the -exec rm {} \; to make sure it's not going to do anything incredibly stupid.

(Solution based off info from http://blog.endpoint.com/2014/09/pgxlog-disk-space-problem-on-postgres.html)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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