Upgrade to PostgreSQL 13 fails



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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2