Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed

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