DDL for a single schema

[btober]

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Shridhar Daithankar]

On Friday 07 November 2003 18:53, bt****@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Shridhar Daithankar]

On Friday 07 November 2003 19:36, Shridhar Daithankar wrote:

On Friday 07 November 2003 18:53, bt****@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...pp-pgdump.html

Sorry for missing it first time..

Shridhar
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[btober]

On Friday 07 November 2003 19:36, Shridhar Daithankar wrote:

On Friday 07 November 2003 18:53, bt****@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...pp-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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Richard Huxton]

On Friday 07 November 2003 14:28, bt****@seaworthysys.com wrote:

On Friday 07 November 2003 19:36, Shridhar Daithankar wrote:

On Friday 07 November 2003 18:53, bt****@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Richard Huxton <de*@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Rod Taylor]

On Fri, 2003-11-07 at 08:23, bt****@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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQA/q6Tu6DETLow6vwwRAjDsAJ98PhoDxt7/tL7eRBqe1RjUo54figCggtVi
NFduLYHXxPzQzJJe5u8QxX0=
=jNcb
-----END PGP SIGNATURE-----