升級到 PostgreSQL 13 失敗

我正在嘗試使用以下腳本將 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桌子？

我編寫了一個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。

引用自：https://dba.stackexchange.com/questions/306121