Re: Replication sequence

From: Paolo Saudin <paolosaudin(at)gmail(dot)com>
To: Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com>
Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: Replication sequence
Date: 2021-02-16 12:10:54
Message-ID: CADtZQgmtt4=+sPo_SbH5wr-LpavHgpbS78fNpxMLi8DU2kp8Fg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Il giorno mar 16 feb 2021 alle ore 10:51 Jehan-Guillaume de Rorthais <
jgdr(at)dalibo(dot)com> ha scritto:

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

Thank you very much!
So in case the primary server crashes, and the backup one gets live, it
will eventually have different sequences. I suppose there is no way to
avoid this, isnt'it?
Paolo

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jehan-Guillaume de Rorthais 2021-02-16 12:38:08 Re: Replication sequence
Previous Message Thomas Kellerer 2021-02-16 11:22:36 Re: PostgreSQL occasionally unable to rename WAL files (NTFS)