Thread: Dump only part of a DB
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.
On Wed, 2004-06-09 at 08:09, David F. Skoll wrote: > 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...
On Wed, 9 Jun 2004, Scott Marlowe wrote: > 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.
On Wed, 2004-06-09 at 10:43, David F. Skoll wrote: > On Wed, 9 Jun 2004, Scott Marlowe wrote: > > > 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.
Scott Marlowe wrote: >On Wed, 2004-06-09 at 10:43, David F. Skoll wrote: > > >>On Wed, 9 Jun 2004, Scott Marlowe wrote: >> >> >>>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
"David F. Skoll" <dfs@roaringpenguin.com> writes:
> 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
Tom Lane wrote:
> "David F. Skoll" <dfs@roaringpenguin.com> writes:
> > 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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073