Upgrading postgres 11 to 12

Question

im trying to migrate my postgres 11 instance to 12, following https://stackoverflow.com/a/62198992 but im stuck. I've made a backup, installed postgres 12

ran check

/usr/lib/postgresql/12/bin/pg_upgrade --old-datadir=/var/lib/postgresql/11/main --new-datadir=/var/lib/postgresql/12/main --old-bindir=/usr/lib/postgresql/11/bin --new-bindir=/usr/lib/postgresql/12/bin --old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' -O "-c timescaledb.restoring=on" --check

which returned bunch of 'ok' and Clusters are compatible

then during upgrade itself, im getting error:

pg_restore: from TOC entry 5992; 0 0 ACL FUNCTION "abstime"(timestamp without time zone) postgres
pg_restore: error: could not execute query: ERROR:  function pg_catalog.abstime(timestamp without time zone) does not exist
Command was: GRANT ALL ON FUNCTION "pg_catalog"."abstime"(timestamp without time zone) TO "admin";

Tried googling around, but with no success. Any hints?

__

i dumped schema and thats where i find abstime

GRANT ALL ON FUNCTION pg_catalog.abstime(timestamp without time zone) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstime(timestamp with time zone) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstimeeq(abstime, abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstimege(abstime, abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstimegt(abstime, abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstimein(cstring) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstimele(abstime, abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstimelt(abstime, abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstimene(abstime, abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstimeout(abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstimerecv(internal) TO admin;
GRANT ALL ON FUNCTION pg_catalog.abstimesend(abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.btabstimecmp(abstime, abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.date(abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.date_part(text, abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.intinterval(abstime, tinterval) TO admin;
GRANT ALL ON FUNCTION pg_catalog.isfinite(abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.mktinterval(abstime, abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog."time"(abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.timemi(abstime, reltime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.timepl(abstime, reltime) TO admin;
GRANT ALL ON FUNCTION pg_catalog."timestamp"(abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.timestamptz(abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.tinterval(abstime, abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.max(abstime) TO admin;
GRANT ALL ON FUNCTION pg_catalog.min(abstime) TO admin;

__

edit 2

digging deeper, ive found out that columns valuntil in pg_shadow and pg_user are of abstime type can anything be done about it?

Answer 1

The existence of columns of abstime type in the old system catalog tables does not matter. System catalogs are not transferred during the upgrade, they are part of the "empty" cluster you need to create with initdb, and so will have a different type in the new system.

You need to revoke the grant to "admin" on the old cluster before you can complete the upgrade. It is not clear to me where that grant come from in the first place, as that catalog function is automatically granted to public so there is no need for a grant to a specific role.

You might consider it a bug that --check did not catch this problem. Although I don't know if it is reasonable to expect it to catch every conceivable problem.