pgdump of schema...

From: "Net Virtual Mailing Lists" <mailinglists(at)net-virtual(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: pgdump of schema...
Date: 2004-11-24 06:23:48
Message-ID: 20041124062348.26736@mail.net-virtual.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

When I do a "pgdump --schema=someschema somedatabase > something.dump",
the results of the dump file look like this:

REVOKE ALL ON SCHEMA someschema FROM PUBLIC;
GRANT ALL ON SCHEMA someschema TO PUBLIC;

SET search_path = someschema, pg_catalog;

CREATE SEQUENCE emailtemplate_email_templat_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

CREATE TABLE emailtemplates (
email_template_id integer DEFAULT
nextval('"emailtemplate_email_templat_seq"'::text) NOT NULL,
template_name character varying(16) NOT NULL,
subject character varying(80) NOT NULL,
plain_text text,
html_block text,
entered_dt timestamp with time zone,
updated_dt timestamp with time zone
);

... This seems *extremely* problematic to me because unless explicitly
set search_path, it is not possible to insert data (in this case into
emailtemplates) because the sequence does not have the schema
specified.... Am I using schemas wrong?.. Should I be to do "INSERT
INTO someschema.emailtemplates" and expect it to work after restoring a
database in this fashion?...

I was really surprised to see this after having gone though extensive/
painstaking work to ensure that all of this was correct in my database
conversion to have pgdump essentially undo all of this -- yikes.... So I
am beginning to think that I must doing something wrong here....

- Greg

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Stark 2004-11-24 06:35:25 Re: Upcoming Changes to News Server ...
Previous Message Stephan Szabo 2004-11-24 05:28:47 Re: Table name as parameter in function