> Here's your problem: user1 has the same userid as pgsql. this
> used to happen in 7.0, I think. Is that what you're running?
I'm sure it's what I used to be running. I am running this now:
postgresql-7.1.2_2
according to FreeBSD ports.
> As to fixing it - have you created a lot of db objects (tables, views,
> functions, etc.) as user1 that need to be kept, or is this a test db?
There is data in a variety of tables belonging to user1 in the db1 database
that CANNOT be lost. I have tried using pg_dump and pg_dumpall with little
to no success:
yanek:~> pg_dump db1 > dump
getDatabase(): SELECT failed. Explanation from backend: 'ERROR: More than
one tuple returned by a subselect used as an expression.'.
> I _think_ you can get away with connecting to the db as a superuser,
> and updating the pg_shadow table so that user1 has a unique
> id. This means that everything user1 used to own will be owned by
> pgsql. Now you can drop and recreate the db that user1 is supposed to
> own.
Anyone can give confirmation on this procedure? I really cannot afford to
lose this data, though I have done an offline tar -zcvf and will again
before I do any dropping. So... just update the 'usesysid' in pg_shadow
(what about pg_user?), drop the database (which is now only owned by
pgsql?), and re-create that DB as user1?
Thanks for the info so far.
-Yanek.