pgdump

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, Pennsylvania 19073

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, Pennsylvania 19073

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?