主页 / dba / 问题 / 306121
Accepted
Tombart
Asked: 2022-01-16 11:16:06 +0800 CST

升级到 PostgreSQL 13 失败

  • 772

我正在尝试使用以下脚本将 PostgreSQL 12 集群升级到版本 13：

/usr/lib/postgresql/13/bin/pg_upgrade --check \
 --old-datadir=/var/lib/postgresql/12/main \
 --new-datadir=/var/lib/postgresql/13/main \
 --old-bindir=/usr/lib/postgresql/12/bin \
 --new-bindir=/usr/lib/postgresql/13/bin \
 --old-options=' -c config_file=/etc/postgresql/12/main/postgresql.conf' \
 --new-options=' -c config_file=/etc/postgresql/13/main/postgresql.conf' \
 --old-port=5432 \
 --new-port=5433

支票返回：

*Clusters are compatible*

然而，在实际升级过程中，由于pg_catalog.pg_pltemplate表：

pg_restore: creating ACL "pg_catalog.TABLE "pg_pltemplate""
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 17728; 0 0 ACL TABLE "pg_pltemplate" postgres
pg_restore: error: could not execute query: ERROR:  relation "pg_catalog.pg_pltemplate" does not exist

这似乎是一个老问题，但是升级脚本不会检查这些模板。

到目前为止，这个查询似乎应该返回一个空结果，否则你会遇到麻烦：

$ psql -c "SELECT * FROM information_schema.role_table_grants WHERE table_name='pg_pltemplate';"
 grantor  | grantee  | table_catalog | table_schema |  table_name   | privilege_type | is_grantable | with_hierarchy 
----------+----------+---------------+--------------+---------------+----------------+--------------+----------------
 postgres | postgres | postgres      | pg_catalog   | pg_pltemplate | TRIGGER        | YES          | NO
 postgres | postgres | postgres      | pg_catalog   | pg_pltemplate | REFERENCES     | YES          | NO
 postgres | postgres | postgres      | pg_catalog   | pg_pltemplate | TRUNCATE       | YES          | NO
 postgres | postgres | postgres      | pg_catalog   | pg_pltemplate | DELETE         | YES          | NO
 postgres | postgres | postgres      | pg_catalog   | pg_pltemplate | UPDATE         | YES          | NO
 postgres | postgres | postgres      | pg_catalog   | pg_pltemplate | SELECT         | YES          | YES
 postgres | postgres | postgres      | pg_catalog   | pg_pltemplate | INSERT         | YES          | NO
 postgres | PUBLIC   | postgres      | pg_catalog   | pg_pltemplate | SELECT         | NO           | YES

撤销这些特权：

REVOKE SELECT ON "pg_catalog"."pg_pltemplate" FROM PUBLIC;
REVOKE ALL ON "pg_catalog"."pg_pltemplate" FROM postgres;

REVOKE由于语句被保存到架构中，因此并没有真正的帮助：

pg_restore: error: could not execute query: ERROR:  relation "pg_catalog.pg_pltemplate" does not exist
Command was: REVOKE ALL ON TABLE "pg_catalog"."pg_pltemplate" FROM "postgres";
REVOKE SELECT ON TABLE "pg_catalog"."pg_pltemplate" FROM PUBLIC;

可以使用以下方法检查（结果也应该为空）：

pg_dump --port 5432 --schema-only --quote-all-identifiers | grep pg_pltemplate

在执行升级之前。

任何想法如何完全摆脱pg_catalog.pg_pltemplate桌子？

postgresql upgrade

1 个回答

  1. Best Answer

    我编写了一个bash 脚本来执行额外的升级检查（它不会替换pg_upgrade --check）。role_table_grants对有关的任何非默认修改pg_pltemplate都会在处理过程中导致致命错误pg_upgrade，基本上它会执行以下操作：

    for db in $(psql -tc "SELECT datname FROM pg_database;")
do
  if [[ "${db}" != "template0" ]]; then
    dump=$(pg_dump --schema-only --quote-all-identifiers ${db} | grep pg_pltemplate)
    if [ ! -z "$dump" ]; then
        echo "ERROR: ${db} contains pg_pltemplate modifications. pg_upgrade will fail"
        exit 1
    fi
  fi
done
echo "OK"

    该脚本可能会返回如下内容：

    -- Name: TABLE "pg_pltemplate"; Type: ACL; Schema: pg_catalog; Owner: postgres
REVOKE ALL ON TABLE "pg_catalog"."pg_pltemplate" FROM "postgres";
REVOKE SELECT ON TABLE "pg_catalog"."pg_pltemplate" FROM PUBLIC;
GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "reader";
ERROR: postgres contains pg_pltemplate modifications. pg_upgrade will fail

    在这种情况下，您必须运行相反的语句：

    GRANT ALL ON TABLE "pg_catalog"."pg_pltemplate" to "postgres";
GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO PUBLIC;
REVOKE SELECT ON TABLE "pg_catalog"."pg_pltemplate" FROM "reader";

    那么数据库应该已准备好升级到 PostgreSQL 13。

    • 1

相关问题