Thread: Dump all except some tables?
Is it possible to dump an entire database but to skip one or two tables? Or, conversely, to restore an entire dump except for one or two tables? (Although I'd prefer the first version.)
The only related option for both pg_dump and pg_restore is --table, which only takes 1 (one) table name. If only it accepted more than one I could've found a workaround.
Any idea, other than messing around with the dump file? I don't look forward to grepping a dump which is several tens of megabytes gzipped...
I'm considering doing a dump with --table for each table except the one or two in question. But I wonder, if I simply concatenate the resulting SQL dumps, will I get a valid dump? There are all kinds of foreign key contraints in place, and if the table data is not fed back in the right order it's useless.
The only related option for both pg_dump and pg_restore is --table, which only takes 1 (one) table name. If only it accepted more than one I could've found a workaround.
Any idea, other than messing around with the dump file? I don't look forward to grepping a dump which is several tens of megabytes gzipped...
I'm considering doing a dump with --table for each table except the one or two in question. But I wonder, if I simply concatenate the resulting SQL dumps, will I get a valid dump? There are all kinds of foreign key contraints in place, and if the table data is not fed back in the right order it's useless.
am 06.10.2005, um 13:59:44 +0300 mailte WireSpot folgendes: > Is it possible to dump an entire database but to skip one or two tables? Or, > conversely, to restore an entire dump except for one or two tables? > (Although I'd prefer the first version.) > > The only related option for both pg_dump and pg_restore is --table, which > only takes 1 (one) table name. If only it accepted more than one I could've > found a workaround. You can use the -t more than once. pg_dump -U foobar database -t foo -t foo1 > I'm considering doing a dump with --table for each table except the one or > two in question. But I wonder, if I simply concatenate the resulting SQL > dumps, will I get a valid dump? There are all kinds of foreign key I'm not sure, perhaps if you dump with --data-only every table. > contraints in place, and if the table data is not fed back in the right > order it's useless. Right. You can do a dump from all tables and after restore all the tables you can delete the one or two tables. Other way: pg_restore with '--use-list=list-file'. You can create a list of contents of the archive and edit this list. (pg_restore --list) Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
WireSpot wrote: > Is it possible to dump an entire database but to skip one or two > tables? Or, conversely, to restore an entire dump except for one or > two tables? (Although I'd prefer the first version.) > > The only related option for both pg_dump and pg_restore is --table, > which only takes 1 (one) table name. If only it accepted more than one > I could've found a workaround. > > Any idea, other than messing around with the dump file? I don't look > forward to grepping a dump which is several tens of megabytes gzipped... > > I'm considering doing a dump with --table for each table except the > one or two in question. But I wonder, if I simply concatenate the > resulting SQL dumps, will I get a valid dump? There are all kinds of > foreign key contraints in place, and if the table data is not fed back > in the right order it's useless. I don't think you can limit the dump output precisely as you ask, but you can get the equivalent by doing a custom format dump, then use pg_restore to produce a archive listing, which you then edit so as to select specific objects you want to include/exclude, and then run pg_restore against that edited list file.
On 10/6/05, A. Kretschmer <akretschmer@despammed.com> wrote: > am 06.10.2005, um 13:59:44 +0300 mailte WireSpot folgendes: > > Is it possible to dump an entire database but to skip one or two tables? Or, > > conversely, to restore an entire dump except for one or two tables? > > (Although I'd prefer the first version.) > > > > The only related option for both pg_dump and pg_restore is --table, which > > only takes 1 (one) table name. If only it accepted more than one I could've > > found a workaround. > > You can use the -t more than once. > > pg_dump -U foobar database -t foo -t foo1 Yes, pg_dump doesn't complain. But it only takes the first one into consideration. I only get one table in the dump. > > I'm considering doing a dump with --table for each table except the one or > > two in question. But I wonder, if I simply concatenate the resulting SQL > > dumps, will I get a valid dump? There are all kinds of foreign key > > I'm not sure, perhaps if you dump with --data-only every table. > > > contraints in place, and if the table data is not fed back in the right > > order it's useless. > > Right. > > You can do a dump from all tables and after restore all the tables you > can delete the one or two tables. That's exactly what I'm trying to avoid. If possible, I wanted to avoid having to dump them at all. We're talking a lot of logging data that is of no use where the dump is going. > Other way: > > pg_restore with '--use-list=list-file'. You can create a list of > contents of the archive and edit this list. (pg_restore --list) Interesting, I'll look into it. If mangling the restore list works, it would solve the problem halfway (at the restoring moment).
am 06.10.2005, um 15:29:50 +0300 mailte WireSpot folgendes: > > > The only related option for both pg_dump and pg_restore is --table, which > > > only takes 1 (one) table name. If only it accepted more than one I could've > > > found a workaround. > > > > You can use the -t more than once. > > > > pg_dump -U foobar database -t foo -t foo1 > > Yes, pg_dump doesn't complain. But it only takes the first one into > consideration. I only get one table in the dump. Oops, sorry. Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
WireSpot <wirespot@gmail.com> writes:
> On 10/6/05, A. Kretschmer <akretschmer@despammed.com> wrote:
>> You can use the -t more than once.
>>
>> pg_dump -U foobar database -t foo -t foo1
> Yes, pg_dump doesn't complain. But it only takes the first one into
> consideration. I only get one table in the dump.
Right. That's on the TODO list but not done yet :-(.
Berend gave you the right advice: dump the whole database using
pg_dump -Fc, then use pg_restore's features for selective restore.
regards, tom lane
On Thu, Oct 06, 2005 at 09:51:22AM -0400, Tom Lane wrote: > WireSpot <wirespot@gmail.com> writes: > > On 10/6/05, A. Kretschmer <akretschmer@despammed.com> wrote: > >> You can use the -t more than once. > >> > >> pg_dump -U foobar database -t foo -t foo1 > > > Yes, pg_dump doesn't complain. But it only takes the first one > > into consideration. I only get one table in the dump. > > Right. That's on the TODO list but not done yet :-(. There have been several patches against pg_dump to do both multiple-table inclusion and multiple-table exclusion. These may get into 8.2, as they missed 8.1. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On Thu, Oct 06, 2005 at 12:32:26PM -0700, David Fetter wrote: > On Thu, Oct 06, 2005 at 09:51:22AM -0400, Tom Lane wrote: > > WireSpot <wirespot@gmail.com> writes: > > > On 10/6/05, A. Kretschmer <akretschmer@despammed.com> wrote: > > >> You can use the -t more than once. > > >> > > >> pg_dump -U foobar database -t foo -t foo1 > > > > > Yes, pg_dump doesn't complain. But it only takes the first one > > > into consideration. I only get one table in the dump. > > > > Right. That's on the TODO list but not done yet :-(. > > There have been several patches against pg_dump to do both > multiple-table inclusion and multiple-table exclusion. These may get > into 8.2, as they missed 8.1. Looking at the archived discussion from the TODO (http://momjian.postgresql.org/cgi-bin/pgtodo?pg_dump), I find myself wondering if it would be good to allow for specifying a set of rules for what to dump in a file, probably something like a set of regexes with a way to specify if it's an include or exclude rule. Seems like it would be a heck of a lot simpler to do that for complex cases than deal with a pile of spaghetti on the command-line, but I've never really worried about it. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jim C. Nasby > Sent: Thursday, October 06, 2005 3:34 PM > Subject: Re: [GENERAL] Dump all except some tables? > > ... I find myself > wondering if it would be good to allow for specifying a set of rules for > what to dump in a file, probably something like a set of regexes with a > way to specify if it's an include or exclude rule. Seems like it would > be a heck of a lot simpler to do that for complex cases than deal with a > pile of spaghetti on the command-line It may be useful to cut down on command line clutter if one could specify a file holding a list of table names to include/exclude. -Roger > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
On Thu, Oct 06, 2005 at 04:31:14PM -0700, Roger Hand wrote: > > From: pgsql-general-owner@postgresql.org > > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jim C. Nasby > > Sent: Thursday, October 06, 2005 3:34 PM > > Subject: Re: [GENERAL] Dump all except some tables? > > > > ... I find myself wondering if it would be good to allow for > > specifying a set of rules for what to dump in a file, probably > > something like a set of regexes with a way to specify if it's an > > include or exclude rule. Seems like it would be a heck of a lot > > simpler to do that for complex cases than deal with a pile of > > spaghetti on the command-line > > It may be useful to cut down on command line clutter if one could > specify a file holding a list of table names to include/exclude. Here's my thoughts on a summary: [-t [table | glob]]... # 0 or more -t options [-T [table | glob]]... # 0 or more -T options [--include-tables-from-file f] [--exclude-tables-from-file f] where globs get expanded just the way they are in psql, and the exclude is evaluated after the include to remove any tables where they might conflict. I don't think regex matching is needed or good. Does this make sense? Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On 10/7/05, David Fetter <david@fetter.org> wrote: > Here's my thoughts on a summary: > > [-t [table | glob]]... # 0 or more -t options > [-T [table | glob]]... # 0 or more -T options > [--include-tables-from-file f] > [--exclude-tables-from-file f] > > where globs get expanded just the way they are in psql, and the > exclude is evaluated after the include to remove any tables where they > might conflict. I don't think regex matching is needed or good. > > Does this make sense? Sure does, and it looks good. But... will the resulting dump be consistent as far as foreign keys are concerned? Or will the current -t warning still apply (YMMV as to the consistency of the resulting dump)? If it's my job to ensure foreign key consistency, an option that only dumps foreign keys and/or omits the foreign keys from the dump would also be essential... Grepping a full dump, as I said, is not nice, plus the foreign keys are multi-line which complicates grepping. If both table filtering and the foreign key options would be implemented, one could truly do useful dumps using pg_dump alone. I could dump only some tables sans the foreign keys, then dump the foreign keys separately and take it from there. I know that I can get the foreign keys from a schema-only dump. But an "don't dump foreign keys" option would still help.
On Fri, Oct 07, 2005 at 11:47:26AM +0300, WireSpot wrote: > On 10/7/05, David Fetter <david@fetter.org> wrote: > > Here's my thoughts on a summary: > > > > [-t [table | glob]]... # 0 or more -t options > > [-T [table | glob]]... # 0 or more -T options > > [--include-tables-from-file f] > > [--exclude-tables-from-file f] > > > > where globs get expanded just the way they are in psql, and the > > exclude is evaluated after the include to remove any tables where > > they might conflict. I don't think regex matching is needed or > > good. > > > > Does this make sense? > > Sure does, and it looks good. > > But... will the resulting dump be consistent as far as foreign keys > are concerned? Or will the current -t warning still apply (YMMV as > to the consistency of the resulting dump)? I think the latter is better. This is solidly in the realm of prying off cover plates, and the warning is already there :) > If it's my job to ensure foreign key consistency, an option that > only dumps foreign keys and/or omits the foreign keys from the dump > would also be essential... Grepping a full dump, as I said, is not > nice, plus the foreign keys are multi-line which complicates > grepping. I think we can avoid a giant rat hole here by not trying to follow foreign keys. Can we consider following foreign keys a separate feature for later discussion and just go with the (not totally trivial) considerations of schema and table inclusion and exclusion? Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On Fri, Oct 07, 2005 at 02:07:47AM -0700, David Fetter wrote: > On Fri, Oct 07, 2005 at 11:47:26AM +0300, WireSpot wrote: > > But... will the resulting dump be consistent as far as foreign keys > > are concerned? Or will the current -t warning still apply (YMMV as > > to the consistency of the resulting dump)? > > I think the latter is better. This is solidly in the realm of prying > off cover plates, and the warning is already there :) I think it would be good to include an option that only does checking and doesn't actually try to dump anything. That would make it easier to ensure your config file is correct. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Fri, Oct 07, 2005 at 08:21:31PM -0500, Jim C. Nasby wrote: > On Fri, Oct 07, 2005 at 02:07:47AM -0700, David Fetter wrote: > > On Fri, Oct 07, 2005 at 11:47:26AM +0300, WireSpot wrote: > > > But... will the resulting dump be consistent as far as foreign > > > keys are concerned? Or will the current -t warning still apply > > > (YMMV as to the consistency of the resulting dump)? > > > > I think the latter is better. This is solidly in the realm of > > prying off cover plates, and the warning is already there :) > > I think it would be good to include an option that only does > checking and doesn't actually try to dump anything. That would make > it easier to ensure your config file is correct. Could you flesh this out a bit? What would this option produce in the (imho most common) case where dependencies weren't all taken care of? Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On Sat, Oct 08, 2005 at 02:22:23PM -0700, David Fetter wrote: > On Fri, Oct 07, 2005 at 08:21:31PM -0500, Jim C. Nasby wrote: > > On Fri, Oct 07, 2005 at 02:07:47AM -0700, David Fetter wrote: > > > On Fri, Oct 07, 2005 at 11:47:26AM +0300, WireSpot wrote: > > > > But... will the resulting dump be consistent as far as foreign > > > > keys are concerned? Or will the current -t warning still apply > > > > (YMMV as to the consistency of the resulting dump)? > > > > > > I think the latter is better. This is solidly in the realm of > > > prying off cover plates, and the warning is already there :) > > > > I think it would be good to include an option that only does > > checking and doesn't actually try to dump anything. That would make > > it easier to ensure your config file is correct. > > Could you flesh this out a bit? What would this option produce in the > (imho most common) case where dependencies weren't all taken care of? For one thing, it would produce a list of missing dependancies (or any other errors that could be detected without doing the actual dump, for that matter). I think that when trying to setup a non-trival dump scenario, it would be great to actually produce output stating exactly what dump would have done had it run for real. One way to think of it would be running pg_dump -v and piping stdout to /dev/null. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
david@fetter.org (David Fetter) writes: > On Fri, Oct 07, 2005 at 08:21:31PM -0500, Jim C. Nasby wrote: >> On Fri, Oct 07, 2005 at 02:07:47AM -0700, David Fetter wrote: >> > On Fri, Oct 07, 2005 at 11:47:26AM +0300, WireSpot wrote: >> > > But... will the resulting dump be consistent as far as foreign >> > > keys are concerned? Or will the current -t warning still apply >> > > (YMMV as to the consistency of the resulting dump)? >> > >> > I think the latter is better. This is solidly in the realm of >> > prying off cover plates, and the warning is already there :) >> >> I think it would be good to include an option that only does >> checking and doesn't actually try to dump anything. That would make >> it easier to ensure your config file is correct. > > Could you flesh this out a bit? What would this option produce in the > (imho most common) case where dependencies weren't all taken care of? I'd think that throwing in the "-s" option would allow a meaningful dry run... -- let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/languages.html Frisbeetarianism: The belief that when you die, your soul goes up on the roof and gets stuck...