Thread: RE: [ADMIN] Can NOT dump db with 6.4.2's pg_dumpall

I'm not sure if this will help but I just dealt with a similar problem.
When I tried to do a pg_dump it would fail.  It turned out the culpret
was one single bad value in one row of one table.  Any query that
returned that value would fail also, so I had to go through an
interitive process of select statements to identify the offending row.
Once I found it, I was able to delete the row, and everything worked
again.

Hope that helps, it was a bear of a problem for me.

-----Original Message-----
From: Michael Ma
To: pgsql-admin@postgresql.org
Sent: 2/19/00 1:41 PM
Subject: [ADMIN] Can NOT dump db with 6.4.2's pg_dumpall

Hi,

In order to upgrade from 6.4.2 to 6.5.2, I tried to dump my db by typing

[postgres@redhat60 pgsql]$ pg_dumpall -z > db.out

Instead of getting a dump file, I got the following message.

========================================================================
pg_dump: The -z option(dump ACLs) is now the default, continuing.
SET TRANSACTION command failed.  Explanation from backend: 'ERROR:
parser: parse error at or near "transaction"
'.
pg_dump failed on cqn, exiting
========================================================================

My working platform is: RedHat Linux 6.0 with kernel 2.2.5-22 on a P-200
machine with 49M RAM.

Can anybody help?  Thanks in advance.

Michael

************

Hi, Matthew

I have tried to dump tables one by one to identify the table contains
bad value.  However I got the following error messages from each dump.

..........
SET TRANSACTION command failed.  Explanation from backend: 'ERROR:
parser: parse error at or near "transaction"'.
..........

May I know how you manage to locate the bad value? Or, are there other
ways (may be more complicated) to dump a table/db?

Thanks

Michael
===================
Matthew wrote:
>
> I'm not sure if this will help but I just dealt with a similar problem.
> When I tried to do a pg_dump it would fail.  It turned out the culpret
> was one single bad value in one row of one table.  Any query that
> returned that value would fail also, so I had to go through an
> interitive process of select statements to identify the offending row.
> Once I found it, I was able to delete the row, and everything worked
> again.
>
> Hope that helps, it was a bear of a problem for me.