PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

Arne Henrik Segtnan
Hi, 

When trying to upgrade from PostgreSQL 10 to 12, the installation fails as shown below. 

# pg_upgradecluster --link --method=upgrade 10 main /dbdata/zabbix


From PosgreSQL log: 
2021-05-04 13:06:04.780 CEST [12861] LOG:  database system is ready to accept connections
 done
server started
.
.
2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR:  column r.relhasoids does not exist at character 1616
2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db STATEMENT:
-- For binary upgrade, must preserve pg_type oid

SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('162222'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('162221'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('162220'::pg_catalog.oid);


CREATE VIEW "repack"."tables" AS
SELECT ("r"."oid")::"regclass" AS "relname",
    "r"."oid" AS "relid",
    "r"."reltoastrelid",
        CASE
.
.
.
command: "/usr/lib/postgresql/12/bin/pg_ctl" -w -D "/etc/postgresql/12/main" -o "" -m fast stop >> "pg_upgrade_server.log" 2>&1
2021-05-04 13:06:05.641 CEST [12861] LOG:  received fast shutdown request
2021-05-04 13:06:05.641 CEST [12861] LOG:  aborting any active transactions
waiting for server to shut down....2021-05-04 13:06:05.644 CEST [12861] LOG:  background worker "logical replication launcher" (PID 12867) exited with exit code 1
2021-05-04 13:06:05.645 CEST [12863] LOG:  shutting down
2021-05-04 13:06:05.657 CEST [12861] LOG:  database system is shut down
 done
server stopped

- Other upgrade methods (clone/dump) fails with the same errors. 

OS info: 
NAME="Ubuntu"
VERSION="20.04.2 LTS (Focal Fossa)"

Linux <server> 5.4.0-72-generic #80-Ubuntu SMP  x86_64 x86_64 x86_64 GNU/Linux


Anyone seen similar issue? 
Please advise. 


Best regards, 

Arne Segtnan


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

Bruce Momjian
On Tue, May  4, 2021 at 06:29:17PM +0200, Arne Henrik Segtnan wrote:

> Hi,
>
> When trying to upgrade from PostgreSQL 10 to 12, the installation fails as
> shown below.
>
> # pg_upgradecluster --link --method=upgrade 10 main /dbdata/zabbix
>
>
> From PosgreSQL log:
> 2021-05-04 13:06:04.780 CEST [12861] LOG:  database system is ready to accept
> connections
>  done
> server started
> .
> .
> 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR:  column
> r.relhasoids does not exist at character 1616

pg_class.relhasoids was removed in PG 12.

> - Other upgrade methods (clone/dump) fails with the same errors.

OK, so the problem is really pg_dump.

> Anyone seen similar issue?

Nope, something very odd going on here.

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.



Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

Magnus Hagander-2
On Tue, May 4, 2021 at 6:38 PM Bruce Momjian <[hidden email]> wrote:

>
> On Tue, May  4, 2021 at 06:29:17PM +0200, Arne Henrik Segtnan wrote:
> > Hi,
> >
> > When trying to upgrade from PostgreSQL 10 to 12, the installation fails as
> > shown below.
> >
> > # pg_upgradecluster --link --method=upgrade 10 main /dbdata/zabbix
> >
> >
> > From PosgreSQL log:
> > 2021-05-04 13:06:04.780 CEST [12861] LOG:  database system is ready to accept
> > connections
> >  done
> > server started
> > .
> > .
> > 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR:  column
> > r.relhasoids does not exist at character 1616
>
> pg_class.relhasoids was removed in PG 12.
>
> > - Other upgrade methods (clone/dump) fails with the same errors.
>
> OK, so the problem is really pg_dump.
>
> > Anyone seen similar issue?
>
> Nope, something very odd going on here.

Do you by any chance have more than one version of PostgreSQL running
at the same time for different clusters? It kind of sounds like it's
picking up the wrong version at some point.

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

Tom Lane-2
In reply to this post by Arne Henrik Segtnan
Arne Henrik Segtnan <[hidden email]> writes:
> When trying to upgrade from PostgreSQL 10 to 12, the installation fails as shown below.

> 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR:  column r.relhasoids does not exist at character 1616
> 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db STATEMENT:
> -- For binary upgrade, must preserve pg_type oid

> SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('162222'::pg_catalog.oid);
> -- For binary upgrade, must preserve pg_type array oid
> SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('162221'::pg_catalog.oid);


> -- For binary upgrade, must preserve pg_class oids
> SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('162220'::pg_catalog.oid);


> CREATE VIEW "repack"."tables" AS
> SELECT ("r"."oid")::"regclass" AS "relname",
>    "r"."oid" AS "relid",
>    "r"."reltoastrelid",
>        CASE
> .
> .
> .

You didn't show us the rest of the command, but it looks like
this view contains a reference to pg_class.relhasoids.  As
Bruce noted, that column no longer exists, so you're not going
to be able to transparently upgrade this view.

I don't know what this view is from, though the schema name
"repack" is suggestive.  Possibly you could drop whatever it's
a part of, and then reinstall an updated version after upgrading?

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

Bruce Momjian
On Tue, May  4, 2021 at 12:43:36PM -0400, Tom Lane wrote:

> Arne Henrik Segtnan <[hidden email]> writes:
> > When trying to upgrade from PostgreSQL 10 to 12, the installation fails as shown below.
>
> > 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR:  column r.relhasoids does not exist at character 1616
> > 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db STATEMENT:
> > -- For binary upgrade, must preserve pg_type oid
>
> > SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('162222'::pg_catalog.oid);
> > -- For binary upgrade, must preserve pg_type array oid
> > SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('162221'::pg_catalog.oid);
>
>
> > -- For binary upgrade, must preserve pg_class oids
> > SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('162220'::pg_catalog.oid);
>
>
> > CREATE VIEW "repack"."tables" AS
> > SELECT ("r"."oid")::"regclass" AS "relname",
> >    "r"."oid" AS "relid",
> >    "r"."reltoastrelid",
> >        CASE
> > .
> > .
> > .
>
> You didn't show us the rest of the command, but it looks like
> this view contains a reference to pg_class.relhasoids.  As
> Bruce noted, that column no longer exists, so you're not going
> to be able to transparently upgrade this view.
>
> I don't know what this view is from, though the schema name
> "repack" is suggestive.  Possibly you could drop whatever it's
> a part of, and then reinstall an updated version after upgrading?

Yeah, Tom, I think you are on to something.  I couldn't figure out where
the r.relhasoids was referenced in the rest of the log output, but if it
is on the lines that are part of CREATE VIEW, it certainly could be the
case that the view references a pre-PG-12 column of pg_class.  To fix
it, Tom is right that removing the view then recreating it with PG-12
pg_class column assumptions is the right fix.  I actually rarely see
this kind of failure.

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.



Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

Arne Henrik Segtnan
In reply to this post by Tom Lane-2
Hi, 

Thanks a lot for the feedback, which actually solved the problem. After executing the below command, upgrade from 10 to 12 worked perfectly fine. 

pgsqldb=# DROP EXTENSION pg_repack CASCADE;


Med vennlig hilsen / Best regards,
 _______________________
Arne Henrik Segtnan


4. mai 2021 kl. 18:52 skrev Bruce Momjian <[hidden email]>:

On Tue, May  4, 2021 at 12:43:36PM -0400, Tom Lane wrote:
Arne Henrik Segtnan <[hidden email]> writes:
When trying to upgrade from PostgreSQL 10 to 12, the installation fails as shown below. 

2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR:  column r.relhasoids does not exist at character 1616
2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db STATEMENT:
-- For binary upgrade, must preserve pg_type oid

SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('162222'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('162221'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('162220'::pg_catalog.oid);


CREATE VIEW "repack"."tables" AS
 SELECT ("r"."oid")::"regclass" AS "relname",
    "r"."oid" AS "relid",
    "r"."reltoastrelid",
        CASE
.
.
.

You didn't show us the rest of the command, but it looks like
this view contains a reference to pg_class.relhasoids.  As
Bruce noted, that column no longer exists, so you're not going
to be able to transparently upgrade this view.

I don't know what this view is from, though the schema name
"repack" is suggestive.  Possibly you could drop whatever it's
a part of, and then reinstall an updated version after upgrading?

Yeah, Tom, I think you are on to something.  I couldn't figure out where
the r.relhasoids was referenced in the rest of the log output, but if it
is on the lines that are part of CREATE VIEW, it certainly could be the
case that the view references a pre-PG-12 column of pg_class.  To fix
it, Tom is right that removing the view then recreating it with PG-12
pg_class column assumptions is the right fix.  I actually rarely see
this kind of failure.

-- 
 Bruce Momjian  <[hidden email]>        https://momjian.us
 EDB                                      https://enterprisedb.com

 If only the physical world exists, free will is an illusion.
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

Bruce Momjian
On Wed, May  5, 2021 at 01:26:43PM +0200, Arne Henrik Segtnan wrote:
> Hi,
>
> Thanks a lot for the feedback, which actually solved the problem. After
> executing the below command, upgrade from 10 to 12 worked perfectly fine.
>
> pgsqldb=# DROP EXTENSION pg_repack CASCADE;

Great to hear, thanks.

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.