Auto clean pg_xlog

Question

Does anyone know how to clean up pg_xlog in PostgreSQL 9.5 automatically? Since I enabled replication disk has been filled up to 200G for 2 weeks. So I cleaned it manually with the following commands:

sudo service portgesql stop
sudo /usr/lib/postgresql/9.5/bin/pg_controldata /var/lib/postgresql/9.5/main/ | grep Next | grep -v Multi
sudo -u postgres /usr/lib/postgresql/9.5/bin/pg_resetxlog -o 44842 -x 575323138 -f /var/lib/postgresql/9.5/main/
sudo service portgesql start

Of course I can automate it with bash script, but the problem here is that it is necessary to stop PotgreSQL. Is there any other ways to clean this without stopping PostgreSQL?

Answer 1

Slightly worrying that you're removing files from pg_xlog, they're imperative to the recovery of your database. Please don't tell me this is production :)

Anyway, there's a more fundamental reason why WAL's are gathering at that kind of rate which is due to the newly introduced "replication slots" in PostgreSQL.

The replication slot will keep WAL logs until they've been applied to the DR so your WALS are not being applied to the standby. Chances are if you've been removing WALS then they never will be applied :) and thus comes the viscious cycle.

You can also control the amount of WALS in that directory by the parameted of "wal_keep_segments" though I'm not sure how much of a role this has in 9.5 with a replica and a replication slot.

Either way you certainly need to stop deleting WAL's and get your DR rebuilt with a fresh sync of your master.