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

Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> · Sat, 31 Jul 2021 11:08:38 +0530

On Sat, Jul 31, 2021, 4:00 AM Dhanush D <dhanushdk94@xxxxxxxxx> 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 oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('793397'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('793396'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_class oids
SELECT 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.