Thread: Locale/encoding problem/question
Hello all, I somehow managed to stuff up the encoding (or locale or something) in a transfer of a database from one machine to another (also different linux distribution). The problem is this: the origional database was created and populated with data using whatever default locale/encoding was installed on the first machine. Words would appear correctly, as in: Hoë After dumping the database and restoring on a different one, the word incorrectly appears as: Hoë Before I do any more damage or waste time, I'd appreciate any ideas or pointers on correctly performing the initdb -E<encoding> --locale=<locale>... (or any other ideas on resolving this issue) , before I try and restore the data again. Thanks henk
On Fri, Aug 04, 2006 at 10:48:17AM +0200, henka@cityweb.co.za wrote: > Hello all, > > I somehow managed to stuff up the encoding (or locale or something) in a > transfer of a database from one machine to another (also different linux > distribution). > > The problem is this: the origional database was created and populated > with data using whatever default locale/encoding was installed on the > first machine. Two big questions: 1. What encoding are the two database (\l will tell you)? 2. What encoding are the clients expecting? It is entirely possible that the databases have got the right encoding, but the client you're accessing it doesn't understand. For example, if you've got both your databases on UTF-8, then the transfer went fine, but perhaps the client on the new machine doesn't display UTF-8. This happens because psql and other clients default to the same encoding as the server and don't check to see if the terminal actually supports that. So what you're decribing could also be a result of the server sending you UTF-8, the client displaying that, but the xterm or whatever you're using thinking it's Latin1. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
> On Fri, Aug 04, 2006 at 10:48:17AM +0200, henka@cityweb.co.za wrote: >> Hello all, >> >> I somehow managed to stuff up the encoding (or locale or something) in a >> transfer of a database from one machine to another (also different linux >> distribution). >> >> The problem is this: the origional database was created and populated >> with data using whatever default locale/encoding was installed on the >> first machine. > > Two big questions: > > 1. What encoding are the two database (\l will tell you)? > 2. What encoding are the clients expecting? Thanks for the response, Martijn. I *think* the client_encoding origionally in the db was UTF-8 (but I could be wrong, it might have been LATIN1). I would imagine that LATIN1 would be the right one, since it needs to display standard english, plus some others (such as é ä ë è etc). The multibyte chars show up in xterm (putty) -and- when the data is displayed using php in a browser - both incorrectly. I've even tried using LATIN1 (ie, explicitly setting it to latin1 using initdb, and then restoring the database after changing the 'utf-8' strings in the dump data to 'latin1'). This still yields the funny chars. To be honest, I have no idea what the origional encoding was. Can you suggest any other approaches I can try to restore the database so that those chars display correctly? All comments are welcome. Regards Henk
On Fri, Aug 04, 2006 at 11:58:22AM +0200, henka@cityweb.co.za wrote: > > On Fri, Aug 04, 2006 at 10:48:17AM +0200, henka@cityweb.co.za wrote: > > Two big questions: > > > > 1. What encoding are the two database (\l will tell you)? > > 2. What encoding are the clients expecting? > I've even tried using LATIN1 (ie, explicitly setting it to latin1 using > initdb, and then restoring the database after changing the 'utf-8' strings > in the dump data to 'latin1'). This still yields the funny chars. Wait, so the dump is in utf-8? You shouldn't need to edit the dump, postgresql will convert the encodings on the fly while loading. > To be honest, I have no idea what the origional encoding was. It should be in the dump file, almost the first line. Locale is of no interest to pg_dump, you'll have to decide how you want it. > Can you suggest any other approaches I can try to restore the database so > that those chars display correctly? Well, at the very least, does it go away if you type: set client_encoding=latin1; Please provide more details about your setup too, your client is on windows? The server is ...? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
> On Fri, Aug 04, 2006 at 11:58:22AM +0200, henka@cityweb.co.za wrote: >> > On Fri, Aug 04, 2006 at 10:48:17AM +0200, henka@cityweb.co.za wrote: >> > Two big questions: >> > >> > 1. What encoding are the two database (\l will tell you)? >> > 2. What encoding are the clients expecting? > >> I've even tried using LATIN1 (ie, explicitly setting it to latin1 using >> initdb, and then restoring the database after changing the 'utf-8' >> strings >> in the dump data to 'latin1'). This still yields the funny chars. > > Wait, so the dump is in utf-8? You shouldn't need to edit the dump, > postgresql will convert the encodings on the fly while loading. I've actually found two versions - one with UTF-8, and the other LATIN1. >> To be honest, I have no idea what the origional encoding was. > > It should be in the dump file, almost the first line. Locale is of no > interest to pg_dump, you'll have to decide how you want it. Yes: UTF-8 and the other is LATIN1 > Well, at the very least, does it go away if you type: > > set client_encoding=latin1; No it doesn't. That was one of the first things I tried after reading the docs. > Please provide more details about your setup too, your client is on > windows? The server is ...? Server: Linux debian sarge PG: 8.1.4 show all: lc_collate C lc_ctype C lc_messages C lc_monetary C lc_numeric C lc_time C client_encoding LATIN1 (or UTF-8) Clients: Windows using PuTTY (ie, for psql), and dynamic web content with PHP/Pg (on any browser).
henka@cityweb.co.za writes:
>> It should be in the dump file, almost the first line. Locale is of no
>> interest to pg_dump, you'll have to decide how you want it.
> Yes: UTF-8 and the other is LATIN1
Note that this represents what the original server *thought* the
encoding was. But it's not at all impossible that the server thought
the data was LATIN1 when it was really UTF8. (The other way around is
less plausible because the server would have been able to detect
encoding errors.) If you were using clients that treated the data
as UTF8 without paying attention to what the server thought, you'd
not have realized you were mislabeling the data.
But, if you tried to load data marked as LATIN1 into a server using
UTF8, it'd have applied a LATIN1 to UTF8 conversion, and then
everything's hosed.
I'd suggest actually inspecting the data in the dump file: it's not that
hard to tell UTF8 from LATIN1 if you look at the byte sequences.
Or you could just take the file marked LATIN1, edit it to change the
client_encoding setting to say the data is UTF8, and see if you can
load it. If it's not UTF8, 8.1.4 will almost certainly detect a ton of
encoding errors.
regards, tom lane
> henka@cityweb.co.za writes: >>> It should be in the dump file, almost the first line. Locale is of no >>> interest to pg_dump, you'll have to decide how you want it. > >> Yes: UTF-8 and the other is LATIN1 > > Note that this represents what the original server *thought* the > encoding was. But it's not at all impossible that the server thought > the data was LATIN1 when it was really UTF8. (The other way around is > less plausible because the server would have been able to detect > encoding errors.) If you were using clients that treated the data > as UTF8 without paying attention to what the server thought, you'd > not have realized you were mislabeling the data. > > But, if you tried to load data marked as LATIN1 into a server using > UTF8, it'd have applied a LATIN1 to UTF8 conversion, and then > everything's hosed. > > I'd suggest actually inspecting the data in the dump file: it's not that > hard to tell UTF8 from LATIN1 if you look at the byte sequences. > > Or you could just take the file marked LATIN1, edit it to change the > client_encoding setting to say the data is UTF8, and see if you can > load it. If it's not UTF8, 8.1.4 will almost certainly detect a ton of > encoding errors. Thanks Tom, your suggestion worked. Just to document this for others, this is what I did: - created a new empty DB: initdb -ELATIN1 -D data. - edited dump file with UTF8 encoding and changed to LATIN1 (doing the reverse resulted in encoding errors during restore). - restored database So, it looks like it was the reverse: the db thought it was UTF8, when in fact it was LATIN1. Regards