Обсуждение: 9.0 to 9.2 pg_upgrade pain due to collation mismatch

Hi all,

Using centos 5.x

I'm trying to upgrade (without having to dump/restore a 1.5TB db) from 9.0
to 9.2 using pg_upgrade, but am having a few issues.

1.  I ran into the (usual?) issue with ld libraries conflicting, so
renamed /etc/ld.so.conf.d/postgresql-9.0-libs.conf to blah, and reran
ldconfig, then I could initdb the new data/ folder.

2.  My 9.0 (old) db is using encoding/Collation/Ctype:
SQL_ASCII/POSIX/POSIX, and for the life of me I cannot initdb the new
(9.2) data/ folder to the same to ensure pg_upgrade works (currently keep
getting "old and new cluster lc_collate values do not match" error).

I've tried:  /usr/pgsql-9.2/bin/initdb -D 9.2/data/ --encoding='SQL_ASCII'
--lc-collate=POSIX --lc-ctype=POSIX

but no matter what, I always end up with the same error "old and new
cluster lc_collate values do not match", since the Collation and Ctype
always end up with C, instead of POSIX.

I'd appreciate any ideas on how to initdb the new data/ folder so I can
use pg_upgrade.

Thanks

Hi,

On Wed, 2012-09-12 at 20:12 +0200, Henry C. wrote:
>
> 1.  I ran into the (usual?) issue with ld libraries conflicting, so
> renamed /etc/ld.so.conf.d/postgresql-9.0-libs.conf to blah, and reran
> ldconfig, then I could initdb the new data/ folder

For the records, I fixed this issue in the latest 9.0.9 packages.

Regards,
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

"Henry C." <henka@cityweb.co.za> writes:
> 2.  My 9.0 (old) db is using encoding/Collation/Ctype:
> SQL_ASCII/POSIX/POSIX, and for the life of me I cannot initdb the new
> (9.2) data/ folder to the same to ensure pg_upgrade works (currently keep
> getting "old and new cluster lc_collate values do not match" error).

"C" is the official name of that locale.  Not sure how you got it to say
"POSIX" ... maybe we didn't have normalization of the locale name back
then?

Anyway, simplest fix seems to be to update the 9.0 installation's
pg_database to say "C" in those entries.

            regards, tom lane

> "C" is the official name of that locale.  Not sure how you got it to say
> "POSIX" ... maybe we didn't have normalization of the locale name back
> then?
>
> Anyway, simplest fix seems to be to update the 9.0 installation's
> pg_database to say "C" in those entries.

Never ceases to amaze me where you guys find the time to respond so
quickly on this list...

Anyway, thanks for the feedback.  Ok, so it's simply a case of:

update pg_database set datcollate='C', datctype='C';

Correct?  Mine are all POSIX, so no where clause needed.

Thanks

"Henry C." <henka@cityweb.co.za> writes:
> Anyway, thanks for the feedback.  Ok, so it's simply a case of:
> update pg_database set datcollate='C', datctype='C';
> Correct?  Mine are all POSIX, so no where clause needed.

Yeah, that should do it.

            regards, tom lane

Thanks to all who responded - upgrade was successful!

One final note, when using pg_upgrade ... --link, it finally recommends
use of delete_old_cluster.sh to remove the old data files.  I'm tempted,
but --link re-uses old data files,... bit of a contradiction there, if you
follow my meaning?

Is use of delete_old_cluster.sh ok if --link used with pg_upgrade?

Thanks

On Wed, Sep 12, 2012 at 09:54:57PM +0200, Henry C. wrote:
>
> Thanks to all who responded - upgrade was successful!
>
> One final note, when using pg_upgrade ... --link, it finally recommends
> use of delete_old_cluster.sh to remove the old data files.  I'm tempted,
> but --link re-uses old data files,... bit of a contradiction there, if you
> follow my meaning?
>
> Is use of delete_old_cluster.sh ok if --link used with pg_upgrade?

Link creates hard links between the old and new cluster.  Removing the
old cluster just changes the file link count from 2 to 1.  Is there a
way to make that clearer?

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

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

On 9/12/12 2:31 PM, Tom Lane wrote:
> "C" is the official name of that locale.  Not sure how you got it to say
> "POSIX" ... maybe we didn't have normalization of the locale name back
> then?

Says who?  I think C and POSIX are distinct locales that just happen to
behave the same way.

Peter Eisentraut <peter_e@gmx.net> writes:
> On 9/12/12 2:31 PM, Tom Lane wrote:
>> "C" is the official name of that locale.  Not sure how you got it to say
>> "POSIX" ... maybe we didn't have normalization of the locale name back
>> then?

> Says who?

Says setlocale(), at least on the Fedora machine I just checked.
The reason I see this:

$ initdb --locale=POSIX
The files belonging to this database system will be owned by user "tgl".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

is that setlocale is returning "C" as the canonical name of the locale.

            regards, tom lane