On 5/20/14, 2:22 PM, Bruce Momjian wrote:
On Tue, May 20, 2014 at 12:59:31PM -0600, Jeff Ross wrote:
Removing support functions from new cluster ok
Copying user relation files
/var/lib/postgresql/8.4/main/base/4275487/4278965
Mismatch of relation OID in database "FNBooking": old OID 4279499,
new OID 19792
Failure, exiting
OK, those numbers are supposed to match. The array is ordered by OID
and pg_upgrade expects a 1-to-1 mapping.
Ah, so I misunderstood the error message--thanks for clearing that up.
On 8.4.21, here's that OID:
postgres=# \c "FNBooking"
psql (9.3.4, server 8.4.21)
You are now connected to database "FNBooking" as user "postgres".
FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
oid = 4279499;
relname | relfilenode | relkind
---------------+-------------+---------
abandone_conv | 4279499 | r
(1 row)
and on 9.3.4 it is the same:
postgres@vdev1commandprompt2:~$ psql "FNBooking"
psql (9.3.4)
Type "help" for help.
FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
oid = 4279499;
relname | relfilenode | relkind
---------------+-------------+---------
abandone_conv | 4279499 | r
(1 row)
Yes, they are supposed to match.
On 8.4.21, the new OID doesn't exist:
FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
oid = 19792;
relname | relfilenode | relkind
---------+-------------+---------
(0 rows)
and on 9.3.4 it is this:
FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
oid = 19792;
relname | relfilenode | relkind
------------------+-------------+---------
pg_toast_4279527 | 19792 | t
(1 row)
Just to check, I did a pg_dump of the 8.4.21 FNBooking database and
it restored with psql to 9.3.4 with no issues but the overall
migration will really be too big to go this route.
So the problem is that some table in the new cluster got a low-numbered
toast file and the version of the table in the old cluster probably
doesn't have a toast file.
Can you track down details on what table owns that toast file? Can you
check on the table's layout to see what might have caused the toast
table creation? Were columns added/removed? If you remove that table,
does pg_upgrade then work? I am guessing it would.
Here's a sample from a different database that failed with the same problem.
Error: Mismatch of relation OID in database "UDB": old OID 1163225, new
OID 22588
postgres@vdev1commandprompt2:~$ psql "UDB"
psql (9.3.4)
Type "help" for help.
UDB=# \x
Expanded display is on.
UDB=# select * from pg_class where reltoastrelid = 22588;
-[ RECORD 1
]--+--------------------------------------------------------------------------------------------------
relname | contact_email
relnamespace | 2200
reltype | 17262
reloftype | 0
relowner | 10
relam | 0
relfilenode | 17260
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 22588
reltoastidxid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 5
relchecks | 0
relhasoids | f
relhaspkey | t
relhasrules | f
relhastriggers | t
relhassubclass | f
relispopulated | t
relfrozenxid | 1944
relminmxid | 2
relacl |
{postgres=arwdDxt/postgres,fnv2=arwd/postgres,webv2=arwd/postgres,krish=r/postgres,fm=r/postgres}
reloptions |
UDB=# \d+ contact_email
Table
"public.contact_email"
Column | Type |
Modifiers | Storage | Stats target | Description
--------+-----------------------------+------------------------------------------------------------+----------+--------------+-------------
id | integer | not null default
nextval('contact_email_id_seq'::regclass) | plain | |
email1 | character varying(255) | not null
| extended | |
email2 | character varying(255) |
| extended | |
time | timestamp without time zone | not null default now()
| plain | |
source | email_source | not null
| plain | |
Indexes:
"contact_email_pkey" PRIMARY KEY, btree (id)
"idx_contact_email_email1" btree (lower(email1::text)
varchar_pattern_ops)
"idx_contact_email_email2" btree (lower(email2::text)
varchar_pattern_ops)
Referenced by:
TABLE "abandoned_master_booking" CONSTRAINT
"abandoned_master_booking_contact_email_id_fkey" FOREIGN KEY
(contact_email_id) REFERENCES contact_email(id)
TABLE "contact" CONSTRAINT "contact_contact_email_id_fkey" FOREIGN
KEY (contact_email_id) REFERENCES contact_email(id)
TABLE "eticketaccesslog" CONSTRAINT
"eticketaccesslog_contact_email_id_fkey" FOREIGN KEY (contact_email_id)
REFERENCES contact_email(id)
Has OIDs: no
UDB=# \dT+ email_source
List of data types
-[ RECORD 1 ]-----+-------------
Schema | public
Name | email_source
Internal name | email_source
Size | 4
Elements | Booking
| User Profile
| UserProfile
Access privileges | =U/postgres
Description |
I do not know if columns were added or removed.
Dropping the table from the last database that caused pg_upgrade to fail
let pg_upgrade proceed on through many more before it failed again on
the UDB database, so that's progress!
If there is anything else I can provide, let me know.
Thanks, Bruce!
Jeff
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
