Re: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command

On Sat, Jul 31, 2021, 4:00 AM Dhanush D <dhanushdk94(at)gmail(dot)com> wrote:

> I am currently upgrading a Postgres cluster running on a Centos machine
> from version 9.6.22 to 12.7. Post the install of 12.7 binaries, I am
> running the pg_upgrade command:
>
> /usr/pgsql-12/bin/pg_upgrade -b /usr/pgsql-9.6/bin/ -B /usr/pgsql-12/bin/
> -d /<path>/9.6/data.old -D /<path>/12/data/ -j 12 -r -v -k
>
> But, I am getting multiple errors during the "Restoring database schemas
> in the new cluster" step:
>
> pg_restore: creating TABLE "schema1.table1"
> pg_restore: creating TABLE "schema1.table2"
> pg_restore: creating TABLE "schema1.table3"
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 358; 1259 793395 TABLE table3 postgres
> pg_restore: error: could not execute query: FATAL: terminating connection due to administrator command
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> Command was:-- For binary upgrade, must preserve pg_type oidSELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('793397'::pg_catalog.oid);
>
> -- For binary upgrade, must preserve pg_type array oidSELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('793396'::pg_catalog.oid);
>
> -- For binary upgrade, must preserve pg_class oidsSELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('793395'::pg_catalog.oid);
> CREATE TABLE "schema1"."table3" (
> "date_key" integer NOT NULL,
> "col1" character varying(32),
> "col2" character varying(32),
> "col3" character varying(32) NOT NULL
> );
> -- For binary upgrade, set
>
>
> ```
> LOG: background worker "logical replication launcher" (PID 35687) exited
> with exit code 1
> LOG: shutting down
> LOG: database system is shut down
> LOG: database system was shut down at 2021-07-30 01:33:51 GMT
> LOG: database system is ready to accept connections ERROR: relation
> "sequence2" already exists
> ```
>
>
> I am getting an error even after only initDB and running upgrade. Any
> suggestions on what could be going wrong?
>
> Thanks in advance!
>

Just asking, is it possible for you to a schema dump from old db and
restore on the newer db, does it throw any errors.
If the db is not really big, maybe dump db with data and restore it on the
newer db server ( use the 12.x postgresql cli for dump restore)

If both these go through fine, then it would be easy to take any unrelated
ddl, data issues out to focus on pg_upgrade.