Обсуждение: LC_COLLATE and pg_upgrade
Hi all, I just tried to pg_upgrade a PostgreSQL-8.4 to 9.0. pg_upgrade stopped with this message: "old and new cluster lc_collate values do not match" So i checked the LC_COLLATE on 8.4 and 9.0: 8.4: postgres=# show lc_collate; lc_collate ------------ en_US.utf8 (1 row) 9.0: postgres=# show lc_collate; lc_collate ------------ en_US.UTF-8 (1 row) Well... I think you see my problem, i don't even know if it is really a problem... Is it possible just to change the lc_collate on the 8.4 machine? Obviously its the same encoding, or am i wrong? My second Question, but not that urgent is about two databases which have LC_COLLATE=C, is there a safe way to get it to UTF-8? regards Bernhard
Am Donnerstag, den 10.02.2011, 17:56 +0100 schrieb Bernhard Schrader: > Hi all, > > I just tried to pg_upgrade a PostgreSQL-8.4 to 9.0. > > pg_upgrade stopped with this message: > > "old and new cluster lc_collate values do not match" > > So i checked the LC_COLLATE on 8.4 and 9.0: > > 8.4: > postgres=# show lc_collate; > lc_collate > ------------ > en_US.utf8 > (1 row) > > 9.0: > postgres=# show lc_collate; > lc_collate > ------------ > en_US.UTF-8 > (1 row) > > > Well... I think you see my problem, i don't even know if it is really a > problem... > > Is it possible just to change the lc_collate on the 8.4 machine? > Obviously its the same encoding, or am i wrong? > > My second Question, but not that urgent is about two databases which > have LC_COLLATE=C, is there a safe way to get it to UTF-8? > > regards > Bernhard > > > Doesn't anyone know if I could override this settings? LC_COLLATE should be interesting for sort order, and while both is utf8, it should work or am I totally wrong? As far as I read right now, LC_COLLATE is a read_only variable which is used while initdb. But why does the pg_upgrade script doesn't see that utf8 and UTF-8 are the same? Is it just a string compare? greetz Bernhard
On mån, 2011-02-14 at 14:18 +0100, Bernhard Schrader wrote: > As far as I read right now, LC_COLLATE is a read_only variable which > is used while initdb. But why does the pg_upgrade script doesn't see > that utf8 and UTF-8 are the same? Is it just a string compare? Why don't you just reinitialize your new database cluster with the same locale spelling as the old one? Your points are valid, but unfortunately difficult to handle in the general case.
If i reinitialize the database cluster, won't i have to pg_dump restore my data? In this case the downtime would be to big. Therefore I'm searching for a faster way. utf8 and UTF-8, should be the same in my opinion, so i think there must be a way to just change this value, so that pg_upgrade can do his work, or is this impossible? regards Am Mittwoch, den 16.02.2011, 13:54 +0200 schrieb Peter Eisentraut: > On mån, 2011-02-14 at 14:18 +0100, Bernhard Schrader wrote: > > As far as I read right now, LC_COLLATE is a read_only variable which > > is used while initdb. But why does the pg_upgrade script doesn't see > > that utf8 and UTF-8 are the same? Is it just a string compare? > > Why don't you just reinitialize your new database cluster with the same > locale spelling as the old one? > > Your points are valid, but unfortunately difficult to handle in the > general case. > > >
On ons, 2011-02-16 at 13:14 +0100, Bernhard Schrader wrote: > If i reinitialize the database cluster, won't i have to pg_dump restore > my data? In this case the downtime would be to big. Therefore I'm > searching for a faster way. I said reinitialize your new database cluster. Since you're running pg_upgrade, I assume your new cluster has no data in it worth keeping. > utf8 and UTF-8, should be the same in my opinion, so i think there must > be a way to just change this value, so that pg_upgrade can do his work, > or is this impossible? No, you can't change it with any reasonable effort. (You can manually edit pg_control and recalculate the checksum, if you must. Don't do that, though.)
Bernhard Schrader wrote: > Doesn't anyone know if I could override this settings? > LC_COLLATE should be interesting for sort order, and while both is utf8, > it should work or am I totally wrong? > > As far as I read right now, LC_COLLATE is a read_only variable which is > used while initdb. But why does the pg_upgrade script doesn't see that > utf8 and UTF-8 are the same? Is it just a string compare? Yes, just a string compare. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +