Menu
I'm trying to configure a PostgreSQL 9.6 database to limit the size of the pg_xlog folder. I've read a lot of threads about this issue or similar ones but nothing I've tried helped.
I wrote an setup script for my Postgresql 9.6 service instance. It executes initdb, registers a windows service, starts it, creates an empty database and restores a dump into the database. After the script is done, the database structure is fine, the data is there but the xlog folder already contains 55 files (880 mb).
To reduce the size of the folder, I tried setting wal_keep_segments to 0 or 1, setting the max_wal_size to 200mb, reducing the checkpoint_timeout, setting archive_mode off and archive_command to an empty string. I can see the properties have been set correctly when I query pg_settings.
I then forced checkpoints through SQL, vacuumed the database, restarted the windows service and tried pg_archivecleanup, nothing really worked. My xlog folder downsized to 50 files (800 mb), not anywhere near the 200 mb limit I set in the config.
I have no clue what else to try. If anyone can tell me what I'm doing wrong, I would be very grateful. If more information is required, I'll be glad to provide it.
Many thanks
364
You can freely delete, rename, compress, and move files in pg_log without penalty, as long as the postgres user still has rights to write to the directory. If pg_log becomes bloated with many large files, you probably need to decrease the number of things you're logging by changing the settings in postgresql.
pg_xlog contains Postgres Write Ahead Logs (WAL, Postgres. implementation of transaction logging) files (normally 16MB in size, each). pg_clog contains the commit log files which contain transaction commit. status of a transaction.
WAL logs are stored in the directory pg_wal under the data directory, as a set of segment files, normally each 16 MB in size (but the size can be changed by altering the --wal-segsize initdb option).
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).
PostgreSQL won't aggressively remove WAL segments that have already been allocated when max_wal_size was at the default value of 1GB.
The reduction will happen gradually, whenever a WAL segment is full and needs to be recycled. Then PostgreSQL will decide whether to delete the file (if max_wal_size is exceeded) or rename it to a new WAL segment for future use.
If you don't want to wait that long, you could force a number of WAL switches by calling the pg_switch_xlog() function, that should reduce the number of files in your pg_xlog.
67
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
