Re: Replication sequence

From: Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com>
To: Paolo Saudin <paolosaudin(at)gmail(dot)com>
Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: Replication sequence
Date: 2021-02-16 09:51:45
Message-ID: 20210216105145.03b5943b@firost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, 15 Feb 2021 18:55:14 +0100
Paolo Saudin <paolosaudin(at)gmail(dot)com> wrote:

> Hi all,
> I have two servers, a primary and a secondary one with a streaming replica
> setup.
> Today I noticed that some sequences are not lined-up, the replica ones are
> well ahead, while the records number is the same. How is it possible?

This is because sequences are not WAL logged on every nextval() call, but by
batch of sequence cache+32 values. As standbys are fed with WAL stream from
primary, their sequences appears jump, then stall, then jump, etc.

See:
https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/commands/sequence.c?id=f672df5fdd22dac14c98d0a0bf5bbaa6ab17f8a5#n52

https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/commands/sequence.c?id=f672df5fdd22dac14c98d0a0bf5bbaa6ab17f8a5#n661

I'm not sure if this is documented somewhere... ?

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guy Burgess 2021-02-16 10:29:01 Re: PostgreSQL occasionally unable to rename WAL files (NTFS)
Previous Message João Gaspar 2021-02-16 09:50:49 Re: [LDAPS] Test connection user with ldaps server