Re: Unable to dump database using pg_dump

Adam Dear <adear@usnx.net> writes:
> pg_dump: query to obtain list of schemas failed: ERROR:  more than one
> row returned by a subquery used as an expression

> The pg_dump command is from version 7.4.11.  This is the version of
> postgres I am running on my old server.

Just FYI, when doing a version upgrade it's considered better practice
to dump with the newer version's pg_dump, if possible.  Sometimes the
newer pg_dump knows things that will smooth the migration.  However,
that's not related to your immediate problem.

A look at the pg_dump source code shows that the subquery it's
complaining about is trying to fetch the name of each view's owner.
Apparently you've got multiple rows in pg_shadow with the same usesysid
--- or perhaps just one row but the index on usesysid is corrupt and is
returning it more than once.  Try
    select ctid, usename, usesysid from pg_shadow;
and see if you see any duplicate usesysid's.  If so, try removing the
extra copies (use WHERE ctid = '...' if there isn't any other difference
between the rows).  If you see no dups in a query like this, then the
index is at fault --- try reindexing.

            regards, tom lane