How to convert a db in postgreSQL to utf8?

Converting to UTF8 should not damage your data as (I believe) all characters in SQL_ASCII also exist in utf8; they just have different byte codes.

Your best bet is to re-build your database. That is dump it, create a utf8 database then restore the dump to that new database.

postgres pg_dump --encoding utf8 main -f main.sqlcreatedb -E utf8 newMainpsql -f main.sql -d newMain

You can then of course rename the databases once you are happy that the new UTF8 one matches your data.

UTF-8 conversion is all about what kind of characters where saved in the non UTF-8 db: depending on the data the proposed solution may fail.I managed to convert mine following this tutorial, using recode (a small tool from the GNU project that let you change on-the-fly the encoding of a given file) and I came up with this:

pg_dump -v --encoding utf8 -Fc -Z9 -c -f origindb.sql.bin iso8859-1-dbpg_restore origindb.sql.bin | recode iso-8859-1..u8 | psql --dbname utf8converteddb

I resolved using these commands;

1-) Export

pg_dump --username=postgres --encoding=ISO88591 database -f database.sql

and after

2-) Import

psql -U postgres -d database < database.sql

these commands helped me solve the problem of conversion SQL_ASCII - UTF-8