Configuring Locales on Linux for PostgreSQL
You need to create the database with a locale that matches the encoding, e.g.,
createdb -T template0 maggieprod -E LATIN1 --locale=en_CASince you don't have all locales installed, I guess you are using Debian or Ubuntu. In that case, call dpkg-reconfigure locales or install the locales-all package.
Alternatively, create the database with encoding UTF8. As long as all your clients set the client encoding correctly, it shouldn't make a difference.
I had trouble using the createdb syntax from The_Denominater, so I did it the following way:
CREATE DATABASE maggieprod WITH ENCODING = 'LATIN1' LC_CTYPE = 'en_CA' LC_COLLATE = 'en_CA' TEMPLATE template0;
If you are still interested using the recode command will transform your database dump to the character set of your choice before you import it into your new database. See this link - http://blog.e-shell.org/134