Postgresql – Using sed to convert hex characters in postgresql dump file

postgresqlsed

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?

Best Answer

A typical solution is to use iconv -c.