Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_upgrade 9.0->9.2 failure: Mismatch of relation OID in database

pg_upgrade 9.0->9.2 failure: Mismatch of relation OID in database

From
Christoph Berg
Date:
On upgrading a 9.0 database to 9.2 using pg_upgrade, I got this:

# pg_upgradecluster -m upgrade 9.0 main /psql/data-9.2
[...]
Performing Upgrade
------------------
[...]
Restoring database schema to new cluster                    ok
Removing support functions from new cluster                 ok
Copying user relation files

Mismatch of relation OID in database "hisrm": old OID 18804, new OID 18803
Failure, exiting
Error: pg_upgrade run failed


This is a cluster that was running with 9.0.12 (compiled locally). For
the upgrade, I installed postgresql-9.0 and -9.2 from
apt.postgresql.org (9.0.13, 9.2.4), so pg_upgrade was using these
versions. OS is Ubuntu 12.04 amd64 now and was 8.04 while the cluster
was still running on 9.0.12.

In the 9.0 cluster, 18804 is the relation oid of glm_lrahm_to_se.


pg_upgrade_dump_all.sql:
--
-- Name: glm_lrahm_to_se; Type: TABLE; Schema: mbs; Owner: fsv; Tablespace:
--


-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('18806'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_type array oid
SELECT binary_upgrade.set_next_array_pg_type_oid('18805'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_class oids
SELECT binary_upgrade.set_next_heap_pg_class_oid('18804'::pg_catalog.oid);

CREATE TABLE glm_lrahm_to_se (
    id integer NOT NULL,
    lrahm integer NOT NULL,
    se integer NOT NULL
);

-- For binary upgrade, set heap's relfrozenxid
UPDATE pg_catalog.pg_class
SET relfrozenxid =3D '14118'
WHERE oid =3D 'glm_lrahm_to_se'::pg_catalog.regclass;


ALTER TABLE mbs.glm_lrahm_to_se OWNER TO fsv;

pg_upgrade_restore.sql:
SELECT binary_upgrade.set_next_pg_type_oid('18806'::pg_catalog.oid);
 set_next_pg_type_oid
----------------------

(1 Zeile)

SELECT binary_upgrade.set_next_array_pg_type_oid('18805'::pg_catalog.oid);
 set_next_array_pg_type_oid
----------------------------

(1 Zeile)

SELECT binary_upgrade.set_next_heap_pg_class_oid('18804'::pg_catalog.oid);
 set_next_heap_pg_class_oid
----------------------------

(1 Zeile)

CREATE TABLE glm_lrahm_to_se (
    id integer NOT NULL,
    lrahm integer NOT NULL,
    se integer NOT NULL
);
CREATE TABLE
UPDATE pg_catalog.pg_class
SET relfrozenxid =3D '14118'
WHERE oid =3D 'glm_lrahm_to_se'::pg_catalog.regclass;
UPDATE 1
ALTER TABLE mbs.glm_lrahm_to_se OWNER TO fsv;
ALTER TABLE


(I can provide more info on request.)

Christoph
--=20
cb@df7cb.de | http://www.df7cb.de/

Re: pg_upgrade 9.0->9.2 failure: Mismatch of relation OID in database

From
Bruce Momjian
Date:
On Thu, Sep 26, 2013 at 02:59:30PM +0200, Christoph Berg wrote:
> On upgrading a 9.0 database to 9.2 using pg_upgrade, I got this:
>
> # pg_upgradecluster -m upgrade 9.0 main /psql/data-9.2
> [...]
> Performing Upgrade
> ------------------
> [...]
> Restoring database schema to new cluster                    ok
> Removing support functions from new cluster                 ok
> Copying user relation files
>
> Mismatch of relation OID in database "hisrm": old OID 18804, new OID 18803
> Failure, exiting
> Error: pg_upgrade run failed
>
>
> This is a cluster that was running with 9.0.12 (compiled locally). For
> the upgrade, I installed postgresql-9.0 and -9.2 from
> apt.postgresql.org (9.0.13, 9.2.4), so pg_upgrade was using these
> versions. OS is Ubuntu 12.04 amd64 now and was 8.04 while the cluster
> was still running on 9.0.12.
>
> In the 9.0 cluster, 18804 is the relation oid of glm_lrahm_to_se.
>
>
> pg_upgrade_dump_all.sql:
> --
> -- Name: glm_lrahm_to_se; Type: TABLE; Schema: mbs; Owner: fsv; Tablespace:
> --
>
>
> -- For binary upgrade, must preserve pg_type oid
> SELECT binary_upgrade.set_next_pg_type_oid('18806'::pg_catalog.oid);
>
>
> -- For binary upgrade, must preserve pg_type array oid
> SELECT binary_upgrade.set_next_array_pg_type_oid('18805'::pg_catalog.oid);
>
>
> -- For binary upgrade, must preserve pg_class oids
> SELECT binary_upgrade.set_next_heap_pg_class_oid('18804'::pg_catalog.oid);
>
> CREATE TABLE glm_lrahm_to_se (
>     id integer NOT NULL,
>     lrahm integer NOT NULL,
>     se integer NOT NULL
> );

That is very interesting, and it certainly should not be failing.

I am surprised it got an oid that was one less than the desired one,
18803.  Is there any mention of 18803 in the SQL file?  If you create a
cluster with just your schema and no data, can you upgrade that cleanly?

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

  + It's impossible for everything to be true. +

Re: pg_upgrade 9.0->9.2 failure: Mismatch of relation OID in database

From
Christoph Berg
Date:
Re: Bruce Momjian 2013-10-02 <20131002170628.GC5960@momjian.us>
> That is very interesting, and it certainly should not be failing.
>
> I am surprised it got an oid that was one less than the desired one,
> 18803.  Is there any mention of 18803 in the SQL file?

18803 wasn't mentioned anywhere, just 18804 like the log says.

> If you create a
> cluster with just your schema and no data, can you upgrade that cleanly?

I haven't tried that yet. This is on a customer machine we don't have
access to except when arranging a remote desktop session. I'll see if
I can do some tests there.

Mit freundlichen Grüßen,
Christoph Berg
--
Tel.: +49 (0)21 61 / 46 43-187
credativ GmbH, HRB Mönchengladbach 12080, USt-ID-Nummer: DE204566209
Hohenzollernstr. 133, 41061 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer