I am working on moving several databases from a Postgresql 8.3 server to a Postgresql 8.4 server. It has worked fine so far, but one base has given me some trouble. The database is listed as unicode-encoded in the 8.3-server, but somehow a client program has managed to inject some invalid unicode data into it.

When I do a normal dump and restore using postgres' custom format, the new server won't accept it, complaining about unicode errors.

My plan is to do a plain text dump of the database, then use sed to replace the invalid characters with nothing (they are not needed). But how do you make sed work on hex/binary values in a file?


A typical solution is to use iconv -c.


Per Peter's answer, using iconv is the typical solution that most people use to clean up the bad data. If you want to do some analysis on the data ahead of time (and maybe clean it up within the original database), you can use the following https://github.com/xzilla/utf8checker to find the trouble spot.


According to the binary sed replacement on stackoverflow, your best bet seen to move through hexdump:

hexdump input | sed -e "..." | xxd -r -p output

This will strip all characters that are outside the range 0x32 (space) to 0x7e (tilde):

someprog | LANG=C sed 's/[\x00-\x31\x7f-\xff]//g'

You can use other character ranges to be more selective.


A colleague tipped me in the direction of Perl:

cat databasedump.sql | perl -pi -e 's/\xc3\xa9//g;' > fixeddatabasedump.sql

Ok, so it is not sed, but at least the syntax is more or less the same.


tr -d '[:xdigit:]' < old_database > new_database may also work.
tr -d deletes only '[:xdigit:]' hex characters

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.