Sequence values change during upgrade

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

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 Wed, 2024-05-15 at 15:17 +0300, Danut Soare wrote:

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

The cache used for sequence creation was 1.

Great. Then everything is working as expected.

Yours,
Laurenz Albe