Thread

1. DDL for a single schema

   Berend Tober <btober@seaworthysys.com> — 2003-11-07T13:23:56Z

   Is there a way to get a dump of all the DDL and data associated with a
   single schema within a database?
   
   What I tried in attempting to hack this out was
   
   \dt consume.*
   
   and
   
   \dv consume.*
   
   to get a list of tables and views in the schema named "consume", then
   sent those lists to a file "tables.txt". Followed that with something
   like
   
   for n in `cat tables.txt` do; pg_dump -t $n >> consume.sql; done
   
   This almost worked, except for that I have some duplicate table names in
   other schemas, and the pg_dump picked up both the table I wanted in the
   consume schema and the the similarly-named table in a different schema.
   I'd really like to be able to get the complete dump for one schema and no
   more. Might work if you could specify a schema-qualified table name in
   the -t option of pg_dump, but that apparently is not possible currently.
   
   I tried also using pg_restore to generate a list file, thinking I could
   edit the output list file to include only the tables from desired schema.
   This would really be a better approach than what I've show above, but
   having the duplicate table names was again the problem because nothing in
   the list file distinquishes which schema the tables are members of.
   
   ~Berend Tober
   
   
   
   
   

2. Re: DDL for a single schema

   Rod Taylor <rbt@rbt.ca> — 2003-11-07T13:58:07Z

   On Fri, 2003-11-07 at 08:23, btober@seaworthysys.com wrote:
   > Is there a way to get a dump of all the DDL and data associated with a
   > single schema within a database?
   
   The 7.4 pg_dump is capable of this task (--schema=SCHEMA) but you may
   have difficulties restoring to a 7.3 backend.
   

3. Re: DDL for a single schema

   Shridhar Daithankar <shridhar_daithankar@myrealbox.com> — 2003-11-07T14:06:11Z

   On Friday 07 November 2003 18:53, btober@seaworthysys.com wrote:
   > Is there a way to get a dump of all the DDL and data associated with a
   > single schema within a database?
   >
   > What I tried in attempting to hack this out was
   >
   > \dt consume.*
   >
   > and
   >
   > \dv consume.*
   >
   > to get a list of tables and views in the schema named "consume", then
   > sent those lists to a file "tables.txt". Followed that with something
   > like
   >
   > for n in `cat tables.txt` do; pg_dump -t $n >> consume.sql; done
   
   pg_dump in 7.4 has the dump option. You need to use --schema as option.
   
    Check developers documentation on web site or download RC1 and try out 
   yourself.
   
   HTH
   
    Shridhar
   
   
   

4. Re: DDL for a single schema

   Shridhar Daithankar <shridhar_daithankar@myrealbox.com> — 2003-11-07T14:11:29Z

   On Friday 07 November 2003 19:36, Shridhar Daithankar wrote:
   > On Friday 07 November 2003 18:53, btober@seaworthysys.com wrote:
   > > Is there a way to get a dump of all the DDL and data associated with a
   > > single schema within a database?
   > >
   > > What I tried in attempting to hack this out was
   > >
   > > \dt consume.*
   > >
   > > and
   > >
   > > \dv consume.*
   > >
   > > to get a list of tables and views in the schema named "consume", then
   > > sent those lists to a file "tables.txt". Followed that with something
   > > like
   > >
   > > for n in `cat tables.txt` do; pg_dump -t $n >> consume.sql; done
   >
   > pg_dump in 7.4 has the dump option. You need to use --schema as option.
   >
   >  Check developers documentation on web site or download RC1 and try out
   > yourself.
   
   http://developer.postgresql.org/docs/postgres/app-pgdump.html
   
   Sorry for missing it first time..
   
    Shridhar
   
   
   

5. Re: DDL for a single schema

   Berend Tober <btober@seaworthysys.com> — 2003-11-07T14:28:55Z

   > On Friday 07 November 2003 19:36, Shridhar Daithankar wrote:
   >> On Friday 07 November 2003 18:53, btober@seaworthysys.com wrote:
   >> > Is there a way to get a dump of all the DDL and data associated
   >> with a single schema within a database?
   >> pg_dump in 7.4 has the dump option. You need to use --schema as
   >> option.
   >>
   >>  Check developers documentation on web site or download RC1 and try
   >> out
   >> yourself.
   >
   > http://developer.postgresql.org/docs/postgres/app-pgdump.html
   >
   > Sorry for missing it first time..
   >
   >  Shridhar
   
   Thanks. Didn't have immediate plans to upgrade from 7.3. But I'll look
   for that feature when the time comes.
   
   ~Berend Tober
   
   
   
   
   

6. Re: DDL for a single schema

   Richard Huxton <dev@archonet.com> — 2003-11-07T17:03:14Z

   On Friday 07 November 2003 14:28, btober@seaworthysys.com wrote:
   > > On Friday 07 November 2003 19:36, Shridhar Daithankar wrote:
   > >> On Friday 07 November 2003 18:53, btober@seaworthysys.com wrote:
   > >> > Is there a way to get a dump of all the DDL and data associated
   > >>
   > >> with a single schema within a database?
   > >> pg_dump in 7.4 has the dump option. You need to use --schema as
   > >> option.
   
   > Thanks. Didn't have immediate plans to upgrade from 7.3. But I'll look
   > for that feature when the time comes.
   
   I believe 7.4 pg_dump should work against a 7.3 database (and further back 
   IIRC).
   -- 
     Richard Huxton
     Archonet Ltd
   
   

7. Re: DDL for a single schema

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-07T17:42:45Z

   Richard Huxton <dev@archonet.com> writes:
   > I believe 7.4 pg_dump should work against a 7.3 database (and further back 
   > IIRC).
   
   It will, but I don't think we guarantee that the output will load into a
   pre-7.4 database --- pg_dump usually assumes its SQL output can take
   advantage of all the features it knows about.
   
   This might work more-or-less-okay for 7.3, but you'd better test before
   depending on it.
   
   			regards, tom lane