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
Can I do something else PostgreSQL wise than this ugly workaround ?
I don't have much experience with pg in real world. my
pg_xlogFS 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/AANLkTimpudRqHf8kFfQZxyMHsULYo79qwK5PCXxP21gt@mail.gmail.com