Restoring SQL_ASCII dumps to a UTF8-encoded database

Question

I've got a Postgres 8.4 environment where the encoding on all our databases is set to SQL_ASCII - We're finally migrating to Postgres 9.2, and I would like to migrate everything over to UTF8 encoding.

Unfortunately the text data in this DB is not clean -- Trying to restore the pg_dump to a utf8-encoded database throws errors about invalid byte sequences, even if I specify --encoding=UTF8 when I run pg_dump (presumably because Postgres doesn't know what to make of them and just dumps them unchanged?).

We have a LOT of data (upwards of a million rows with text/string elements), and auditing all of it by hand would be very time consuming (and error prone) so I'd like to automate this if possible.

Is there an easy way to find the non-utf8-conforming strings/text fields in the database so we can fix them? Or am I stuck with a manual audit to straighten this mess out?

Answer 1

I suspect your database content may be in iso8859 or cp1252. If it were ascii, you would not run into problems importing it. You may be able to determine the coding by opening your dump with python. The following python3 tries encodings until it succeeds. It can be used to determine the file encoding.

for enc in ('cp1252', 'utf8'):
    print('Encoding', enc)
    try:
        file = open(fileName, 'r', encoding=enc)
        return file.read()
    except Exception as e:
        print(e)
        return None

Python also can also be used to access the database and audit the data. It could also be used to transcode the data to UTF-8 while copying it if necessary.

Answer 2

Mangled text encoding dumps are a pain to work with.

The usual - and admittedly crude - solution is to run iconv on the SQL-format dump, with the -c flag to tell it to omit characters that are invalid in the target encoding.

This is only viable if the origin database is supposedly in one primary encoding, and just has a bit of bad data here and there due to the failure to actually enforce and check that encoding.

If the origin database is in mixed/multiple encodings, this approach won't really work. You'd have to do per-field charset detection, using something like PL/Python or PL/Perl stored procedure to do best-guess encoding detection on each field, convert it to the target encoding (say utf-8) and then UPDATE the field with the re-encoded text. Needless to say this is going to be slow and clunky, but if every field could have a different encoding there's not much else to be done.

Such an approach is inexact and error prone. It'll still leave you with mangled text where encodings are mis-guessed. However, that text probably got displayed mangled in the app before, too, because it'd be stored in one encoding then re-interpreted in another for display.

Answer 3

You can use uchardet to determine the encoding:

https://code.google.com/p/uchardet/

Then use iconv, possibly with -c, to convert between encodings:

http://www.documentroot.com/2013/12/utf8-encoding-and-postgres-dump.html