Over the past many years I have been involved in projects requiring localization into many different languages. One issue with this is how data is stored in the database.

As we support and help various customers with Postgresql related database issues, we still find users who are encoding their databases in SQL_ASCII. Perhaps there is a reason for this, if there is I haven’t figured it out yet.

In many cases, this eventually causes them problems and they end up having to switch from SQL_ASCII or some other encoding over to UTF8.

There may be more, but I know of two different ways of converting the encoding of the database to UTF8.

One way is

iconv -f iso-8859-1 -t utf-8 dump_file dump_file_recoded

The preferred way however is to use the -E option of the pg_dump command like this:

pg_dump -U postgres -W -E UTF8 -d pg_bench >pgbench.backup.

the -W command forces you to put in a password.

For more details on how to use the pg_dump command, here are the docs:

http://www.postgresql.org/docs/8.3/interactive/app-pg_dump.html

This isn’t intended to be a complete tutorial, but for those of us who have previously only run Slony on Linux, this might be a helpful post.
Read the rest of this entry »