pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dick Visser <dick.visser@tienhuis.nl>
To: tony <tony@tgds.net>
Cc: Hannes Dorbath <light@theendofthetunnel.de>
Cc: pgsql-admin@postgresql.org
Subject: Re: upgrading a three year old server
Date: Mon, 19 Dec 2005 12:51:45 +0100
Message-ID: <43A69ED1.2000206@tienhuis.nl> (raw)
In-Reply-To: <1134988158.2847.220.camel@hush.localdomain>
References: <1134928154.2847.203.camel@hush.localdomain>
	<43A66D67.3090701@theendofthetunnel.de>
	<1134988158.2847.220.camel@hush.localdomain>

tony wrote:

> Can I change the encoding to UTF-8 easily?

I had to convert as well while upgrading to 8.1.0.

We came from 8.0.3; for some reason all our db's were 'LATIN1' in PG, 
and while things worked OK, we wanted to get rid of this too. All the 
data in our db's came from web-apps and forms. All the pages that were 
used to enter that data were UTF-8, so essentially the data *was* 
correct UTF8.
First I went for the dirty hack and tried to fool PG by simply changing 
the LATIN1 string in the dump to UTF8; it seems to work but later on 
pg_restore chokes on the blobs.
After extensive research I found a solution that worked 100% for me.

On the old server, dump db including blobs etc:
pg_dump -Fc yourdb > yourdb.dump

On our new server (debian), the installation seemed to have run initdb 
with -E LATIN1, so I needed to remove my data tree first and the 
properly recreate it:

initd -E UTF-8 -D /var/lib/postgresql/8.1/main/data

After that I created the db but with the old LATIN1 encoding:

createdb -E LATIN1 yourdb

Then restore the old dump into it:

pg_restore yourdb yourdb.dump

 From this moment on, the data on my webpages looks OK (I looked at some 
really foreign stuff my users had filled in, like Arabic and Hebrew (no 
pun intended)), but the db is still LATIN1.

Because 8.1 allows you to dump db's with blobs and all to TXT files, you 
dump the whole thing to TXT on your new server:

pg_dump -Fp yourdb > yourdb.txt.dump

Then edit the dumpfile manually and change the LATIN1 string to UTF8.
If you are sure your db does not actually contain the string LATIN1, you 
could do:

pg_dump -Fp yourdb | sed 's/LATIN1/UTF8/g' > yourdb.txt.dump

Now drop the db and recreate it with the correct encoding:

dropdb yourdb && createdb -E UTF8 yourdb

Now import the dump; this needs to be done with psql because it is txt:

psql yourdb < yourdb.txt.dump

At this time you will have a proper UTF8 db.

FYI, this solution worked for me, and I am pretty sure the reason why it 
worked it that our websites were 'UTF8-proof' from the beginning.


If everything is setup correct, you might see really cool strings appear 
on user-filled-in forms:

http://www.terena.nl/compendium/2005/basicinfo.php?nrenid=26

:)

Best regards,



-- 
   *    ***     Dick Visser         TIENHUIS Networking
  **   *   *    Touwbaan 68             P: +31206843731
   *   * ***    1018 HS Amsterdam       F: +31208641420
   *   * * *    The Netherlands         M: +31622698108
   *   **  *    IP-phone (SIP)/email:  dick@tienhuis.nl
   *   *   *    PGP-key: http://www.tienhuis.nl/gpg.txt
   *   *   *    Webcam: http://www.tienhuis.nl/cam2.asx
  ***   ***



view thread (6+ messages)  latest in thread

Message-ID: <43A69ED1.2000206@tienhuis.nl>
Permalink:  ../43A69ED1.2000206@tienhuis.nl/
Also on:    postgresql.org/message-id/43A69ED1.2000206@tienhuis.nl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: dick.visser@tienhuis.nl, tony@tgds.net, light@theendofthetunnel.de
  Subject: Re: upgrading a three year old server
  In-Reply-To: <43A69ED1.2000206@tienhuis.nl>
  References: <1134928154.2847.203.camel@hush.localdomain> <43A66D67.3090701@theendofthetunnel.de> <1134988158.2847.220.camel@hush.localdomain> <43A69ED1.2000206@tienhuis.nl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox