pg_upgrade issue upgrading 10 -> 13

Sean Brown schrieb am 01.10.2020 um 16:51:

I’m having a little problem using pg_upgrade to move from 10 to 13,
I’m assuming the issue is related to the removal of pg_pltemplate,
but I can’t find anything related to how to handle it.

pg_upgrade —check reports that the clusters are compatible, but the
actual upgrade fails starting the new cluster with -

ERROR: relation “pg_catalog.pg_pltemplate” does not exist STATEMENT:
GRANT SELECT ON TABLE “pg_catalog"."pg_pltemplate” TO "appuser”;

Is there a way to deal with this that doesn’t include dropping the
source table?

Does revoking the privilege before running pg_upgrade help?

So in the v10 database:

revoke select on pg_catalog.pg_pltemplate from appuser;

On Thu, Oct 1, 2020 at 05:28:53PM +0200, Thomas Kellerer wrote:

Sean Brown schrieb am 01.10.2020 um 16:51:

I’m having a little problem using pg_upgrade to move from 10 to 13,
I’m assuming the issue is related to the removal of pg_pltemplate,
but I can’t find anything related to how to handle it.

pg_upgrade —check reports that the clusters are compatible, but the
actual upgrade fails starting the new cluster with -

ERROR: relation “pg_catalog.pg_pltemplate” does not exist STATEMENT:
GRANT SELECT ON TABLE “pg_catalog"."pg_pltemplate” TO "appuser”;

Is there a way to deal with this that doesn’t include dropping the
source table?

Does revoking the privilege before running pg_upgrade help?

So in the v10 database:

revoke select on pg_catalog.pg_pltemplate from appuser;

Yeah, there must be a reference to pg_catalog.pg_pltemplate somewhere
that was missed. I think a simple dump/restore would also error on the
restore, but a normal restore might ignore the error, while pg_upgrade
will not.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On 10/1/20 7:51 AM, Sean Brown wrote:

I’m having a little problem using pg_upgrade to move from 10 to 13, I’m assuming the issue is related to the removal of pg_pltemplate, but I can’t find anything related to how to handle it.

pg_upgrade —check reports that the clusters are compatible, but the actual upgrade fails starting the new cluster with -

ERROR: relation “pg_catalog.pg_pltemplate” does not exist
STATEMENT: GRANT SELECT ON TABLE “pg_catalog"."pg_pltemplate” TO "appuser”;

Which points to the dangers of doing things to the system tables. They
can change/disappear between major versions.

Is there a way to deal with this that doesn’t include dropping the source table?

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Oct 1, 2020 at 09:03:31AM -0700, Adrian Klaver wrote:

On 10/1/20 7:51 AM, Sean Brown wrote:

I’m having a little problem using pg_upgrade to move from 10 to 13, I’m assuming the issue is related to the removal of pg_pltemplate, but I can’t find anything related to how to handle it.

pg_upgrade —check reports that the clusters are compatible, but the actual upgrade fails starting the new cluster with -

ERROR: relation “pg_catalog.pg_pltemplate” does not exist
STATEMENT: GRANT SELECT ON TABLE “pg_catalog"."pg_pltemplate” TO "appuser”;

Which points to the dangers of doing things to the system tables. They can
change/disappear between major versions.

And pg_dump (used by pg_upgrade) had little handling for such changes.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee