Thread: Sequence values change during upgrade

Sequence values change during upgrade

From
Danut Soare
Date:
Hi everyone.
 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
changedto a value different from the original value. Not all but some of the sequences have modified value. I’ve
repeatedthe upgrade several times and each time the same sequences have changed with the same values. I’ve expected
thatthe 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

Best regards,
Dănuț Soare




Re: Sequence values change during upgrade

From
Laurenz Albe
Date:
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



Re: Sequence values change during upgrade

From
Danut Soare
Date:
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 <laurenz.albe@cybertec.at> wrote:
>
> 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




Re: Sequence values change during upgrade

From
Laurenz Albe
Date:
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