Re: Unable to dump database using pg_dump

Adam Dear <adear(at)usnx(dot)net> writes:
> 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

How annoying :-(. And I suppose madisoncounty isn't a superuser,
so you're now stuck with no working superuser. What you'll need
to do is shut down the database and start up a standalone backend
(read the "postgres" reference page about how to work in this mode).
What I'd then do is delete the other postgres row (if you can still
see it) and do CREATE USER postgres WITH SYSID 1 CREATEUSER CREATEDB.
That should get you back to having a working postgres account.

It's hard to be sure how you got into this state, although one possible
theory is you got burnt by not vacuuming pg_shadow on a regular basis.
The newer version should keep you out of that error, if so.

regards, tom lane