Dump only part of a DB

[David F. Skoll]

Hi,

pg_dump can be used to dump an entire database, or just a single
table. Is there a way to make a consistent dump of more than one
table, but less than all of the tables in the database? Doing a bunch
of single-table pg_dumps isn't really an option, because some tables
may change during the dumps, leading to constraint violations if we
try to restore them.

Rationale: We have an application that makes a nightly dump of its
database. There's one particular table that tends to be large, but happily
it's not critical if it's lost -- no real need to back it up. So we'd
like to back up everything except that one large, non-critical table.

Regards,

David.

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

[Scott Marlowe]

Post by David F. Skoll
Hi,
pg_dump can be used to dump an entire database, or just a single
table. Is there a way to make a consistent dump of more than one
table, but less than all of the tables in the database? Doing a bunch
of single-table pg_dumps isn't really an option, because some tables
may change during the dumps, leading to constraint violations if we
try to restore them.
Rationale: We have an application that makes a nightly dump of its
database. There's one particular table that tends to be large, but happily
it's not critical if it's lost -- no real need to back it up. So we'd
like to back up everything except that one large, non-critical table.

If you put the application's data into a specific schema, then you can
dump just that schema with the -n switch...


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[David F. Skoll]

Post by Scott Marlowe
If you put the application's data into a specific schema, then you can
dump just that schema with the -n switch...

Thanks. That's a solution for 7.4, but some of our installed base
(especially the older ones with large DB's that cause the problem) are
still on 7.2 or 7.3.

I'll keep it in mind, though.

Regards,

David.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Scott Marlowe]

Post by David F. Skoll

Post by Scott Marlowe
If you put the application's data into a specific schema, then you can
dump just that schema with the -n switch...

Thanks. That's a solution for 7.4, but some of our installed base
(especially the older ones with large DB's that cause the problem) are
still on 7.2 or 7.3.

7.3 supported schemas, but I'm not sure its pg_dump supported dumping
individual ones.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bill Montgomery]

Post by Scott Marlowe

Post by David F. Skoll

Post by Scott Marlowe
If you put the application's data into a specific schema, then you can
dump just that schema with the -n switch...

Thanks. That's a solution for 7.4, but some of our installed base
(especially the older ones with large DB's that cause the problem) are
still on 7.2 or 7.3.

7.3 supported schemas, but I'm not sure its pg_dump supported dumping
individual ones.

I'm running 7.3.4 and use pg_dump with individual schemas.

-Bill Montgomery

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Post by David F. Skoll
pg_dump can be used to dump an entire database, or just a single
table. Is there a way to make a consistent dump of more than one
table, but less than all of the tables in the database?

This has been discussed before, and I think we had agreed that the
Right Thing is to make pg_dump accept more than one -t switch (also
more than one -n switch, at the schema level), and dump anything
that matches any -t or -n switch.

No one's got round to making this happen, but it seems like it
should not be a big job. Want to send in a patch?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bruce Momjian]

Post by Tom Lane

Post by David F. Skoll
pg_dump can be used to dump an entire database, or just a single
table. Is there a way to make a consistent dump of more than one
table, but less than all of the tables in the database?

This has been discussed before, and I think we had agreed that the
Right Thing is to make pg_dump accept more than one -t switch (also
more than one -n switch, at the schema level), and dump anything
that matches any -t or -n switch.
No one's got round to making this happen, but it seems like it
should not be a big job. Want to send in a patch?

Added to TODO:

* Allow pg_dump to use multiple -t and -n switches
--
Bruce Momjian | http://candle.pha.pa.us
***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org