Replication sequence

One thing is the number of records in the table and another is the current
value of the sequence.

Execute:

select max (field) from bobo.menu_pages;

In both master and slave server.

What give you back?

"field" is the data that belongs to the sequence.

El lun., 15 feb. 2021 18:55, Paolo Saudin <paolosaudin@gmail.com> escribió:

Il giorno lun 15 feb 2021 alle ore 20:40 Loles <lolesft@gmail.com> ha
scritto:

One thing is the number of records in the table and another is the current
value of the sequence.

Execute:

select max (field) from bobo.menu_pages;

In both master and slave server.

What give you back?

"field" is the data that belongs to the sequence.

El lun., 15 feb. 2021 18:55, Paolo Saudin <paolosaudin@gmail.com>
escribió:

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?

Thanks,
Paolo

--
-- primary server
--
postgres@db-srv1:~$ psql
psql (9.5.19)
cf-db-srv1:5432 postgres@rete_llpp=# SELECT last_value FROM
bobo.menu_pages_mp_id_seq;
┌────────────┐
│ last_value │
├────────────┤
│ 74 │
└────────────┘
cf-db-srv1:5432 postgres@rete_llpp=# select count(*) from
bobo.menu_pages;
┌───────┐
│ count │
├───────┤
│ 74 │
└───────┘

--
-- replica server
--
postgres@db-srv2:~$ psql
psql (9.5.24)
cf-db-srv2:5433 postgres@rete_llpp=# SELECT last_value FROM
bobo.menu_pages_mp_id_seq;
┌────────────┐
│ last_value │
├────────────┤
│ 105 │
└────────────┘
cf-db-srv2:5433 postgres@rete_llpp=# select count(*) from
bobo.menu_pages;
┌───────┐
│ count │
├───────┤
│ 74 │
└───────┘

Hi, I checked and the number of records are identical,

cf-db-srv1:5432 postgres@rete_llpp=# select max(mp_id) from bobo.menu_pages;
┌─────┐
│ max │
├─────┤
│ 74 │
└─────┘

cf-db-srv2:5433 postgres@rete_llpp=# select max(mp_id) from bobo.menu_pages;
┌─────┐
│ max │
├─────┤
│ 74 │
└─────┘
Thank you,
Paolo

On Mon, 15 Feb 2021 18:55:14 +0100
Paolo Saudin <paolosaudin@gmail.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... ?

Il giorno mar 16 feb 2021 alle ore 10:51 Jehan-Guillaume de Rorthais <
jgdr@dalibo.com> ha scritto:

On Mon, 15 Feb 2021 18:55:14 +0100
Paolo Saudin <paolosaudin@gmail.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

On Tue, 16 Feb 2021 13:10:54 +0100
Paolo Saudin <paolosaudin@gmail.com> wrote:

Il giorno mar 16 feb 2021 alle ore 10:51 Jehan-Guillaume de Rorthais <
[...]

[...]
[...]
[...]
[...]
[...]
[...]
[...]

Thank you very much!
So in case the primary server crashes, and the backup one gets live, it
will eventually have different sequences.

No. You will just have a gap in the sequence, on both side.

And if you decided to promote your standby to production, you'll have to resync
the old primary anyway.

I suppose there is no way to
avoid this, isnt'it?

You can't avoid gap.

Thank you!

Il giorno mar 16 feb 2021 alle ore 13:38 Jehan-Guillaume de Rorthais <
jgdr@dalibo.com> ha scritto: