BUG #15183: pg_upgrade and pg_upgrade.exe both corrupt sequences when migrating 9.1 10.3

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: decalod85(at)gmail(dot)com
Subject: BUG #15183: pg_upgrade and pg_upgrade.exe both corrupt sequences when migrating 9.1 10.3
Date: 2018-05-01 19:47:43
Message-ID: 152520406365.31703.13069551450249013430@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15183
Logged by: Jared Dahl
Email address: decalod85(at)gmail(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2018-05-01 20:29:33 Re: BUG #15180: Alter table add column if not exists with unique constraint will add extra duplicate
Previous Message David G. Johnston 2018-05-01 19:44:22 Re: power() function in Windows: "value out of range: underflow"