Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed - Mailing list pgsql-hackers

于 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?
>