Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed

于 2012/9/19 7:22, Bruce Momjian 写道:
> On Mon, Sep 17, 2012 at 05:07:23PM -0400, Bruce Momjian wrote:
>>> # select * from pg_tables where tablename='sql_features';
>>>      schemaname     |  tablename   | tableowner | tablespace |
>>> hasindexes | hasrules | hastriggers
>>> --------------------+--------------+------------+------------+------------+----------+-------------
>>> information_schema | sql_features | postgres   |            | f
>>> | f        | f
>>> (1 row)
>> OK, good to know.  This is the query pg_upgrade 9.2 uses to pull
>> information from 9.1 and 9.2:
>>
>> 	SELECT c.oid, n.nspname, c.relname,  c.relfilenode, c.reltablespace, t.spclocation
>> 	FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
>> 		LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid
>> 	WHERE relkind IN ('r','t', 'i', 'S') AND
>> 		((n.nspname !~ '^pg_temp_' AND
>> 		  n.nspname !~ '^pg_toast_temp_' AND
>> 		  n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade') AND
>> 		  c.oid >= 16384
>> 		 )
>> 		 OR
>> 		 (n.nspname = 'pg_catalog' AND
>> 		  relname IN
>> 		  ('pg_largeobject', 'pg_largeobject_loid_pn_index', 'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index')
>> 		 )
>> 		)
>> 	ORDER BY 1;
>>
>> Based on the fact that sql_features exists in the information_schema
>> schema, I don't think 'sql_features' table is actually being processed
>> by pg_upgrade, but I think its TOAST table, because it has a high oid,
>> is being processed because it is in the pg_toast schema.  This is
>> causing the mismatch between the old and new clusters.
>>
>> I am thinking this query needs to be split apart into a UNION where the
>> second part handles TOAST tables and looks at the schema of the _owner_
>> of the TOAST table.  Needs to be backpatched too.
> OK, I am at a conference now so will not be able to write-up a patch
> until perhaps next week.  You can drop the information schema in the old
> database and pg_upgrade should run fine.  I will test your failure once
> I create a patch.
>
OK. I will try. I also found some problems on initdb when re-init my 
pg9.2 db.
1. initdb doesn't create the pg_log dir so pg can not be started after 
initdb before I create the dir manually.
2. The case issue of db charset name. I installed pg9.1 and pg9.2 with 
zh_CN.UTF8. But somehow it seems the actual chaset name is stored with 
lowercase 'zh_CN.utf8' during the install. In this case, I can run the 
pg_upgrade without problem since they are both lowercase. But when I 
re-init pg9.2 with option '-E zh_CN.UTF8', pg_upgrade will fail and 
report that encoding/charset mis-match: one is uppercase and another is 
lowercase. If I run initdb with '-E zh_CN.utf8', it will tell me there 
is no such charset in the system. I found a workaround to run initdb 
with '--lc-collate=zh_CN.utf8 --lc-ctype=zh_CN.utf8 
--lc-messages=zh_CN.utf8 --lc-monetary=zh_CN.utf8 
--lc-numeric=zh_CN.utf8 --lc-time=zh_CN.utf8'. But the case problem is 
really confusing.