UTF8 Encoding and Postgres Dump

%expand% UTF8 Encoding and Postgres Dump %set(body,%html-clean({

If you back up a database from one version of postgres using pg_dumpall, then restore to another version, it's very possible that the strictness or some other aspect of the UTF8 library has changed. The end result is that you will see an error "invalid character for encoding" while restoring. The utility "iconv" can help, and it was really hard to find the answer online, which is why I'm reposting for my own sanity:


gunzip -c dumpall.gz | iconv | sudo -u postgres psql > restore.errs 2>&1

Miraculously, iconv worked without options, cleaning up version incompatibilities, while maintaining all the data... even the funny French city names. If you're changing encodings while also upgrading, that's fine too. iconv takes a "from" and "to" argument, so log in to the source db and destination db, and use:


SHOW client_encoding;

Then use the results as the "-f FROM" and "-t TO" arguments to iconv. No conversion is perfect, but iconv seems to do as good a job as possible.

}))


Bracing against the wind
www.documentroot.com

View Archive

Free Blog Commenter & Counter

Memebot: Free Website Hosting

Americans for Approval Voting

2002 WTC Pics: Aug 12 Aug 16 Aug 30 Sep 11 Sep 23 Oct 24

Shop with Bitcoin At Amazon

Yvette's Weblog

All sites by me

RSS for this site

Lo! In the east against the blazing shadow of a rising sun, there come four men striding in unison, each step a journey, against the terrible winds.

Tuesday, December 03, 2013

UTF8 Encoding and Postgres Dump
Posted 5:52 AM

If you back up a database from one version of postgres using pg_dumpall, then restore to another version, it's very possible that the strictness or some other aspect of the UTF8 library has changed. The end result is that you will see an error "invalid character for encoding" while restoring. The utility "iconv" can help, and it was really hard to find the answer online, which is why I'm reposting for my own sanity:


gunzip -c dumpall.gz | iconv | sudo -u postgres psql > restore.errs 2>&1

Miraculously, iconv worked without options, cleaning up version incompatibilities, while maintaining all the data... even the funny French city names. If you're changing encodings while also upgrading, that's fine too. iconv takes a "from" and "to" argument, so log in to the source db and destination db, and use:


SHOW client_encoding;

Then use the results as the "-f FROM" and "-t TO" arguments to iconv. No conversion is perfect, but iconv seems to do as good a job as possible.

[View/Post Comments] [Digg] [Del.icio.us] [Stumble]

| Bloghop: | Blogarama | Technorati | Blogwise