Thread: dumping strategy
I have many tables and don't want to dump all of them. Of 30 tables about two are really huge and I actually don't mind losing those tables and don't want to backup. One of the reasons being that I transfer the back up to another host and wish to economize on bandwidth. pg_dump command cannot handle. Ideally what I would like to dump all but a few tables. Currently I am dumping all but a few tables to a directory and then make a tar ball and then bzip2 and transport to a machine of my choice. Can someone tell me a better solution? Thanks
Why wouldn't pg_dump work? You can do pg_dump -t <tablename> <databasename> to dump just a single table.... If pg_dump fails for some reason, I'd say the developers would like to know as I'm sure it's not supposed to :-) Good luck! -Mitch > I have many tables and don't want to > dump all of them. Of 30 tables > about two are really huge and I > actually don't mind losing those > tables and don't want to backup. > One of the reasons being that > I transfer the back up to another > host and wish to economize on > bandwidth. > > pg_dump command cannot handle. > Ideally what I would like to > dump all but a few tables. Currently > I am dumping all but a few > tables to a directory and > then make a tar ball and then > bzip2 and transport to a machine > of my choice. > > Can someone tell me a better > solution? > > Thanks > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Wed, May 30, 2001 at 02:59:16PM -0400, Mitch Vincent wrote: > Why wouldn't pg_dump work? You can do pg_dump -t <tablename> <databasename> > to dump just a single table.... If pg_dump fails for some reason, I'd say > the developers would like to know as I'm sure it's not supposed to :-) > > Good luck! > > -Mitch > > Sorry for the confusion. pg_dump works but I have to write a shell script to dump each individual tables. What I meant by it does not work is like this pg_dump -t table1 table2 table3 database |bzip2 > database.du
From: <newsreader@mediaone.net> > Sorry for the confusion. > > pg_dump works but I have to write > a shell script to dump each individual tables. > > What I meant by it does not work is like this > > pg_dump -t table1 table2 table3 database |bzip2 > database.du It's just for i in t1 t2 t3; do pg_dump -t$i mydb > $i.tbl; done - Richard Huxton
On Thu, May 31, 2001 at 11:49:48AM +0100, Richard Huxton wrote: > From: <newsreader@mediaone.net> > > > for i in t1 t2 t3; do pg_dump -t$i mydb > $i.tbl; done This is very nice _if_ I had _really_ named my tables like that.
> > for i in t1 t2 t3; do pg_dump -t$i mydb > $i.tbl; done > >This is very nice _if_ I had _really_ named >my tables like that. So you do for i in thistable thattable t1 t2 whatevername; do ... Unless tables are dynamically created and their names cannot be statically known, I don't see any problems.
On Thu, May 31, 2001 at 11:49:48AM +0100, Richard Huxton wrote: > From: <newsreader@mediaone.net> > > pg_dump works but I have to write > > a shell script to dump each individual tables. > > > > What I meant by it does not work is like this > > > > pg_dump -t table1 table2 table3 database |bzip2 > database.du > > It's just > > for i in t1 t2 t3; do pg_dump -t$i mydb > $i.tbl; done Although with a strategy like this, they're no guarantee that the snapshot you get will be consistent. And if you're using refential integrity it might not even restore properly. Cheers, Neil
Neil Conway <nconway@klamath.dyndns.org> writes:
>> It's just
>> for i in t1 t2 t3; do pg_dump -t$i mydb > $i.tbl; done
> Although with a strategy like this, they're no guarantee that the
> snapshot you get will be consistent. And if you're using refential
> integrity it might not even restore properly.
Good point. So who wants to tweak pg_dump to accept multiple -t
switches? Seems like
pg_dump -t foo -t bar -t baz dbname
is a reasonably non-ambiguous syntax.
regards, tom lane
> Neil Conway <nconway@klamath.dyndns.org> writes: > >> It's just > >> for i in t1 t2 t3; do pg_dump -t$i mydb > $i.tbl; done > > > Although with a strategy like this, they're no guarantee that the > > snapshot you get will be consistent. And if you're using refential > > integrity it might not even restore properly. > > Good point. So who wants to tweak pg_dump to accept multiple -t > switches? Seems like > > pg_dump -t foo -t bar -t baz dbname > > is a reasonably non-ambiguous syntax. Not that I am anywhere close to being able to make thsese changes, but it seems like it would be nice to have an option that says "dump all tables except the ones specified". Kind of like grep's -V option... maybe doing the two at the same time would be easier... -philip
On Thu, May 31, 2001 at 07:05:19PM -0700, Philip Hallstrom wrote: > > Neil Conway <nconway@klamath.dyndns.org> writes: > > pg_dump -t foo -t bar -t baz dbname > > > > is a reasonably non-ambiguous syntax. > > Not that I am anywhere close to being able to make thsese changes, but it > seems like it would be nice to have an option that says "dump all tables > except the ones specified". Kind of like grep's -V option... This is precisely what I am looking for. Who wants to type in a huge list of tables? Not me. Also I'm not qualified to make these changes myself. Others have contributed many shell scripts and very nice of them. And I could have come up equivalent perl one liner myself but I was hoping others have already hacked pg_dump
> This is precisely what I am looking for.
> Who wants to type in a huge list of tables?
> Not me. Also I'm not qualified to make
> these changes myself.
Apart from the referential inegrity problem, you can get the table
names with
SELECT relname FROM pg_class WHERE relname NOT LIKE 'pg_%';
it should not be too hard to build something on top of that.
With kind regards / Mit freundlichem Gruß
Holger Klawitter
--
Holger Klawitter
holger@klawitter.de http://www.klawitter.de