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
Show quoted text
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; doneAlthough 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 likepg_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 dbnameis 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