Re: Dump all except some tables?

> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)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(at)pervasive(dot)com

On Thu, Oct 06, 2005 at 04:31:14PM -0700, Roger Hand wrote:
> > From: pgsql-general-owner(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

On 10/7/05, David Fetter <david(at)fetter(dot)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(at)fetter(dot)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(at)fetter(dot)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(at)pervasive(dot)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(at)fetter(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

david(at)fetter(dot)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...