Re: Unable to dump database using pg_dump - Mailing list pgsql-general

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@usnx.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
>