Date: Mon, 24 Sep 2012 10:26:59 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Rural Hunter <ruralhunter@gmail.com>
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
Message-ID: <20120924142659.GC21242@momjian.us>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <50606726.3050802@gmail.com>
User-Agent: Mutt/1.5.20 (2009-06-14)

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

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +