Re: Testing 9.2 in ~production environment

On mån, 2012-06-18 at 17:57 -0400, James Cloos wrote:
> >>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes:
> 
> JB> Can you check the collations of the two databases?  I'm wondering if 9.1
> JB> is in "C" collation and 9.2 is something else.
> 
> Thanks!
> 
> pg_dump -C tells me these two differences:
> 
>  -SET client_encoding = 'SQL_ASCII';
>  +SET client_encoding = 'UTF8';
> 
>  -CREATE DATABASE dbm WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII' LC_COLLATE = 'C' LC_CTYPE = 'C';
>  +CREATE DATABASE dbm WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'en_US.UTF-8';
> 
> for every db in the clusters.
> 
> I presume that lc_ctype is the significant difference?

It certainly makes some difference, but it's a bit shocking that makes
things that much slower.

> LC_CTYPE *is* specified as 'C' in the dump from which I created the 9.2
> cluster, so it must have been overridden by pg_restore.  I see that my
> dist's /etc rc script now sets LC_CTYPE.  Would that explain why lc_ctype
> changed between the two clusters?

It's possible, depending on how exactly the start up script maze is set
up on your particular OS.

> Is there any way to alter a db's lc_ctype w/o dumping and restoring?  I
> want to preserve some of the changes made since I copied the 9.1 cluster.
> Alter database reports that lc_ctype cannot be changed.

Not really, but in practice you can probably just update pg_database
directly.  If you don't have any case-insensitive indexes, nothing
should change.  Worst case, reindex everything.