PostgreSQL + how to clean pg_xlog log without damage the PostgreSQL service

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

we are using postgresql for ambari DB , in our linux server – rhel 7.2

postgres -V postgres (PostgreSQL) 9.2.13

we noticed that pg_xlog folder consuming 198g , and its gives /var to get 100% size

so after google searching

we noticed about postgresql.conf that have the parameter – wal_keep_segments

according to documentation , wal_keep_segments Specifies the minimum number of past log file segments kept in the pg_xlog directory

my questions are:

  1. is it safe to use this parameter – wal_keep_segments , and doing purging on pg_xlog folder
  2. based on the size of the folder that is nearly to 200g , what is the value that we need to set to wal_keep_segments

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

You should ask whoever set wal_keep_segments why they did it. Typically it is done for pg_rewind or streaming replication.

It is no problem to reset the parameter to its default value 0. Then, when the next checkpoint runs, old WAL segments should be removed.

Never delete WAL segments manually.

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

Leave a Reply