Subject: Re: Sequence values change during upgrade

From: Laurenz Albe <[email-masked]>
Date: Wed, 15 May 2024 13:01:30 +0200

On Wed, 2024-05-15 at 11:03 +0300, Danut Soare wrote:
>  I’m upgrading the PostgreSQL version from 12 to 16 using pg_upgrade utility
> with -k option. After upgrading the database, before starting any application
> processing, on checking the last value of some sequences, the value has been
> changed to a value different from the original value. Not all but some of the
> sequences have modified value. I’ve repeated the upgrade several times and
> each time the same sequences have changed with the same values. I’ve expected
> that the upgrade would not interfere with the application data … 
> This is the upgrade command:
> 
> /usr/pgsql-16/bin/pg_upgrade \
> —old-datadir=/u01/pgrdb/12/app \
> —new-data for=/u01/pgrdb/16/app \
> —old-bindir=/usr/pgsql-12/bin \
> —new-bindir=/usr/pgsql-16/bin \
> -k

How exactly did you determine the current sequence value before and after
the upgrade?  Was the difference less than 32?  If yes, that is normal,
because sequences don't WAL log every single nextval() call.
Also, what CACHE setting did you use in CREATE SEQUENCE?  That will have an
influence, and the difference could be bigger.

If the sequence moved backward, that would be a bug.  If it moves forward,
that is to be expected and OK.

Yours,
Laurenz Albe

---

From: Danut Soare <[email-masked]>
Date: Wed, 15 May 2024 15:17:27 +0300

Hi,

Thank you for your answer. Here is the requested information.

select last_value from seq_name;

The difference was less than 32 except one sequence with difference equal to 32

The cache used for sequence creation was 1.

Thank you once again!
Dănuț Soare

> On 15 May 2024, at 14:01, Laurenz Albe <[email-masked]> wrote:
>

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 15 May 2024 16:29:28 +0200

> The cache used for sequence creation was 1.

Great.  Then everything is working as expected.

Yours,
Laurenz Albe