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@xxxxxxxx> 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
