Re: pg_dump problem
От | Lynn Holt |
---|---|
Тема | Re: pg_dump problem |
Дата | |
Msg-id | 9npj4r$kui$1@news.tht.net обсуждение исходный текст |
Ответ на | Re: pg_dump problem (lbayuk@mindspring.com (ljb)) |
Список | pgsql-general |
In article <9np0uc$ei9$1@news.tht.net>, "ljb" <lbayuk@mindspring.com> wrote: > lholt@greensand.net wrote: >>Hi all, >> anybody seen this one? >>------ >>pgsu@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
В списке pgsql-general по дате отправления: