于 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?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
