Thread: Error; pg_upgrade 10 to 15

Error; pg_upgrade 10 to 15

From

SOzcn

Date:

17 July, 16:24:04

Hello Folks,

We are upgrading from version 10 to 15. We encountered the following error for the first time. Can you give any ideas on how to solve this error? Since it is a system table, we cannot intervene on it.

pg_restore: from TOC entry 3312; 0 0 ACL TABLE "pg_pltemplate" postgres
pg_restore: error: could not execute query: ERROR: relation "pg_catalog.pg_pltemplate" does not exist
Command was: GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "username";

We upgraded many Postgresql in the same way. But this error just blew up somehow.
For this cluster, we upgraded 10 to 11 and then 11 to 12 without a problem, but the same error comes after 12 to 13 and 12 to 15.

The other issue is that the check is successful when we check the cluster for the upgrade.

The Upgrade Command;

/usr/lib/postgresql/15/bin/pg_upgrade
--old-bindir /usr/lib/postgresql/12/bin/
--new-bindir /usr/lib/postgresql/15/bin/
--old-datadir /data/rac/12/main/
--new-datadir /data/rac/15/main2
--old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'
--new-options '-c config_file=/etc/postgresql/15/main2/postgresql.conf' --check

Re: Error; pg_upgrade 10 to 15

From

Ron Johnson

Date:

17 July, 16:34:30

On Wed, Jul 17, 2024 at 9:24 AM SOzcn <selahattinozcnma@gmail.com> wrote:

Hello Folks,

We are upgrading from version 10 to 15. We encountered the following error for the first time. Can you give any ideas on how to solve this error? Since it is a system table, we cannot intervene on it.

pg_restore: from TOC entry 3312; 0 0 ACL TABLE "pg_pltemplate" postgres
pg_restore: error: could not execute query: ERROR: relation "pg_catalog.pg_pltemplate" does not exist
Command was: GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "username";

We upgraded many Postgresql in the same way. But this error just blew up somehow.
For this cluster, we upgraded 10 to 11 and then 11 to 12 without a problem, but the same error comes after 12 to 13 and 12 to 15.

https://www.postgresql.org/docs/12/catalog-pg-pltemplate.html

Looks like that table was removed after PG12:

https://www.postgresql.org/docs/release/13.0/

https://www.postgresql.org/docs/13/upgrading.html

Since the error was on the GRANT statement, REVOKE SELECT on that table and the pg_upgrade should succeed.

The other issue is that the check is successful when we check the cluster for the upgrade.

The Upgrade Command;

/usr/lib/postgresql/15/bin/pg_upgrade
--old-bindir /usr/lib/postgresql/12/bin/
--new-bindir /usr/lib/postgresql/15/bin/
--old-datadir /data/rac/12/main/
--new-datadir /data/rac/15/main2
--old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'
--new-options '-c config_file=/etc/postgresql/15/main2/postgresql.conf' --check

Re: Error; pg_upgrade 10 to 15

From

Holger Jakobs

Date:

17 July, 16:53:39

Just general remarks:

1. There is no need to upgrade to intermediate major versions. Go from 10.(lastest minor) directly to 16.3

2. Why would you want to upgrade to 15 when 16 is already out.

Regards,
Holger

Am 17. Juli 2024 15:24:04 MESZ schrieb SOzcn <selahattinozcnma@gmail.com>:

Hello Folks,

We are upgrading from version 10 to 15. We encountered the following error for the first time. Can you give any ideas on how to solve this error? Since it is a system table, we cannot intervene on it.

pg_restore: from TOC entry 3312; 0 0 ACL TABLE "pg_pltemplate" postgres
pg_restore: error: could not execute query: ERROR: relation "pg_catalog.pg_pltemplate" does not exist
Command was: GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "username";

We upgraded many Postgresql in the same way. But this error just blew up somehow.
For this cluster, we upgraded 10 to 11 and then 11 to 12 without a problem, but the same error comes after 12 to 13 and 12 to 15.

The other issue is that the check is successful when we check the cluster for the upgrade.

The Upgrade Command;

/usr/lib/postgresql/15/bin/pg_upgrade
--old-bindir /usr/lib/postgresql/12/bin/
--new-bindir /usr/lib/postgresql/15/bin/
--old-datadir /data/rac/12/main/
--new-datadir /data/rac/15/main2
--old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'
--new-options '-c config_file=/etc/postgresql/15/main2/postgresql.conf' --check

Re: Error; pg_upgrade 10 to 15

From

Zaid Shabbir

Date:

17 July, 17:13:02

Hello SOzcn,

You are trying to upgrade from each major PostgreSQL versions one by one but as per the PostgreSQL documentation you can directly perform the pg_upgrade from any older major version to latest supported version [Link]

Other than pg_upgrade, PostgreSQL supports a couple of other upgradaing mechanisms like 'upgrading data via pg_dumpall',' upgradaing data via Replication'. For more details you can view the official documentation.

Regard,

Zaid

Bitnine Global Inc.

On Wed, Jul 17, 2024 at 6:24 PM SOzcn <selahattinozcnma@gmail.com> wrote:

Hello Folks,

