Thread: Replication sequence

Replication sequence

From

Paolo Saudin

Date:

15 February 2021, 20:55:14

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 │
└───────┘

Re: Replication sequence

From

Loles

Date:

15 February 2021, 22:40:41

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 │
└───────┘

Re: Replication sequence

From

Paolo Saudin

Date:

16 February 2021, 09:27:04

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

Re: Replication sequence

From

Jehan-Guillaume de Rorthais

Date:

16 February 2021, 12:51:45

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

Re: Replication sequence

From

Paolo Saudin

Date:

16 February 2021, 15:10:54

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

Re: Replication sequence

From

Jehan-Guillaume de Rorthais

Date:

16 February 2021, 15:38:08

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.

Re: Replication sequence

From

Paolo Saudin

Date:

16 February 2021, 17:28:48

Thank you!

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

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.