How to clear /clean-up pg_xlog directory? on ambari cluster

[#1]

I have found myself in a situation where the pg_xlog folder contains 80GB of data maybe because wrong configuration

how to purge or cleanup the files under pg_xlog?

/var/lib/pgsql/data/pg_xlog
[root@master02 pg_xlog]# ls -ltr | more
total 53887180
-rw-------. 1 postgres postgres 16777216 Sep 19  2017 0000000100000000000000F1
-rw-------. 1 postgres postgres 16777216 Sep 19  2017 0000000100000000000000F2
-rw-------  1 postgres postgres 16777216 Sep 19  2017 0000000100000000000000F3
-rw-------  1 postgres postgres 16777216 Sep 19  2017 0000000100000000000000F4
-rw-------  1 postgres postgres 16777216 Sep 19  2017 0000000100000000000000F5
-rw-------  1 postgres postgres 16777216 Sep 20  2017 0000000100000000000000F6
-rw-------  1 postgres postgres 16777216 Sep 20  2017 0000000100000000000000F7
-rw-------  1 postgres postgres 16777216 Sep 20  2017 0000000100000000000000F8
-rw-------  1 postgres postgres 16777216 Sep 20  2017 0000000100000000000000F9
-rw-------  1 postgres postgres 16777216 Sep 20  2017 0000000100000000000000FA
-rw-------  1 postgres postgres 16777216 Sep 20  2017 0000000100000000000000FB
-rw-------  1 postgres postgres 16777216 Sep 20  2017 0000000100000000000000FC
-rw-------  1 postgres postgres 16777216 Sep 20  2017 0000000100000000000000FD
-rw-------  1 postgres postgres 16777216 Sep 21  2017 0000000100000000000000FE
-rw-------  1 postgres postgres 16777216 Sep 21  2017 000000010000000100000000
-rw-------  1 postgres postgres 16777216 Sep 21  2017 000000010000000100000001
-rw-------  1 postgres postgres 16777216 Sep 21  2017 000000010000000100000002
-rw-------  1 postgres postgres 16777216 Sep 21  2017 000000010000000100000003
-rw-------  1 postgres postgres 16777216 Sep 21  2017 000000010000000100000004
-rw-------  1 postgres postgres 16777216 Sep 21  2017 000000010000000100000005
-rw-------  1 postgres postgres 16777216 Sep 22  2017 000000010000000100000006
-rw-------  1 postgres postgres 16777216 Sep 22  2017 000000010000000100000007
-rw-------  1 postgres postgres 16777216 Sep 22  2017 000000010000000100000008
-rw-------  1 postgres postgres 16777216 Sep 22  2017 000000010000000100000009
-rw-------  1 postgres postgres 16777216 Sep 22  2017 00000001000000010000000A
-rw-------  1 postgres postgres 16777216 Sep 22  2017 00000001000000010000000B
-rw-------  1 postgres postgres 16777216 Sep 23  2017 00000001000000010000000C
-rw-------  1 postgres postgres 16777216 Sep 23  2017 00000001000000010000000D
-rw-------  1 postgres postgres 16777216 Sep 23  2017 00000001000000010000000E
-rw-------  1 postgres postgres 16777216 Sep 23  2017 00000001000000010000000F
-rw-------  1 postgres postgres 16777216 Sep 23  2017 000000010000000100000010
-rw-------  1 postgres postgres 16777216 Sep 23  2017 000000010000000100000011
-rw-------  1 postgres postgres 16777216 Sep 23  2017 000000010000000100000012
-rw-------  1 postgres postgres 16777216 Sep 24  2017 000000010000000100000013
-rw-------  1 postgres postgres 16777216 Sep 24  2017 000000010000000100000014
-rw-------  1 postgres postgres 16777216 Sep 24  2017 000000010000000100000015
-rw-------  1 postgres postgres 16777216 Sep 24  2017 000000010000000100000016
-rw-------  1 postgres postgres 16777216 Sep 24  2017 000000010000000100000017
-rw-------  1 postgres postgres 16777216 Sep 24  2017 000000010000000100000018
-rw-------  1 postgres postgres 16777216 Sep 24  2017 000000010000000100000019
-rw-------  1 postgres postgres 16777216 Sep 24  2017 00000001000000010000001A
-rw-------  1 postgres postgres 16777216 Sep 24  2017 00000001000000010000001B
-rw-------  1 postgres postgres 16777216 Sep 25  2017 00000001000000010000001C
-rw-------  1 postgres postgres 16777216 Sep 25  2017 00000001000000010000001D
-rw-------  1 postgres postgres 16777216 Sep 25  2017 00000001000000010000001E
-rw-------  1 postgres postgres 16777216 Sep 25  2017 00000001000000010000001F
-rw-------  1 postgres postgres 16777216 Sep 25  2017 000000010000000100000020
-rw-------  1 postgres postgres 16777216 Sep 25  2017 000000010000000100000021
--More--

[#2 · Accepted solution]

@Michael Bronson

The replication relies on the WAL files in pg_xlog and px_xlog generated these can be deleted by PostgreSQL automatically. Unless you have enabled replication for this Postgres instance? If it keeps WAL files for too long then check the settings at WAL configuration.

WAL Files are a record of all the transactions that have taken place .. but that's it - a delta between state before and state after, essentially. Which means you have to have a BASE to start off of before you can apply any WAL Files.

If you clear out WAL archives without creating a new BASE, then you will be in trouble when you try to restore your database (via PITR) from them.

If you are going to delete WAL files, then you need to make sure you have a valid base to build from again afterward.

Did you activate some parameters in your default postgresql.conf like archiving/replication? If so stop the Postgres database, comment out the lines and restart the databases it quite simply. Stopped the DB, commented the options in postgresql.conf and restarted.

PostgreSQL will take care of deleting everything automatically

[#3]

Hi Geoffrey , please advice which lines in postgresql.conf we need to comment ? ( archiving/replication )

[#4]

@Michael Bronson

Any updates ?
Please don't forget to vote a helpful answer and accept the best answer.

[#5]

@Geoffrey , files are created each day ( average 5 files each day ) , on other clusters this isnt happens , so if I will delete the old files then the problem will return , so I need to find the root cause , maybe conf problem but my cluster have the same conf of cluster that not have this problem , so I am little confuse ,

[#6]

what variable represented the - "enabled replication for this Postgres instance" ?

[#7]

@Michael Bronson

Can you share your postgresql.conf ? Remember to protect vital info

[#8]

finally we success to decrease it by restart the postgresql and set the right values in postgresql.conf