Re: difference between checkpoint_segments and wal_keep_segments in postgres configuration file

Thanks a lot.

On Wed 11 Apr, 2018, 9:07 AM Michael Paquier, <michael(at)paquier(dot)xyz> wrote:

> 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
>