Skip site navigation (1) Skip section navigation (2)

Re: Cannot recreate DB scheme using pg_dump

From: prakashn(at)uci(dot)edu
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Cc: <nishad(at)ptolemy(dot)tlg(dot)uci(dot)edu>
Subject: Re: Cannot recreate DB scheme using pg_dump
Date: 2004-07-19 20:41:31
Message-ID: 1090266091.smmsdV1.1.2@smtp.uci.edu (view raw or flat)
Thread:
Lists: pgsql-bugs
>And which PG version are you using, anyway?
>

Oops, my bad.  It's PostgreSQL 7.4 on sparc-sun-solaris2.8, compiled by 
GCC 3.0.4.


>
>That's a tad hard to believe: superusers always have ownership rights.
>Could we see a complete example?
>

I've attached the schema dump, and the corresponding psql output when 
run by the superuser as "psql -f schema_dump db_name >& psql_output".  
If you don't have time to go through that, here's a brief summary:


The first group of errors I get when trying to create a new db (as
superuser) from the schema dump are failures to create indices on
certain tables (I can't see any difference between the tables on which
it succeeds and those on which it fails).  The errors are of the type:  


"ERROR: must be owner of relation cookie_log".  The relevant lines in 
the schema dump are:

  --
  -- TOC entry 302 (OID 16552096)
  -- Name: cl_ip_idx; Type: INDEX; Schema: public; Owner: nishad
  --
  
  CREATE INDEX cl_ip_idx ON cookie_log USING hash (ip); 

[The definition of the cookie_log table is: 

  --
  -- TOC entry 102 (OID 16169191)
  -- Name: cookie_log; Type: TABLE; Schema: public; Owner: nishad
  --

  CREATE TABLE cookie_log (
      ltime timestamp with time zone NOT NULL,
      id integer NOT NULL,
      ip inet NOT NULL,
      "action" text NOT NULL,
      cookie text NOT NULL
  );
]

Other index re-creation errors follow the same pattern.  This is 
followed by errors recreating indices upon definitions of primary keys, 
and errors recreating both foreign keys constraints and their associated 
triggers.  All errors are either "must be owner of relation..." or 
"permission denied for relation."  You can find examples of all these in 
the attached files by grepping for "ERROR" and looking up the 
corresponding line # in the schema dump.  I hope this is helpful; if 
not, please let me know how I can present this in a more intelligible 
way.


Cheers,

Nishad


Attachment: pgdumps.tar.gz
Description: application/x-gzip (20.0 KB)

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2004-07-19 21:06:26
Subject: Re: Cannot recreate DB scheme using pg_dump
Previous:From: Laurent FAILLIEDate: 2004-07-19 09:51:48
Subject: Re: Problem with PostGreSQL 7.4.2 on HPUX 11.11(700 PARISC Systems)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group