Re: difference between checkpoint_segments and wal_keep_segments in postgres configuration file

On Tue, Apr 10, 2018 at 11:06:54PM +0530, Raghavendra Rao J S V wrote:
> I am not clear the difference between checkpoint_segments and
> wal_keep_segments .
>
> I would like to now below things. Please explain.Thanks in advance.
>
> - Difference between *checkpoint_segments *and *wal_keep_segments *
> value
> - Role of *checkpoint_segments *and *wal_keep_segments *
> - Which one should has higher value.

Documentation is king here. For checkpoint_segments:
https://www.postgresql.org/docs/9.4/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS

Maximum number of log file segments between automatic WAL
checkpoints (each segment is normally 16 megabytes). The default is
three segments. Increasing this parameter can increase the amount of
time needed for crash recovery. This parameter can only be set in
the postgresql.conf file or on the server command line.

For wal_keep_segments:
https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER

Specifies the minimum number of past log file segments kept in the
pg_xlog directory, in case a standby server needs to fetch them for
streaming replication. Each segment is normally 16 megabytes. If a
standby server connected to the sending server falls behind by more than
wal_keep_segments segments, the sending server might remove a WAL
segment still needed by the standby, in which case the replication
connection will be terminated. Downstream connections will also
eventually fail as a result. (However, the standby server can recover by
fetching the segment from archive, if WAL archiving is in use.)

Mentioning checkpoint_segments implies that you are using PostgreSQL 9.4
or older versions as this has been removed and replaced by max_wal_size
in 9.5. You should consider upgrading to a newer version.

Hence the first is used in the context of normal operations to decide
the frequency of checkpoints when those are triggered by volume. The
second can be used with streaming replication to give a standby a higher
catchup window. Giving value to one or the other depends on the
context, and both are usable in completely different circumstances.
--
Michael