Thread: 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
Attachment
"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