1/2/2024 0 Comments Xlog filling up causesPostgreSQL WAL segments take up approximately this much room, according to the documentation for 9.1 WAL Configuration: There are some tips on dealing with pg_xlog filling up in this blog post: Solving pg_xlog out of disk space problems, but these mostly apply to a primary that's run out of space, and the panic related to that. But really, on a replica, checkpoint_timeout is really the only way to keep the pg_xlog directory down in size on a replica.Īdditional reference here: pgsql bugs #7801Īdjusting checkpoint_timeout is unfortunately the best option. You can also set checkpoint_completion_target=0 to adjust the checkpointing behavior as well. I made sure to enable log_checkpoints in my nf and used check_ to monitor the number of segments and replication lag. Set the checkpoint_timeout lower, as you did, to 30 seconds or so. PS2: the article I found about checkpoint_timeout on standby answer your questions: PS: Feel free to ask me more info, it's my first post. Is it a normal size or is it way too small ? I don't have much experience with pg in real world. How did you handle restart point when it was triggered only by checkpoint_timeout?Ĭan I do something else PostgreSQL wise than this ugly workaround ? It's ugly but somehow the checkpoint timing are in synch and the standby doesn't "desync". Since checkpoints every 5s is "a bit too much", I tried to set checkpoint_segments to 50 on the primary and checkpoint_timeout to 30s in the standby. The result is that my standby doesn't recycle its WAL quickly enough and the FS becomes full before the next checkpoint. The primary checkpoints a lot (every 5 sec or so) because of the WAL activity (triggered by checkpoint_segments (10) ). I found out that with this version of PostgreSQL, the restartpoints are triggered only because of the checkpoint_timeout (5 min) parameter on the standby. I could just increase the FS but I would like to understand. The standby desynchronizes because the pg_xlog becomes full. We have a few batches that perform some heavy writes, resulting in a lot of WAL bein produced in a short period of time. I am using PostgreSQL 9.1.16 (and I cannot upgrade) with Hot Standby.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |