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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, 25 Aug 2008 10:21:54 -0400
"John T. Dow" <john@johntdow.com> wrote:

> 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).

You can use the TOC feature of -Fc to remove restoring of that single
table. You can then convert that single table to a plain text dump and
clean the data. Then restore it separately.

If you have foregin keys and indexes on the bad data table, don't
restore the keys until *after* you have done the above.

Sincerely,

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Joshua

The TOC feature sounds good, as does converting a single table to plain text.

But I can't find documentation for the TOC feature under pg_dump or pg_restore. I'm looking in postgresql-8.2.1-US.pdf.

Neither could I see anything about converting a single table to a plain text dump.

Also, I stumbled across the statement that you can't restore large objects for a single table. Is that true?

Another thing I couldn't find was how to dump roles using -Fc.

John



On Mon, 25 Aug 2008 10:04:13 -0700, Joshua Drake wrote:

>On Mon, 25 Aug 2008 10:21:54 -0400
>"John T. Dow" <john@johntdow.com> wrote:
>
>> 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).
>
>You can use the TOC feature of -Fc to remove restoring of that single
>table. You can then convert that single table to a plain text dump and
>clean the data. Then restore it separately.
>
>If you have foregin keys and indexes on the bad data table, don't
>restore the keys until *after* you have done the above.
>
>Sincerely,
>
>Joshua D. Drake
>
>--
>The PostgreSQL Company since 1997: http://www.commandprompt.com/
>PostgreSQL Community Conference: http://www.postgresqlconference.org/
>United States PostgreSQL Association: http://www.postgresql.us/
>Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general