pg_upgrade Log
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for invalid "unknown" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
app_solutions
app
appam
postgres
template1
ok
New cluster database "app_solutions" is not empty: found relation "public.config"
Failure, exiting
pg_upgrade Dump
# cat pg_upgrade_dump_17110.custom
app_solutions9.6.2213.3 (Debian 13.3-1.pgdg100+1)
0ENCODINENCODINGSET client_encoding = 'UTF8';
false00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
false00
SEARCHPATH
SEARCHPATH8SELECT pg_catalog.set_config('search_path', '', false);
app_solutionDATABASEcCREATE DATABASE "app_solutions" WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_US.utf8';
DROP DATABASE "app_solutions";
app_solutionsDATABASE PROPERTIES▒
-- For binary upgrade, set datfrozenxid and datminmxid.
UPDATE pg_catalog.pg_database
SET datfrozenxid = '543', datminmxid = '1'
WHERE datname = 'app_solutions';
postgresfalse00pg_largeobjectpg_largeobject▒
-- For binary upgrade, set pg_largeobject relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '543', relminmxid = '1'
WHERE oid = 2613;
false▒125917113configTABLE▒
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('17115'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('17114'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type toast oid
SELECT pg_catalog.binary_upgrade_set_next_toast_pg_type_oid('17118'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('17113'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_toast_pg_class_oid('17117'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('17119'::pg_catalog.oid);
CREATE TABLE "public"."config" (
"id" integer NOT NULL,
"name" character varying(20) NOT NULL,
"data" "text",
"version" integer,
"timestamp" timestamp without time zone
);
-- For binary upgrade, set heap's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '928', relminmxid = '1'
WHERE oid = '"public"."config"'::pg_catalog.regclass;
-- For binary upgrade, set toast's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '928', relminmxid = '1'
WHERE oid = '17117';
DROP TABLE "public"."config";
config_id_seSEQUENCE▒5917111
-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('17111'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('17112'::pg_catalog.oid);
CREATE SEQUENCE "public"."config_id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
(DROP SEQUENCE "public"."config_id_seq";
config_id_seqSEQUENCE OWNED BYIALTER SEQUENCE "public"."config_id_seq" OWNED BY "public"."config"."id";
publipostgresfalse185▒260417116 config idDEFAULTtALTER TABLE ONLY "public"."config" ALTER COLUMN "id" SET DEFAULT "nextval"('"public"."config_id_seq"'::"regclass");
>ALTER TABLE "public"."config" ALTER COLUMN "id" DROP DEFAULT;
config_id_seqfalse18618518600
SEQUENCE SET@SELECT pg_catalog.setval('"public"."config_id_seq"', 1, false);
publipostgresfalse185▒260617121config unique_name
CONSTRAINT▒
-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('17120'::pg_catalog.oid);
ALTER TABLE ONLY "public"."config"
ADD CONSTRAINT "unique_name" UNIQUE ("name", "version");
BALTER TABLE ONLY "public"."config" DROP CONSTRAINT "unique_name";
Benutzt wird das pg_upgrade
9.6-to-13
von hier:
Wird angewendet auf Postgres in Docker, in Kubernetes.
Es wurden über 10 Datenbanken mit fast demselben Inhalt erfolgreich upgraded.
Bei 2 Datenbanken ist das Upgrade fehlgeschlagen, wie der im Log anzutreffende Fehler zeigt.
Mir ist nicht ganz klar, wie der ganze Upgrade Prozess funktioniert, aber soweit ich das im Dump sehe, wird im Prozess eine Tabelle namens public.config hinzugefügt, in der Datenbank app_solutions, nur damit der ganze pg_upgrade
Prozess fehlschlägt, weil genau diese Datenbank bereits die public.config Tabelle hat.
Ich verstehe nicht was da los ist und würde gern herausfinden, wie ich diese Datenbank erfolgreich von PostgreSQL 9.6 auf PostgreSQL 13 upgraden kann. Danke.