Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

6 Answers 6

A typical solution is to use iconv -c.

share|improve this answer

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.

share|improve this answer

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

hexdump input | sed -e "..." | xxd -r -p output
share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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