Upgrade to PostgreSQL 13 fails

Question

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 an old issue, 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?

• Daniel Vérité: It seems that you're doing the REVOKE in a database that doesn't have a problem. The premise that your control query should return empty seems wrong. It should return exactly what you show in the question. I think what you need is the opposite: you should find and fix the databases that don't have these exact grants on pg_pltemplate.
• Tombart: @DanielVérité Yes, you're right. I was trying to fix the issue on wrong database.

Answer 1

I wrote a bash script 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.

• Laurenz Albe: Correct. The lesson learned is: don't modify system catalog tables, it is unsupported and can lead to interesting problems.