Thread: pg_dump problem
Good morning,
I tried to dump a pg 7.1 database last night, but got the following error:
dumpSequence(user_id_sequence): SELECT failed. Explanation from backend:
'ERROR: user_id_sequence: Permission denied.
user_id_sequence is just
'create sequence user_id_sequence'
Thinking it might be a 7.1 problem I upgraded to 7.1.2. Same problem.
I created a test case:
p0 rack[44]% psql -U postgres template1
~/wd/kksmith/www/doc/sql
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
template1=# create user test;
CREATE USER
template1=# create database test;
CREATE DATABASE
template1=# \q;
p0 rack[45]% psql -U test test
~/wd/kksmith/www/doc/sql
test=> create sequence user_id_sequence
CREATE
test=> \d
List of relations
Name | Type | Owner
------------------+----------+-------
user_id_sequence | sequence | test
(1 row)
test=> \q
p0 rack[46]% pg_dump -S postgres --no-owner test > ~/test.dump
dumpSequence(user_id_sequence): SELECT failed. Explanation from backend:
'ERROR: user_id_sequence: Permission denied.
'.
p0 rack[47]% psql -U test test
~/wd/kksmith/www/doc/sql
test=> grant all on user_id_sequence to test;
CHANGE
test=> \q
p0 rack[49]% pg_dump -S postgres --no-owner test >! ~/test.dump
dumpSequence(user_id_sequence): SELECT failed. Explanation from backend:
'ERROR: user_id_sequence: Permission denied.
I'm stumped. Suggestions? Post to pgsql-hackers? Or file a bug report?
--cro
cro@ncacasi.org
"C. R. Oldham" <cro@ncacasi.org> writes:
> Ahh! I understand. Exporting a sane value for PG_USER fixed it.
> This (and -u) are the only ways to change the user that pg_dump connects
> as?
AFAIR, yes.
regards, tom lane
"C. R. Oldham" <cro@ncacasi.org> writes:
> p0 rack[46]% pg_dump -S postgres --no-owner test > ~/test.dump
> dumpSequence(user_id_sequence): SELECT failed. Explanation from backend:
> 'ERROR: user_id_sequence: Permission denied.
Hmm. Which user is pg_dump connecting as? (The -S switch does not
answer this question, btw; that only determines what's emitted into
the output script.) I would expect behavior like this if you are
connecting as someone who's neither a superuser nor the sequence
owner. Check PGUSER and USER environment variables.
regards, tom lane
cro@nca.asu.edu wrote: > > 'ERROR: user_id_sequence: Permission denied. tgl@sss.pgh.pa.us wrote: > Hmm. Which user is pg_dump connecting as? Ahh! I understand. Exporting a sane value for PG_USER fixed it. This (and -u) are the only ways to change the user that pg_dump connects as? -- / C. R. (Charles) Oldham | NCA-CASI \ / Director of Technology | Arizona State University \ / cro@nca.asu.edu | V:480-965-8703 F:480-965-9423 \
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.
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