Re: 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