Re: pg_dump problem

In article <9np0uc$ei9$1(at)news(dot)tht(dot)net>, "ljb"
<lbayuk(at)mindspring(dot)com> wrote:

> lholt(at)greensand(dot)net wrote:
>>Hi all,
>> anybody seen this one?
>>------
>>pgsu(at)dsl-225:~>pg_dump mydb > xxx.dump getDatabase():
>>SELECT failed. Explanation from backend: 'ERROR: More
>>than one tuple returned by a subselect used as an
>>expression. '.
>>-----------
>>I'm on Slackware 8.0 with postgres 7.1.2 compilied locally
>>and installed. Running AuthPg, mod_perl DBI serving
>>dynamic web pages. Everything appears to work ok except
>>pg_dump.
>>
>>Any Ideas?
>
> Check your list of databases and their owner IDs (select
> datname,datdba from pg_databases). Then check your
> pg_users table, looking for those owner ids (as usesysid).
> Maybe you have a duplicate user ID in your user table
> (pg_user/pg_shadow) which owns a database. This query
> might help:
> select datname,datdba,usename from pg_database,pg_user
> where datdba=usesysid;
> For each database name, there must be exactly one usename.

Thank you, mystery person
here's what your suggested command produced:
template1=# select datname,datdba,usename from pg_database,pg_user
template1-# where datdba=usesysid;
datname | datdba | usename
-----------+--------+---------
template1 | 1002 | pgsu
template0 | 1002 | pgsu
mydb | 1002 | pgsu
vgn | 1002 | pgsu
pgdemo | 1002 | pgsu
uaccess | 1002 | pgsu
Pagen2db | 1002 | pgsu
w3_prefs | 1002 | pgsu
template1 | 1002 | w3
template0 | 1002 | w3
mydb | 1002 | w3
vgn | 1002 | w3
pgdemo | 1002 | w3
uaccess | 1002 | w3
Pagen2db | 1002 | w3
w3_prefs | 1002 | w3
(16 rows)
-----------------------------
Pretty much screwed, I'd say. Don't have the foggest idea
of how it got that way, but I probably screwed up my
install somewhere. pgsu is my postgres user and w3 my
webmaster. w3 can create db's, but not add users. I think I
ran initdb as pgsu, then created all the other db's as w3,
then loaded them from a pg_dump output taken from a working
postgres 6.4 instalation. Everything worked as expected
until I tried to back stuff up.
For what it's worth, I have destroyed the whole works and
reloaded one of the smaller db's and now template 0 &1
are owned by pgsu and pg_dump works fine.

Thank you very much. I've been running postgres for
several years and have gone thru at least 3 upgrades and
cannot imagine what I did to create that situation.

--lynn