By "bad data", I mean a character that's not UTF8, such as hex 98.
As far as I can tell, pg_dump is the tool to use. But it has
serious drawbacks.
If you dump in the custom format, the data is compressed (nice) and
includes large objects (very nice). But, from my tests and the postings of
others, if there is invalid data in a table, although PostgreSQL won't complain and
pg_dump won't complain, pg_restore will strenuously object, rejecting all rows for that
particular table (not nice at all).
If you dump in plain text format, you can at least inspect the dumped
data and fix it manually or with iconv. But the plain text
format doesn't support large objects (again, not nice). While byte arrays are supported, they result in very large dump
files.
Also, neither of these methods gets information such as the roles, so
that has to be captured some other way if the database has to be rebuilt
from scratch.
Is my understanding incomplete or wrong? Is there no good solution?
Why isn't there a dumpall that writes in compressed format and allows recovery from bad data?
John
