Upgrade to PostgreSQL 13 fails

[Kadir]

I'm trying to upgrade PostgreSQL 12 cluster to version 13, with following script:

/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

The check returns:

*Clusters are compatible*

However during the actual upgrade the process fails miserably due to pg_catalog.pg_pltemplate table:

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

It appears to be https://postgrespro.com/list/thread-id/2521269 , however the upgrade script does not check these templates.

So far it appears that this query should return an empty result, otherwise you're in troubles:

$ 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

REVOKING these privileges:

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

doesn't really help as the REVOKE statement gets saved to schema:

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;

this can be checked (also the result should be empty) using:

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

before performing the upgrade.

Any ideas how to get rid of the pg_catalog.pg_pltemplate table altogether?

[Raziyah00]

I wrote a https://gist.github.com/deric/0e45b86650f94f144084c0007ceb15c7 to perform additional upgrade checks (it does NOT replace pg_upgrade --check). Any non-default modifications to role_table_grants regarding pg_pltemplate would cause a fatal error during pg_upgrade process, basically it does following:

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"

The script might return something like this:

-- 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

in that case you have to run the inverse statements:

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";

then the database should be ready for upgrade to PostgreSQL 13.