Message-ID: <5060720E.1080903@gmail.com>
Date: Mon, 24 Sep 2012 22:45:34 +0800
From: Rural Hunter <ruralhunter@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1;
	rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
To: Bruce Momjian <bruce@momjian.us>
CC: Peter Eisentraut <peter_e@gmx.net>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
References: <20120917043236.GA20123@momjian.us> <5056AF29.3040903@gmail.com>
	<20120917210723.GA30394@momjian.us>
	<20120918232239.GA2014@momjian.us> <505C307E.20501@gmail.com>
	<1348403628.18490.1.camel@vanquo.pezone.net>
	<505F1A98.9090901@gmail.com>
	<1348440393.18490.2.camel@vanquo.pezone.net>
	<20120924125500.GA21242@momjian.us> <50606726.3050802@gmail.com>
	<20120924142659.GC21242@momjian.us>
In-Reply-To: <20120924142659.GC21242@momjian.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

于 2012/9/24 22:26, Bruce Momjian 写道:
> On Mon, Sep 24, 2012 at 09:59:02PM +0800, Rural Hunter wrote:
>> 于 2012/9/24 20:55, Bruce Momjian 写道:
>>> On Sun, Sep 23, 2012 at 06:46:33PM -0400, Peter Eisentraut wrote:
>>>> On Sun, 2012-09-23 at 22:20 +0800, Rural Hunter wrote:
>>>>> Ah yes, seems I used a wrong parameter. The --locale='zh_CN.utf8'
>>>>> works. --locale='zh_CN.UTF8' also works. But still the question is,
>>>>> should the encoding name be case sensitive?
>>>> PostgreSQL treats encoding names as case insensitive.
>>>>
>>>> But it depends on the operating system whether locale names are case
>>>> sensitive.
>>> I can confirm that pg_upgrade does case-insensitive comparisons of
>>> encoding/locale names:
>>>
>>> 	static void
>>> 	check_locale_and_encoding(ControlData *oldctrl,
>>> 	                          ControlData *newctrl)
>>> 	{
>>> 	    /* These are often defined with inconsistent case, so use pg_strcasecmp(). */
>>> 	    if (pg_strcasecmp(oldctrl->lc_collate, newctrl->lc_collate) != 0)
>>> 	        pg_log(PG_FATAL,
>>> 	               "old and new cluster lc_collate values do not match\n");
>>> 	    if (pg_strcasecmp(oldctrl->lc_ctype, newctrl->lc_ctype) != 0)
>>> 	        pg_log(PG_FATAL,
>>> 	               "old and new cluster lc_ctype values do not match\n");
>>> 	    if (pg_strcasecmp(oldctrl->encoding, newctrl->encoding) != 0)
>>> 	        pg_log(PG_FATAL,
>>> 	               "old and new cluster encoding values do not match\n");
>>> 	}
>>>
>> strange. not sure what happened. I reviewed the log and here is what I did:
>> 1. initdb without encoding/locale parameter:
>> $ initdb
>> The files belonging to this database system will be owned by user
>> "postgres".
>> This user must also own the server process.
>>
>> The database cluster will be initialized with locale "zh_CN.UTF-8".
>> The default database encoding has accordingly been set to "UTF8".
>> initdb: could not find suitable text search configuration for locale
>> "zh_CN.UTF-8"
>> The default text search configuration will be set to "simple".
>>
>> 2. Run pg_upgrade:
>> $ /opt/PostgreSQL/9.2/bin/pg_upgrade -b /opt/PostgreSQL/9.1/bin -B
>> /opt/PostgreSQL/9.2/bin -d /raid/pgsql -D /raid/pg92data -c
>> Performing Consistency Checks
>> -----------------------------
>> Checking current, bin, and data directories ok
>> Checking cluster versions ok
>> Checking database user is a superuser ok
>> Checking for prepared transactions ok
>> Checking for reg* system OID user data types ok
>> Checking for contrib/isn with bigint-passing mismatch ok
>>
>> old and new cluster lc_collate values do not match
>> Failure, exiting
>>
>> 3. initdb with --lc-collate:
>> $ initdb --lc-collate=zh_CN.utf8
>> The files belonging to this database system will be owned by user
>> "postgres".
>> This user must also own the server process.
>>
>> The database cluster will be initialized with locales
>> COLLATE: zh_CN.utf8
>> CTYPE: zh_CN.UTF-8
>> MESSAGES: zh_CN.UTF-8
>> MONETARY: zh_CN.UTF-8
>> NUMERIC: zh_CN.UTF-8
>> TIME: zh_CN.UTF-8
>> The default database encoding has accordingly been set to "UTF8".
>> initdb: could not find suitable text search configuration for locale
>> "zh_CN.UTF-8"
>> The default text search configuration will be set to "simple".
>>
>> 4. try pg_upgrade again:
>> $ /opt/PostgreSQL/9.2/bin/pg_upgrade -b /opt/PostgreSQL/9.1/bin -B
>> /opt/PostgreSQL/9.2/bin -d /raid/pgsql -D /raid/pg92data -c
>> Performing Consistency Checks
>> -----------------------------
>> Checking current, bin, and data directories ok
>> Checking cluster versions ok
>> Checking database user is a superuser ok
>> Checking for prepared transactions ok
>> Checking for reg* system OID user data types ok
>> Checking for contrib/isn with bigint-passing mismatch ok
>>
>> old and new cluster lc_ctype values do not match
>> Failure, exiting
>>
>> 5. Run initdb with all those locale settings:
>> $ initdb --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
>> The files belonging to this database system will be owned by user
>> "postgres".
>> This user must also own the server process.
>>
>> The database cluster will be initialized with locale "zh_CN.utf8".
>> The default database encoding has accordingly been set to "UTF8".
>> initdb: could not find suitable text search configuration for locale
>> "zh_CN.utf8"
>> The default text search configuration will be set to "simple".
>>
>> 6. Run pg_upgrade. this time it worked.
> OK, that is good information.  pg_upgrade gets the locale and encoding
> from the template0 database settings:
>
> 	"SELECT datcollate, datctype "
> 	"FROM	pg_catalog.pg_database "
> 	"WHERE	datname = 'template0' ");
>
> If your operating system locale/encoding names changed after the initdb
> of the old cluster, this would not be reflected in template0.
No. It's not changed. look at my system settings:
LANG=zh_CN.UTF-8
$ cat /var/lib/locales/supported.d/local
zh_CN.UTF-8 UTF-8

I think the problem is on the options when I installed pgsql(both 9.1 
and 9.2)
Select the locale to be used by the new database cluster.

Locale

[1] [Default locale]
[2] C
[3] POSIX
[4] zh_CN.utf8
[5] zh_HK.utf8
[6] zh_SG.utf8
[7] zh_TW.utf8
Please choose an option [1] : 4
I chose 4 instead of 1. I guess the default locale(option 1) is with dash.
> I think
> Peter is right that this might be as dash issue, utf8 vs utf-8.  Look at
> the initdb output:
>
>> 3. initdb with --lc-collate:
>> $ initdb --lc-collate=zh_CN.utf8
>> The files belonging to this database system will be owned by user
>> "postgres".
>> This user must also own the server process.
>>
>> The database cluster will be initialized with locales
>> COLLATE: zh_CN.utf8
>> CTYPE: zh_CN.UTF-8
>> MESSAGES: zh_CN.UTF-8
>> MONETARY: zh_CN.UTF-8
>> NUMERIC: zh_CN.UTF-8
>> TIME: zh_CN.UTF-8
> Notice colldate does not have dash, while ctype does.
>
> Peter, ideas on a solution?
>