Thread: BUG #15183: pg_upgrade and pg_upgrade.exe both corrupt sequences whenmigrating 9.1 10.3
BUG #15183: pg_upgrade and pg_upgrade.exe both corrupt sequences whenmigrating 9.1 10.3
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15183 Logged by: Jared Dahl Email address: decalod85@gmail.com PostgreSQL version: 10.3 Operating system: Windows 10, CentOS 7.4.1708 3.10.0-693.5.2.el7.x86 Description: Using sequences for a Ruby on Rails app to keep track of unique identifiers. Application is about 9 years old. We bundle postgresql with our application, install and configure it for the user, and manage the migrations ourselves. The application is multi-platform, running on both Linux and Windows. We have been using 9.1.2 for Windows and 9.1.3 for Linux. On the most recent version, after the pg_upgrade ran, we were unable to insert records. Analysis showed that our normally very simple sequences had multiple dependencies attached to them (in the 10.3 database). The 9.1 database only had a single dependency on the sequence. The pattern of which dependencies were added to which sequences was not discernible, but it was the same on both Windows and Linux. I saw no obvious errors in the pg_upgrade output. When using pg_dump and pg_restore with the same databases, the problem did not occur (guessing this has to do with the --binary-upgrade flag that is used by pg_upgrade when calling pg_dump). We are not doing anything fancy, getting an OS shell and running the commands as they are meant to be used. I pulled the SQL using pgAdmin 4 for the below examples. Here is an example SQL for creating one of our sequences : CREATE SEQUENCE public.some_table_id_seq; ALTER SEQUENCE public.some_table_id_seq OWNER TO "role-name"; Here is the definition of the field that uses this sequence from the table. id bigint NOT NULL DEFAULT nextval('some_table_variables_id_seq'::regclass) Here is the pg_upgrade command for windows (Nullsoft installer) pg_upgrade.exe --verbose --old-datadir="$INSTDIR\${PRODUCT_NAME}\${POSTGRESQL_OLDDATADIR}" --old-bindir="$INSTDIR\${PRODUCT_NAME}\${POSTGRESQL_OLDBINDIR}" --new-datadir="$INSTDIR\${PRODUCT_NAME}\${POSTGRESQL_DATADIR}" --new-bindir="$INSTDIR\${PRODUCT_NAME}\${POSTGRESQL_BINDIR}" --old-port=$OLD_DB_PORT --new-port=$NEW_DB_PORT --username=$USERNAME Here is the pg_upgrade command for Linux (from a perl program) $newDBdir/bin/pg_upgrade -b $oldDBdir/bin -d $oldDBdir/data -B $newDBdir/bin -D $newDBdir/data -p $dbPort -P $dbPort -U user
Re: BUG #15183: pg_upgrade and pg_upgrade.exe both corrupt sequences when migrating 9.1 10.3
From
Tom Lane
Date:
=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes: > On the most recent version, after the pg_upgrade ran, we were unable to > insert records. Analysis showed that our normally very simple sequences had > multiple dependencies attached to them (in the 10.3 database). The 9.1 > database only had a single dependency on the sequence. The pattern of which > dependencies were added to which sequences was not discernible, but it was > the same on both Windows and Linux. You haven't really provided enough information to let anyone else reproduce or investigate this. What do you mean by "multiple dependencies attached"? How would that have broken your application? What exactly is the database schema that pg_upgrade failed to reproduce accurately? Ideally, what I'd like to have to avoid wasting time reverse-engineering your problem is a SQL script that creates a 9.1 database that pg_upgrade will then fail on. regards, tom lane