I am using PostgreSQL 9.1.16 (and I cannot upgrade) with Hot Standby.
We have a few batches that perform some heavy writes, resulting in a lot of WAL bein produced in a short period of time.
The standby desynchronizes because the pg_xlog
becomes full.
I could just increase the FS but I would like to understand.
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.
The primary checkpoints a lot (every 5 sec or so) because of the WAL activity (triggered by checkpoint_segments
(10) ). The result is that my standby doesn't recycle its WAL quickly enough and the FS becomes full before the next checkpoint.
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. It's ugly but somehow the checkpoint timing are in synch and the standby doesn't "desync".
My questions are:
In 9.1.16 and before. How did you handle restart point when it was triggered only by
checkpoint_timeout
?Can I do something else PostgreSQL wise than this ugly workaround ?
I don't have much experience with pg in real world. my
pg_xlog
FS is 5G. Is it a normal size or is it way too small ?
PS: Feel free to ask me more info, it's my first post.
PS2: the article I found about checkpoint_timeout
on standby http://www.postgresql.org/message-id/[email protected]