Convert a PostgreSQL Database from LATIN1 to UTF8

I had a problem with my family django-powered website. I have an aggregator for all our friends blogs, very similar to the Django aggregator, except that mine hasn’t been aggregating. After some serious investigation, I found that psycopg was barfing error messages because the feeds that were being stuffed into my postgresql database contained utf-8 characters that it couldn’t figure out how to convert into latin1 characters. I hadn’t paid any attention to this, but apparently my blog database was using latin1. Lame. I decided it was time to learn how to convert it to utf-8.

First, I did a pg_dumpall in order to get a look at things (this happens automatically every night, actually):

/usr/bin/pg_dumpall -U <special user> > /home/bryan/backups/all.dbs.out

Sure enough, my database was created like so:

CREATE DATABASE bryan WITH TEMPLATE = template0 OWNER = bryan ENCODING = 'LATIN1';

So, I dumped just that database:

pg_dump -U <special user> bryan > bryan.dbs.out

Then I converted it to utf-8 with iconv:

iconv --from-code latin1 --to-code utf-8 bryan.dbs.out > bryan.dbs.out.utf8

Then I dropped the lame old latin1 database, after shutting down apache2:

sudo /etc/init.d/apache2 stop
dropdb bryan

Then I created the shiny new utf-8 database, using the command from the pg_dumpall output, with a slight change:

psql -U <special user> bryan < "CREATE DATABASE bryan WITH TEMPLATE = template0 OWNER = bryan ENCODING = 'UTF8';"

Then I restored the converted backup:

psql -U <special user> bryan < bryan.dbs.out.utf8

After starting up apache2 again:

sudo /etc/init.d/apache2 start

The website looked just like it did before. Phew!

Next I ran my update_feeds.py script and my aggregator aggregated, which was really awesome to see. I have some serious reading to catch up on now.

References:

  1. http://archives.postgresql.org/pgsql-general/2006-03/msg01259.php
  2. http://www.postgresql.org/docs/8.2/interactive/backup-dump.html
  3. http://www.postgresql.org/docs/8.2/interactive/manage-ag-dropdb.html
  4. http://www.postgresql.org/docs/8.2/interactive/manage-ag-createdb.html

Comments

Anonymous said…

psql -U ... bryan < "CREATE DATABASE bryan WITH TEMPLATE = template0 OWNER = bryan ENCODING = 'LATIN1';"


should be


psql -U ... bryan < "CREATE DATABASE bryan WITH TEMPLATE = template0 OWNER = bryan ENCODING = 'LATIN1';"


Also, you don't need to convert the sql dump to UTF-8. Postgresql knows what encoding it currently working on, so if you restore a latin1 encoded database into a utf-8 database it will automatically do the right thing.

In other words, with all clients disconnected:

1. pg_dump yourdb > yourdb.sql
2. dropdb yourdb
3. createdb -E utf-8 yourdb
4. psql yourdb < yourdb.sql
November 4, 2008 at 3:07 AM
Anonymous said…

psql -U ... bryan < "CREATE DATABASE bryan WITH TEMPLATE = template0 OWNER = bryan ENCODING = 'UTF-8';"


forgot to fix it.. :/
November 4, 2008 at 3:08 AM
Bryan said…
Good catch! Fortunately I only made that mistake in the blog, not on the command line.
November 4, 2008 at 5:32 PM
Unknown said…
I had the same issue with my DB. Following your instructions I managed to convert the encoding quickly.

In addition I executed the following to convert my htmls to UTF-8:

ksh# for i in *
> do iconv -f CP1251 -t UTF-8 $i | tee $i
> done

and

ksh# for i in *
>do cat $i | sed s/windows-1251/utf-8/ | tee $i
>done


10x a lot
May 19, 2009 at 4:31 AM
Anonymous said…
Thank you for the fix
June 12, 2012 at 11:42 PM
Anonymous said…
Hi guys, I am having a similar problem with a postgresql database which was created with latin1 encoding.
I am about to convert the database to utf-8 but I don't want to loose the data already stored in the database.
Can I try the following and still have my website intact? Please advice
1. pg_dump yourdb > yourdb.psql
2. dropdb yourdb
3. createdb -E utf-8 yourdb
4. psql yourdb < yourdb.psql
September 6, 2012 at 1:21 AM
Bryan said…
You won't lose any data in the database. pg_dump dumps *everything*. You can examine the file produced by the pg_dump command with a text editor and see what is in there if you aren't sure you trust it. You can (and probably should) also do all this on a test database first, just to make sure.
September 6, 2012 at 9:12 AM
Post a Comment

Popular posts from this blog

SystemVerilog Fork Disable "Gotchas"

This is a long post with a lot of SystemVerilog code. The purpose of this entry is to hopefully save you from beating your head against the wall trying to figure out some of the subtleties of SystemVerilog processes (basically, threads). Subtleties like these are commonly referred to in the industry as "Gotchas" which makes them sound so playful and fun, but they really aren't either. I encourage you to run these examples with your simulator (if you have access to one) so that a) you can see the results first hand and better internalize what's going on, and b) you can tell me in the comments if this code works fine for you and I'll know I should go complain to my simulator vendor. OK, I'll start with a warm-up that everyone who writes any Verilog or SystemVerilog at all should be aware of, tasks are static by default. If you do this: module top; task do_stuff ( int wait_time); #wait_time $display ( "waited %0d, then did stuff" , wa
Read more

'git revert' Is Not Equivalent To 'svn revert'

I just learned that if you have some changes in your working tree that you want to get rid of, you don't type 'git revert' like you might guess. No, that's what cvs, subversion, mercurial, and bazaar (to name a few) use revert to mean, but not git. With git, revert is used to undo actual commits. Thankfully, you can undo your revert with another 'git revert', I just learned. So let me repeat to be clear, if you have changes to your working files that you want to abandon, DO NOT do this: git revert HEAD That will undo your last commit. Do this instead: git reset --hard HEAD I'm glad I have that straightened out now. I'm wondering if /etc was really a good place for me to start out playing with git. UPDATE: Nearly two years later and I'm still getting comments on this. I'm glad I've been able to help people out this way. The discussion in the comments is good, and one thing I'd like to point out is that I now always use
Read more

Put /etc Under Revision Control (with git)

UPDATE : There is a much easier way now : sudo aptitude install etckeeper There, now etc is under revision control and changes are automatically committed with a helpful message after you install new packages. Thanks for the tip, Marcelo! Read on for the original blog entry. --- I put my /etc directory under revision control. I used git. It's awesome. Not that I think I'm going to wholesale copy my /etc from one machine to another, nor because I'm going to branch my /etc directory and spend weeks developing some cool new feature (whatever that would look like...) and then merge it back into the main branch. No, it's mostly because I like the idea of having the big "undo" option that having things under revision control provides. Having to commit changes and write check-in comments when I make changes helps me stay more organized too. It's never good when I go in willy-nilly to make a "quick change" to some configuration file. I r
Read more