SEO

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.

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 ===

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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).

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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 ===

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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!

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq