Setting wal_keep_segments for PostgreSQL hot_standby

Question

I'm having trouble setting up PostgreSQL hot_standby. When I try to start the database after running pg_basebackup, I get FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000001000006440000008D has already been removed in postgresql.log. After a brief discussion in IRC, I realized that the error most likely is due to the wal_keep_segments being too low for my intensive write database.

How can I calculate, if possible, the correct setting for wal_keep_segments ? What is an acceptable value for this parameter?

What I work with:

• Postgresql 9.3
• Debian 7.6

Answer 1

wal_keep_segments can be estimated as the average number of new WAL segments per minute in the pg_xlog directory times the number of minutes you want to be safe in. Keep in mind that this speed is expected to increase after wal_level changes from its default value of minimal to archive or hot_standby . The only cost is disk space, which by default is 16 MB per segment.

Usually I use permissions 2 as values. At a speed of about 1 segment per minute, a value of 256 gives me about 4 hours to set up standby mode.

Alternatively, you can use the WAL streaming using pg_basebackup . This corresponds to the --xlog-method=stream option. Unfortunately, at least since 2013, for a discussion on the PostgreSQL mailing list , setting wal_keep_segments to a non-zero value can still be recommended - this is to prevent the risk that the thread will not keep up with the times. If you are using pg_basebackup , but also do not forget --checkpoint=fast .