Forum OpenACS Q&A: Problem dumping under pg 7.1
duplicate it on a production server. pg_dump gives me
dumpSequence(user_id_sequence): SELECT failed. Explanation from
backend: 'ERROR: user_id_sequence: Permission denied.
This is with Debian Woody (testing), kernel 2.4.4, pg 7.1 in that
distribution. I've tried different users, including the postgres user.
I first got it with lob_sequence. Thinking it was specific to that
sequence I dropped it. It appears to be a general problem with all
I searched the PG lists to no avail. Has anyone seen this?
But sequences don't create an underlying dummy table AFAIK...
This problem doesn't occur with simple, vanilla setups (at least, it didn't for me) - what kind of user permissions have you set up? Have you tried granting all permissions on the sequence to the postgres user before dumping?
You may want to talk to the PG development folks about this directly - after upgrading to PG 7.1.2, though, as the view/table problem may've been fixed (and I know some other dump problems were fixed).
I'll contact the pg developers and see what they say.
Thanks for the reply, Don!
This was user error. pg_dump -S username database does not connect to postgres as "username". I exported a sane value for PG_USER to my environment and I was able to dump the database.