We are upgrading from version 10 to 15. We encountered the following error for the first time. Can you give any ideas on how to solve this error? Since it is a system table, we cannot intervene on it.

pg_restore: from TOC entry 3312; 0 0 ACL TABLE "pg_pltemplate" postgres
pg_restore: error: could not execute query: ERROR: relation "pg_catalog.pg_pltemplate" does not exist
Command was: GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "username";

We upgraded many Postgresql in the same way. But this error just blew up somehow.
For this cluster, we upgraded 10 to 11 and then 11 to 12 without a problem, but the same error comes after 12 to 13 and 12 to 15.

The other issue is that the check is successful when we check the cluster for the upgrade.

The Upgrade Command;

/usr/lib/postgresql/15/bin/pg_upgrade
--old-bindir /usr/lib/postgresql/12/bin/
--new-bindir /usr/lib/postgresql/15/bin/
--old-datadir /data/rac/12/main/
--new-datadir /data/rac/15/main2
--old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'
--new-options '-c config_file=/etc/postgresql/15/main2/postgresql.conf' --check

Re: Error; pg_upgrade 10 to 15

From

Ron Johnson

Date:

18 July, 01:08:46

On Wed, Jul 17, 2024 at 9:53 AM Holger Jakobs <holger@jakobs.com> wrote:

Just general remarks:

1. There is no need to upgrade to intermediate major versions. Go from 10.(lastest minor) directly to 16.3

Maybe the version of some extension (like PostGIS) installed on PG10 is incompatible with PG 15, so the upgrade must be done in stages (upgrading the extension at every step).

2. Why would you want to upgrade to 15 when 16 is already out.

An application vendor might not have certified PG 16. Or maybe non-technical upper management is scared of the latest version.

Regards,
Holger

Am 17. Juli 2024 15:24:04 MESZ schrieb SOzcn <selahattinozcnma@gmail.com>:
Hello Folks,

We are upgrading from version 10 to 15. We encountered the following error for the first time. Can you give any ideas on how to solve this error? Since it is a system table, we cannot intervene on it.

pg_restore: from TOC entry 3312; 0 0 ACL TABLE "pg_pltemplate" postgres
pg_restore: error: could not execute query: ERROR: relation "pg_catalog.pg_pltemplate" does not exist
Command was: GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "username";

We upgraded many Postgresql in the same way. But this error just blew up somehow.
For this cluster, we upgraded 10 to 11 and then 11 to 12 without a problem, but the same error comes after 12 to 13 and 12 to 15.

The other issue is that the check is successful when we check the cluster for the upgrade.

The Upgrade Command;

/usr/lib/postgresql/15/bin/pg_upgrade
--old-bindir /usr/lib/postgresql/12/bin/
--new-bindir /usr/lib/postgresql/15/bin/
--old-datadir /data/rac/12/main/
--new-datadir /data/rac/15/main2
--old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'
--new-options '-c config_file=/etc/postgresql/15/main2/postgresql.conf' --check

Re: Error; pg_upgrade 10 to 15

From

Imran Khan

Date:

18 July, 15:17:31

Hi Ron,

Why don't you go with logical backup and restore than pg_upgrade? How much is the size ? Will you get downtime for performing the upgrade? Can you get a parallel server with latest OS and PG15 or 16 installed ?

Thanks,

Imran

On Thu, Jul 18, 2024, 1:09 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

On Wed, Jul 17, 2024 at 9:53 AM Holger Jakobs <holger@jakobs.com> wrote:
Just general remarks:

1. There is no need to upgrade to intermediate major versions. Go from 10.(lastest minor) directly to 16.3

Maybe the version of some extension (like PostGIS) installed on PG10 is incompatible with PG 15, so the upgrade must be done in stages (upgrading the extension at every step).

2. Why would you want to upgrade to 15 when 16 is already out.

An application vendor might not have certified PG 16. Or maybe non-technical upper management is scared of the latest version.

Regards,
Holger

Am 17. Juli 2024 15:24:04 MESZ schrieb SOzcn <selahattinozcnma@gmail.com>:
Hello Folks,

We are upgrading from version 10 to 15. We encountered the following error for the first time. Can you give any ideas on how to solve this error? Since it is a system table, we cannot intervene on it.

pg_restore: from TOC entry 3312; 0 0 ACL TABLE "pg_pltemplate" postgres
pg_restore: error: could not execute query: ERROR: relation "pg_catalog.pg_pltemplate" does not exist
Command was: GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "username";

We upgraded many Postgresql in the same way. But this error just blew up somehow.
For this cluster, we upgraded 10 to 11 and then 11 to 12 without a problem, but the same error comes after 12 to 13 and 12 to 15.

The other issue is that the check is successful when we check the cluster for the upgrade.

The Upgrade Command;

/usr/lib/postgresql/15/bin/pg_upgrade
--old-bindir /usr/lib/postgresql/12/bin/
--new-bindir /usr/lib/postgresql/15/bin/
--old-datadir /data/rac/12/main/
--new-datadir /data/rac/15/main2
--old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'
--new-options '-c config_file=/etc/postgresql/15/main2/postgresql.conf' --check