Change the encoding of a Postgres database

Ever wondered how you could change the encoding of one of your Postgres database?

I have had that problem several times, and in most cases people tell you to:

1. Dump your database, may have to use –encoding on the command line
2. Drop the existing database (or rename it)
3. Create a new database with the proper encoding
4. Restore the database

The problem is that there is no support for altering the encoding of a database. The ALTER DATABASE does not include that option. And since it should be really rare, it makes sense.

Now, there is an easier way than using the pg_dumpcommand. There are the steps:

1. Rename the database (ALTER DATABASE name RENAME TO nametmpl)
2. Re-create a database with the old name (CREATE DATABASE name WITH ENCODING = ‘UTF8′ … TEMPLATE nametmpl)

To be more secure, you may want to dump the database first (just in case). But see how this is done: you rename the database as a template database, then you create a new database using the old database as a template and the correct encoding (UTF-8 here.)