Thread

1. Cannot recreate DB scheme using pg_dump

   Nishad Prakash <prakashn@uci.edu> — 2004-07-16T23:22:21Z

   
   I'm trying to create a new database whose schema is the same as one
   that already exists.  To my surprise, the following steps don't work:
   
   1) pg_dump -s db1 > db1_schema
   2) createdb db2
   3) psql -f db1_schema db2
   
   Both db1 and db2 are created by the user "Bob", who also owns all the
   tables in db1.  Now, if step (3) is performed by Bob, then C language
   functions are not re-created, for lack of permission.  If step (3) is
   perfomed by the postgres superuser, then certain indices and foreign
   key constraints are not re-created and I see the error "must be owner
   of relation <foo>".
   
   Am I doing something wrong?  Is there a better way to do this?
   
   Nishad
   -- 
   "Underneath the concrete, the dream is still alive" -- Talking Heads
   
   
   

2. Re: Cannot recreate DB scheme using pg_dump

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-07-17T05:08:07Z

   Nishad Prakash <prakashn@uci.edu> writes:
   > If step (3) is perfomed by the postgres superuser, then certain
   > indices and foreign key constraints are not re-created and I see the
   > error "must be owner of relation <foo>".
   
   That's a tad hard to believe: superusers always have ownership rights.
   Could we see a complete example?
   
   And which PG version are you using, anyway?
   
   			regards, tom lane