Re: Unable to dump database using pg_dump

I ran the select as instructed, and this is the output:

madisoncounty=# select ctid, usename, usesysid from pg_shadow;
  ctid  |    usename    | usesysid
-------+---------------+----------
  (0,1) | postgres      |        1
  (0,2) | postgres      |        1
  (0,5) | madisoncounty |      100
(3 rows)

I then did:

delete from pg_shadow where ctid='(0,2)';
DELETE 1

I had already reindexed the tables.  Now when I run the command, I get this:

pg_dump: [archiver (db)] connection to database "madisoncounty" failed:
FATAL:  user "postgres" does not exist

Also, I have been connecting to the db as the postgres user.  Now I am
unable to connect to the database with the postgres user, or with the
actual user for the site.

Tom Lane wrote:
> 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
>