Thread: pgdump
Is there anyone who written a patch for a multiple pg_dump like: pg_dump -t table1 table2 ... tableN dbname Regards Enrico
On Friday 14 January 2005 11:45, Enrico wrote: > Is there anyone who written a patch for a multiple pg_dump like: > > pg_dump -t table1 table2 ... tableN dbname Yes, I have such a patch lying around(pg_dump -t table1 -t table2 ... dbname). It's for 7.4, but shouldn't be hard to port to 8.0. -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | Can i wash my clothes with my dvd drive? | Hoffsveien 17 | Or do i need to replace it with a washing | PO. Box 425 Skøyen | machine?? | 0213 Oslo | | NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
>Yes, I have such a patch lying around(pg_dump -t table1 -t table2 ... dbname). > >It's for 7.4, but shouldn't be hard to port to 8.0. > > > Oh wonderful, how can I see that? I'm working with 7.4.x version. Thanks Enrico
yo mero wrote: >you can use this in BASH: > > >for a in table1 table2 tableN >do >echo $a >pg_dump -t $a dbname > $a.sql >done > > >works fine > >leonel > > Yes I wrote that, but I wanted to know if is possible to do that without a bash script, Regards Enrico
On Friday 14 January 2005 14:54, Enrico wrote: > >Yes, I have such a patch lying around(pg_dump -t table1 -t table2 ... > > dbname). > > > >It's for 7.4, but shouldn't be hard to port to 8.0. > > Oh wonderful, how can I see that? I'm working with 7.4.x version. Actually, it's for 7.4beta3, but should probably apply to 7.4 final as well.... Here it is: http://dev.officenet.no/~andreak/pg_dump.c.diff -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | Can i wash my clothes with my dvd drive? | Hoffsveien 17 | Or do i need to replace it with a washing | PO. Box 425 Skøyen | machine?? | 0213 Oslo | | NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
> >Here it is: > >http://dev.officenet.no/~andreak/pg_dump.c.diff > > Many Thanks :))) Enrico
On Fri, 2005-01-14 at 16:24 +0100, Andreas Joseph Krogh wrote: > http://dev.officenet.no/~andreak/pg_dump.c.diff Looks good, except for some minor code cleanups and doc updates. Barring any objections, I'll clean it up and apply it once we branch 8.0. I suppose for consistency we ought to allow multiple schemas to be specified via the "-n" option? (Speaking of which, is the plan still to branch 8.0 very shortly after the 8.0.0 final release?) -Neil
Neil Conway wrote: > On Fri, 2005-01-14 at 16:24 +0100, Andreas Joseph Krogh wrote: > > http://dev.officenet.no/~andreak/pg_dump.c.diff > > Looks good, except for some minor code cleanups and doc updates. Barring > any objections, I'll clean it up and apply it once we branch 8.0. I > suppose for consistency we ought to allow multiple schemas to be > specified via the "-n" option? I don't remember this patch. How is it related to the other pg_dump patches in the 8.1 pathces queue? > (Speaking of which, is the plan still to branch 8.0 very shortly after > the 8.0.0 final release?) I guess we could do that now actually. -- 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, Pennsylvania19073
On Sun, 2005-01-16 at 23:42 -0500, Bruce Momjian wrote: > I don't remember this patch. http://archives.postgresql.org/pgsql-patches/2004-07/msg00331.php > How is it related to the other pg_dump > patches in the 8.1 pathces queue? I missed the July '04 discussion about the other patches for improving -t behavior. AFAIK the patches are unrelated. Something like the design elaborated here: http://archives.postgresql.org/pgsql-patches/2004-07/msg00374.php looks good to me, and would be preferrable to Andreas' patch IMHO. Unless I'm missing something, I don't see a patch from David Skoll in that thread that actually implements the above behavior. I'd be happy to implement Tom's suggested design for 8.1 unless someone has already beaten me to it. -Neil
Neil Conway wrote: > On Sun, 2005-01-16 at 23:42 -0500, Bruce Momjian wrote: > > I don't remember this patch. > > http://archives.postgresql.org/pgsql-patches/2004-07/msg00331.php > > > How is it related to the other pg_dump > > patches in the 8.1 pathces queue? > > I missed the July '04 discussion about the other patches for improving > -t behavior. AFAIK the patches are unrelated. > > Something like the design elaborated here: > > http://archives.postgresql.org/pgsql-patches/2004-07/msg00374.php > > looks good to me, and would be preferrable to Andreas' patch IMHO. > Unless I'm missing something, I don't see a patch from David Skoll in > that thread that actually implements the above behavior. I'd be happy to > implement Tom's suggested design for 8.1 unless someone has already > beaten me to it. There were actually competing pg_dump -n patches in July. I think I just kept the last one posted. -- 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, Pennsylvania19073
Neil Conway <neilc@samurai.com> writes: > Something like the design elaborated here: > http://archives.postgresql.org/pgsql-patches/2004-07/msg00374.php > looks good to me, and would be preferrable to Andreas' patch IMHO. > Unless I'm missing something, I don't see a patch from David Skoll in > that thread that actually implements the above behavior. I'd be happy to > implement Tom's suggested design for 8.1 unless someone has already > beaten me to it. A little further down-thread there was some discussion of also allowing wild cards in the individual switches, eg -t 's1.*' (This would differ from '-n s1' in that a -t switch would restrict the dump to tables only, whereas -n should take every sort of object in the selected schema.) I dismissed it at the time because we were too close to feature freeze, but the idea should be considered if you're going to do a new patch for 8.1. I think the issues would be * what are the wildcard rules exactly? * what about quoting/downcasing rules? Possibly it's sufficient to say "just like the way \d works in psql", but we should look closely before leaping. We've been burnt before by choosing rules that turned out to be awkward to use on a shell command line because of interference from the shell's quoting and expansion behavior. regards, tom lane
On Mon, 2005-01-17 at 00:24 -0500, Tom Lane wrote: > A little further down-thread there was some discussion of also allowing > wild cards in the individual switches, eg > > -t 's1.*' > > (This would differ from '-n s1' in that a -t switch would restrict the > dump to tables only, whereas -n should take every sort of object in the > selected schema.) Is this actually useful behavior? My gut feeling is "no", but I'm open to debate. ISTM that the combination of "-n" and "-t" achieves a pretty wide swath of the desired functionality. Considering that the various combinations of these switches is already quite complex, I think it would be wise to avoid additional, unnecessary complications. Plus it avoids the need to play games with escaping the wildcard from the shell. > * what about quoting/downcasing rules? If we don't implement wildcards, I don't believe we will need to change the present behavior of the "-n" and "-t" switches WRT case conversion etc. -Neil
Neil Conway <neilc@samurai.com> writes:
> On Mon, 2005-01-17 at 00:24 -0500, Tom Lane wrote:
>> A little further down-thread there was some discussion of also allowing
>> wild cards in the individual switches,
> Is this actually useful behavior?
Possibly not. It's been requested often enough, but multiple -t and -n
switches might be sufficient.
>> * what about quoting/downcasing rules?
> If we don't implement wildcards, I don't believe we will need to change
> the present behavior of the "-n" and "-t" switches WRT case conversion
> etc.
I'm not sure you can ignore the issue completely. The proposal you're
supporting included being able to pick out a specific table with-t s1.t1
and without any quoting rules it would then become impossible to deal
with names containing dots. Are we willing to blow off that case?
Or is it better to drop that part of the proposal?
regards, tom lane
On Mon, 2005-01-17 at 00:54 -0500, Tom Lane wrote: > -t s1.t1 > [...] without any quoting rules it would then become impossible to > deal with names containing dots. Ah, yeah -- sorry, I was focusing on case conversion rather than quoting in general. > Are we willing to blow off that case? > Or is it better to drop that part of the proposal? I would be OK with just ignoring this case, but on reflection I would prefer removing the "-t schema.table" syntax. Removing the feature resolves the quoting issue and also simplifies pg_dump's behavior. We lose the ability to dump table t1 in schema s1 and table t2 in schema s2 in a single command, but (a) you can specify "-t t1 -t t2 -n s1 -n s2", although this might also dump t1.s2 and/or t2.s1 (b) you can just run pg_dump twice, specifying the appropriate -t and -n options each time So the behavior would be that suggested earlier by David Skoll: > pg_dump -t t1 -- Dump table t1 in any schema > pg_dump -n s1 -- Dump all of schema s1 > pg_dump -t t1 -n s1 -- Dump t1 in s1 > pg_dump -t t1 -t t2 -n s1 -- Dump s1.t1 and s1.t2 > pg_dump -t t1 -t t2 -n s1 -n s2 -- Dump s1.t1, s1.t2, s2.t1 and s2.t2 We'd only raise an error if we found no matching tables/schemas, as was hashed out in July. -Neil
Neil Conway <neilc@samurai.com> writes:
> So the behavior would be that suggested earlier by David Skoll:
>> pg_dump -t t1 -- Dump table t1 in any schema
>> pg_dump -n s1 -- Dump all of schema s1
>> pg_dump -t t1 -n s1 -- Dump t1 in s1
>> pg_dump -t t1 -t t2 -n s1 -- Dump s1.t1 and s1.t2
>> pg_dump -t t1 -t t2 -n s1 -n s2 -- Dump s1.t1, s1.t2, s2.t1 and s2.t2
Well, that at least obeys the KISS principle ;-). Sure, let's try that
and see if it satisfies people.
Just to be clear: what I understand the logic to be is "OR" across
multiple switches of the same type, but "AND" across switches of
two types.
regards, tom lane
On Mon, 2005-01-17 at 01:19 -0500, Tom Lane wrote: > Just to be clear: what I understand the logic to be is "OR" across > multiple switches of the same type, but "AND" across switches of > two types. If I understand you correctly, you're suggesting that we should only report an error if none of the specified tables exist OR none of the specified schemas exist. I'm not sure I agree. Consider this command: pg_dump -t some_table -t non_existent_table Assuming some_table exists, we will now blithely ignore the nonexistent table. That is perfectly reasonable because of the cartesian explosion of possibilities that occurs when both -t and -n are specified, but in the absence of that it seems regrettable. The same applies to "-n foo -n non_existent_schema", naturally. An easy fix would be to raise an error for each specified but nonexistent object, *except* if both "-n" and "-t" are specified, in which case we use your behavior (report an error if none of the specified tables are found OR none of the specified schemas are found). Perhaps better would be to require that each "-t" or "-n" switch results in a 'match' -- i.e. if you specify "-t foo -n x -n y", we check that (a) schema x exists AND (b) schema y exists AND (c) table foo exists in (schema x OR schema y) This means we have tighter error checking, although I'm not sure how intuitive it is. -Neil
Neil Conway <neilc@samurai.com> writes:
> On Mon, 2005-01-17 at 01:19 -0500, Tom Lane wrote:
>> Just to be clear: what I understand the logic to be is "OR" across
>> multiple switches of the same type, but "AND" across switches of
>> two types.
> If I understand you correctly, you're suggesting that we should only
> report an error if none of the specified tables exist OR none of the
> specified schemas exist.
No, I was only expressing an opinion about what should be dumped,
not about what kind of diagnostic messages to issue.
If you want to warn about switches that fail to match anything,
go for it. (I vote for just a warning, though, not a hard error.)
regards, tom lane
Neil Conway wrote: >I would be OK with just ignoring this case, but on reflection I would >prefer removing the "-t schema.table" syntax. Removing the feature >resolves the quoting issue and also simplifies pg_dump's behavior. We >lose the ability to dump table t1 in schema s1 and table t2 in schema s2 >in a single command, but > >(a) you can specify "-t t1 -t t2 -n s1 -n s2", although this might also >dump t1.s2 and/or t2.s1 > >(b) you can just run pg_dump twice, specifying the appropriate -t and -n >options each time > >So the behavior would be that suggested earlier by David Skoll: > > > >>pg_dump -t t1 -- Dump table t1 in any schema >>pg_dump -n s1 -- Dump all of schema s1 >>pg_dump -t t1 -n s1 -- Dump t1 in s1 >>pg_dump -t t1 -t t2 -n s1 -- Dump s1.t1 and s1.t2 >>pg_dump -t t1 -t t2 -n s1 -n s2 -- Dump s1.t1, s1.t2, s2.t1 and s2.t2 >> >> > >We'd only raise an error if we found no matching tables/schemas, as was >hashed out in July. > > I really prefer the -t "schema.table" syntax over the scenario listed above. If you look at the syntax for psql "\" commands, and SQL commands, the structure "tablename, optionally schema-qualified" is seen time and time again. By allowing the same structure in arguments to pg_dump, you're helping add to an overall feeling of consistency in the postgres toolbox. My feeling is that, to an occasional or novice user of pg_dump, the proposed combination of -n and -t will seem daunting and idiosyncratic, especially for complex cases. The fact that with -n -t there are some cases that are actually impossible to perform in a single dump is quite a powerful disadvantage IMO. Yes, you *can* just run pg_dump multiple times, but I think anyone using pg_dump would rather quote out a wilcard than issue virtually the same command with one changed argument over and over again. Or writing a script to loop through the desired schema/table combinations and dumping each one at a time. Is command line quoting really that much of a hassle? And if so, what are the major hurdles?