sql - Postgres 9.6 到 12.7 pg_upgrade 错误 - 由于管理员命令而终止连接
问题描述
我目前正在将在 Centos 机器上运行的 Postgres 集群从版本 9.6.22 升级到 12.7。安装 12.7 二进制文件后,我正在运行 pg_upgrade 命令:
/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
但是,我在“在新集群中恢复数据库模式”步骤中遇到了多个错误:
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;
另一个日志文件:
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;
从日志中恢复命令:
"/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
同样,在每次运行时,不同的转储文件都会失败 ( pg_upgrade_dump_16405.custom
/ pg_upgrade_dump_16404.custom
)。有没有人在升级到 Postgres 12.7 时遇到过类似的问题?
解决方案
推荐阅读
- java - 解析 xml 文件时出现异常(3 字节 UTF-8 序列的字节 2 无效)
- python - 寻找一个 dnf 插件来访问 s3 托管的 repos
- c# - 使用 C# 以编程方式在 Xamarin 上编写对象 ID 名称
- javascript - 我想修改这个文本
- javascript - matNoDataRow 显示“未找到数据”,即使数据源(Angular 10.0.8)中有记录使用角度材料在多个选项卡之间切换
- github - 在 PR 合并后,有没有办法在另一个 repo 中的 PR 上触发 CircleCI 工作流程?
- sql - 在 PostgreSQL 中如何检索具有特殊字符和字母的列
- javascript - 使用 Javascript 显示每个按钮单击的 HTML 内容
- java - 将 Qute TypeSafe 模板与 Kotlin 一起使用时出现 java.lang.UnsatisfiedLinkError
- c++ - 在 Windows 上使用 _vscwprintf 处理 utf-8 编码字符串时失败