Thread: difference between checkpoint_segments and wal_keep_segments inpostgres 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