Re: Unable to dump database using pg_dump

I really appreciate you help so far. here is what I am seeing . I did:

select * from pg_shadow;

1: usename (typeid = 19, len = 64, typmod = -1, byval = f)
2: usesysid (typeid = 23, len = 4, typmod = -1, byval = t)
3: usecreatedb (typeid = 16, len = 1, typmod = -1, byval = t)
4: usesuper (typeid = 16, len = 1, typmod = -1, byval = t)
5: usecatupd (typeid = 16, len = 1, typmod = -1, byval = t)
6: passwd (typeid = 25, len = -1, typmod = -1, byval = f)
7: valuntil (typeid = 702, len = 4, typmod = -1, byval = t)
8: useconfig (typeid = 1009, len = -1, typmod = -1, byval = f)
----
1: usename = "postgres" (typeid = 19, len = 64, typmod
= -1, byval = f)
2: usesysid = "1" (typeid = 23, len = 4, typmod = -1,
byval = t)
3: usecreatedb = "t" (typeid = 16, len = 1, typmod = -1,
byval = t)
4: usesuper = "t" (typeid = 16, len = 1, typmod = -1,
byval = t)
5: usecatupd = "t" (typeid = 16, len = 1, typmod = -1,
byval = t)
----

I'm not seeing the madisoncounty user in there. Also, I tried starting
the db using /etc/init.d/postgres start, and it fails. Is that the
proper way to get the service going, or should I be doing something else?

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