Setting wal_keep_segments for PostgreSQL hot_standby

Question

I am having a trouble setting up a PostgreSQL hot_standby. When attempting to start the database after running pg_basebackup, I receive, FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000001000006440000008D has already been removed, in the postgresql.log. After a brief discussion in IRC, I came to understand the error likely originates from a too low wal_keep_segments setting for my write intensive database..

How might I calculate, if possible, the proper setting for wal_keep_segments? What is an acceptable value for this setting?

What I am working with:

• Postgresql 9.3
• Debian 7.6

Answer 1

wal_keep_segments could be estimated as the average number of new WAL segments per minute in the pg_xlog directory multiplied by the number of minutes across which you want to be safe for. Bear in mind that the rate is expected to increase after wal_level is changed from its default value of minimal to either archive or hot_standby. The only cost is disk space, which as you know by default is 16 MB per segment.

I typically use powers of 2 as values. At the rate of about 1 segment per minute, a value of 256 gives me about 4 hours in which to set up the standby.

You could alternatively consider using WAL streaming with pg_basebackup. This is per its --xlog-method=stream option. Unfortunately, at least as of 2013, per a discussion on a PostgreSQL mailing list, setting wal_keep_segments to a nonzero value may still be recommended - this is to prevent risking the stream from being unable to keep up. If you do use pg_basebackup though, also don't forget --checkpoint=fast.