wal_keep_segments why minimum, not maximum?

Question

According to docs

wal_keep_segments (integer) Specifies the minimum number of past log file segments kept in the pg_xlog directory

Meanwhile in my experience - you create a slave and change wal_keep_segments from default to let it be 64, and observe as the number of xlogs starts to grow untill reaches 64 files. Which I assume as maximum, not minimum.

Then if you create a transaction that exceeds 16M*64=1GB slave is broken saying it needs removed WAL file. Because the MAXIMUM number of files is less then it is needed, right?.. So the question: why MINIMUM? Why not MAXIMUM?

Update: AS documentation states in first sentence I'm talking about streaming replication

These settings control the behavior of the built-in streaming replication feature

master, not slave (no cascaded replication)

18.6.1. Sending Server(s)

archive_command is "do-nothing" cd . and restore_command in recovery.conf is not set up at all

Answer 1

To answer your question directly, why minimum and why not maximum? Because the new WAL segments can grow faster than the RemoveOldXlogFiles(_logSegNo, recptr) function can delete the old ones.

Also, the formula for calculating the probable number of WAL segments in the docs is wrong. I always have a few more WALs than checkpoint_segments + wal_keep_segments + 1 A much more accurate formula is this: wal_keep_segments + 2 * checkpoint_segments + 1

There's an old-ish, but really good post on this here: http://www.postgresql.org/message-id/CAECtzeUeGhwCiNTishH=+kxhiepJsHu7EO0J6-LEVO-ek5oPkg@mail.gmail.com

If you do massive inserts, your WAL segments will grow faster than they can be removed. This got me just this week. I expected the pg_xlog to maintain a relatively constant size. There was a large process run at night and when I got to work the following morning, my postgres instance crashed because the volume I mounted to plop those WALs on was completely full. Postgres filled the volume, tried to write even more WALs, could not, and abruptly died. Luckily we run replicas behind pgpool2.

If you have a curious mind, I encourage you to browse the postgres source code. It's giant and in C, but the code comments really help. This file in particular is enlightening as it gets into the nuts and bolts of how checkpointing works and how removing old WAL segments happens: https://github.com/postgres/postgres/blob/master/src/backend/access/transam/xlog.c

Answer 2

the number of xlogs starts to grow untill reaches 64 files. Which I assume as maximum, not minimum.

No, it's not a maximum. The formula for the maximum is given in the documentation at http://www.postgresql.org/docs/current/static/wal-configuration.html

There will always be at least one WAL segment file, and will normally not be more than (2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1 files. Each segment file is normally 16 MB (though this size can be altered when building the server). You can use this to estimate space requirements for WAL.

The problem you mentioned about a slave requiring deleted WAL files should be looked in context, that is, how is log shipping configured or is it not configured at all, and if you're using Hot Standby or Streaming Replication.

See https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial for explanations that might be easier to digest than the main documentation.

Answer 3

It is minumum because WAL files are kept in case you need to recovery, they can be more than wal_keep_segments for a short period, but never be less because the number of WAL files determine how much a standby server can fall behind before being unable to catch up.