0

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 heap's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET 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 oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('1949074'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_type oid
SELECT 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 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?

Dhanush D
  • 75
  • 12
  • It loos like the server crashed. What was in pg_upgrade_server.log about a crash or immediate shutdown? – jjanes Jul 30 '21 at 03:31
  • There's nothing helpful in the logs. It looks like the server starts and stop for the old and new data files. pg_upgrade_server.log file - https://textuploader.com/ta9o3 – Dhanush D Jul 30 '21 at 16:17
  • When I run the upgrade, all the stops I see are done with `-m smart` , while several in your log are done with `-m fast`. I bet your problem is related to that difference, although I don't understand the cause of that difference. – jjanes Jul 30 '21 at 16:32
  • It looks like you had the logging collector turned on for the new system before running the upgrade. That means some of the interesting log entries might have been diverted from pg_upgrade_server.log and instead ended up in `//12/data/log/`. So the log files in there might hold the key. – jjanes Jul 30 '21 at 16:47
  • Did you use `pg_upgrade` too? I think `-m fast` is triggered when there's a failure detected during the upgrade. – Dhanush D Jul 30 '21 at 16:48
  • Thanks for the pointer to `//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 "ts_demand_input_details_id_seq" already exists ``` Maybe it's related "logical replication launcher" ? – Dhanush D Jul 30 '21 at 16:51

0 Answers0