Re: [GENERAL] 9.0 to 9.2 pg_upgrade pain due to collation mismatch
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.0 to 9.2 pg_upgrade pain due to collation mismatch
Peter Eisentraut pete...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.0 to 9.2 pg_upgrade pain due to collation mismatch
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 signature.asc Description: This is a digitally signed message part
Re: [GENERAL] 9.0 to 9.2 pg_upgrade pain due to collation mismatch
Henry C. he...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.0 to 9.2 pg_upgrade pain due to collation mismatch
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.0 to 9.2 pg_upgrade pain due to collation mismatch
Henry C. he...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.0 to 9.2 pg_upgrade pain due to collation mismatch
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.0 to 9.2 pg_upgrade pain due to collation mismatch
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
