Thread: pg_upgrade --check doesn't check pg_pltemplate modifications
Hi,
when upgrading postgresql 12 -> 13 the pg_upgrade --check doesn't sufficiently check for pg_pltemplate modifications (at least in version 13.5-1.pgdg110+1).
There's been a discussion on the mailing list https://postgrespro.com/list/thread-id/2521269 regarding this topic. But it doesn't seem to be implemented in the upgrade process.
Here's a simple bash script that checks for pg_pltemplate modifications on each database:
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"
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"
Any non-default GRANT/REVOKE like these:
REVOKE SELECT ON TABLE "pg_catalog"."pg_pltemplate" FROM PUBLIC;
GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "reader";
REVOKE SELECT ON TABLE "pg_catalog"."pg_pltemplate" FROM PUBLIC;
GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "reader";
would break the upgrade process, even though the pg_upgrade --check says:
*Clusters are compatible*
*Clusters are compatible*
then upgrade (at least with --link) fails
pg_restore: error: could not execute query: ERROR: relation "pg_catalog.pg_pltemplate" does not exist
Including such checks in the upgrade process might make the upgrade path much easier.
Tomas Barton
Tomas Barton <barton.tomas@gmail.com> writes:
> Any non-default GRANT/REVOKE like these:
> REVOKE SELECT ON TABLE "pg_catalog"."pg_pltemplate" FROM PUBLIC;
> GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "reader";
Uh ... why would you do that? It seems only academically
different from randomly deleting some catalog entries.
> would break the upgrade process, even though the pg_upgrade --check says:
> *Clusters are compatible*
pg_upgrade has never promised to detect every possible problem.
Considering that pg_pltemplate is gone entirely in v13 and up,
I doubt we're going to want to invest a lot of effort here.
regards, tom lane