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

From: Dhanush D <dhanushdk94(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Cc: Dhanush D <dhanushdk94(at)gmail(dot)com>
Subject: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command
Date: 2021-07-30 19:22:15
Message-ID: CAMpFLfP9UN=mHX9hLUBg04MY8r1awsUktN5CMfeiq-J7mQFDNA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 heap's relfrozenxid and relminmxidUPDATE
pg_catalog.pg_classSET relfrozenxid = '1491955518', relminmxid =
'1'WHERE oid = '"schema1"."table3"'::pg_catalog.regclass;

Another log file:

pg_restore: creating TABLE "schema99.table1"
pg_restore: creating SEQUENCE "schema99.sequence1"
pg_restore: creating SEQUENCE OWNED BY "schema99.sequence1"
pg_restore: creating TABLE "schema99.table2"
pg_restore: creating SEQUENCE "schema99.sequence2"
pg_restore: creating SEQUENCE OWNED BY "schema99.sequence2"
pg_restore: creating SEQUENCE "schema99.sequence2"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 347; 1259 1949074 SEQUENCE sequence2 postgres
pg_restore: error: could not execute query: ERROR: relation
"sequence2" already exists
Command was:-- For binary upgrade, must preserve pg_class oidsSELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('1949074'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type oidSELECT
pg_catalog.binary_upgrade_set_next_pg_type_oid('1949075'::pg_catalog.oid);
CREATE SEQUENCE "schema99"."sequence2"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

Restore command from the logs:

"/usr/pgsql-12/bin/pg_restore" --host /var/lib/pgsql --port 50432
--username postgres --create --exit-on-error --verbose --dbname
template1 "pg_upgrade_dump_16405.custom" >>
"pg_upgrade_dump_16405.log" 2>&1

Similarly, at each run a different dump file fails (
pg_upgrade_dump_16405.custom/pg_upgrade_dump_16404.custom). Has anyone
faced similar issues during upgrade to Postgres 12.7?

There isn't much in the pg_upgrade_server.log file. It looks like the
server starts and stops for the old and new data files.
pg_upgrade_server.log file - textuploader.com/ta9o3

Under /<path>/12/data/log. I see these lines:

```
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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Haas 2021-07-30 19:47:53 Re: Have I found an interval arithmetic bug?
Previous Message Bruce Momjian 2021-07-30 19:20:10 Re: Have I found an interval arithmetic bug?