Re: fix for pg_upgrade

panam wrote:
> Hi Bruce,
>
> here is the whole dump (old DB):
> http://postgresql.1045698.n5.nabble.com/file/n4844725/dump.txt dump.txt

Wow, that is interesting. I see this in the dump output:

-- For binary upgrade, must preserve relfilenodes
SELECT binary_upgrade.set_next_heap_relfilenode('465783'::pg_catalog.oid);
SELECT binary_upgrade.set_next_toast_relfilenode('465786'::pg_catalog.oid);
SELECT binary_upgrade.set_next_index_relfilenode('465788'::pg_catalog.oid);

CREATE TABLE accounts (
guid character varying(32) NOT NULL,
name character varying(2048) NOT NULL,
account_type character varying(2048) NOT NULL,
commodity_guid character varying(32),
commodity_scu integer NOT NULL,
non_std_scu integer NOT NULL,
parent_guid character varying(32),
code character varying(2048),
description character varying(2048),
hidden integer,
placeholder integer
);

and it is clearly saying the oid/relfilenode should be 465783, but your
9.1 query shows:

C:\Program Files\PostgreSQL\9.1\bin>psql -c "select * from pg_class where oid = 465783 or oid = 16505;" -p 5433 -U postgres
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions
----------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------
accounts | 2200 | 16507 | 0 | 16417 | 0 | 16505 | 0 | 0 | 0 | 16508 | 0 | t | f | p | r | 11 | 0 | f | t | f | f | f | 3934366 | |
(1 row)

and 9.0 says correctly 465783:

C:\Program Files\PostgreSQL\9.0\bin>psql -c "select * from pg_class where oid = 465783 or oid = 16505;" -p 5432 -U postgres
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relistemp | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasexclusion | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions
----------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+-----------+---------+----------+-----------+------------+------------+-----------------+-------------+----------------+----------------+--------------+--------+------------
accounts | 465781 | 465785 | 0 | 456619 | 0 | 465783 | 0 | 3 | 122 | 465786 | 0 | t | f | f | r | 11 | 0 | f | t | f | f | f | f | 3934366 | |
(1 row)

It is as though the system ignoring the set_next_heap_relfilenode()
call, but I don't see how that could happen. I don't see any other
'accounts' table in that dump.

My only guess at this point is that somehow the -b/IsBinaryUpgrade flag
is not being processed or regognized, and hence the binary_upgrade 'set'
routines are not working.

Is this 9.1 final or later? Can you turn on debug mode and send me the
pg_upgrade log file that is generated? I am going go look for the
pg_ctl -o '-b' flag. Are all databases/objects failing or just this
one?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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