Обсуждение: Sequence values change during upgrade
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
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 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
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