Menu
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?
591
These logs are also used for binary replication. Unlike pg_log, you may not freely delete, move, or compress files in this directory. You may not even move the directory without symlinking it back to its original location. Deleting pg_xlog files may result in unrecoverable database corruption.
You can never delete pg_wal files. pg_wal is the directory that contains the primary WAL files, and there will never be a *. backup file in it.
pg_wal (in PostgreSQL 9.6 and earlier: pg_xlog ) earlier is the subdirectory of the main PostgreSQL data directory where WAL files are stored. It also contains the subdirectory archive_status .
Running out of disk space in the pg_xlog directory is a fairly common Postgres problem. This important directory holds the WAL (Write Ahead Log) files. (WAL files contain a record of all changes made to the database—see the link for more details).
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.
90
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
