Обсуждение: Backup of multiple tables

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi.

I usually backup my database with pg_backup without the -t option. But now I
need to only backup certain tables(say tab1 and tab2), is this possible with
pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without success.

- --
Andreas Joseph Krogh <andreak@officenet.no>
Managing Director, Senior Software Developer
OfficeNet AS

- - Writing software is more fun than working.

gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/au5eUopImDh2gfQRAsu4AKC0R9WhMMlqbRAPhe+Si+zykxe5bACeLOCm
VRBGOqu78we2O9IxbOTlWIc=
=5Keu
-----END PGP SIGNATURE-----

On Fri, Sep 19, 2003 at 01:54:01PM +0200, Andreas Joseph Krogh wrote:
> I usually backup my database with pg_backup without the -t option. But now I
> need to only backup certain tables(say tab1 and tab2), is this possible with
> pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without success.

Here's a perl script I used to dump all the tables separately.
I'm not sure most of options do work there, I didn't test ;-)
It won't be hard to make it dump certain tables, I think.


--
Fduch M. Pravking

On Fri, Sep 19, 2003 at 04:30:57PM +0200, Andreas Joseph Krogh wrote:
> > Here's a perl script I used to dump all the tables separately.
> > I'm not sure most of options do work there, I didn't test ;-)
> > It won't be hard to make it dump certain tables, I think.
> 
> Thanks for your suggestion, but the problem with it is that I may end up with 
> inconsistencies if data is inserted/updated or deleted in one of the tables 
> during the backup, so I would miss the "snapshot"-effect.

You can try to explicitly lock all tables being dumped from the
script before and release them after dump is complete...
But there could be dead-lock conditions.

What will gurus say?

-- 
Fduch M. Pravking

Andreas Joseph Krogh <andreak@officenet.no> writes:
> I usually backup my database with pg_backup without the -t option. But now I 
> need to only backup certain tables(say tab1 and tab2), is this possible with 
> pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without success.

pg_dump can only handle one -t option at a time.  It'd make sense to
allow multiple -t options (likewise -n) but no one's got round to
improving the code in that particular direction.  I don't think it would
be hard; want to fix it and send in a patch?
        regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Friday 19 September 2003 17:38, Tom Lane wrote:
> Andreas Joseph Krogh <andreak@officenet.no> writes:
> > I usually backup my database with pg_backup without the -t option. But
> > now I need to only backup certain tables(say tab1 and tab2), is this
> > possible with pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without
> > success.
>
> pg_dump can only handle one -t option at a time.  It'd make sense to
> allow multiple -t options (likewise -n) but no one's got round to
> improving the code in that particular direction.  I don't think it would
> be hard; want to fix it and send in a patch?

I've never looked at the code-base of pgsql before, but I'll give it a try.
Don't expect anything real soon tho.

- --
Andreas Joseph Krogh <andreak@officenet.no>
Managing Director, Senior Software Developer
OfficeNet AS

- - Writing software is more fun than working.

gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/b3jeUopImDh2gfQRAhP3AKCwsviN37+7ZAG4x4hI/ErQEyKmHACfW4on
EI900FlO/lDInoDnf9nDEM0=
=Xcq/
-----END PGP SIGNATURE-----

On 23 Sep 2003 at 0:34, Andreas Joseph Krogh wrote:

> On Friday 19 September 2003 17:38, Tom Lane wrote:
>
> > pg_dump can only handle one -t option at a time.  It'd make sense to
> > allow multiple -t options (likewise -n) but no one's got round to
> > improving the code in that particular direction.  I don't think it would
> > be hard; want to fix it and send in a patch?
> 
> I've never looked at the code-base of pgsql before, but I'll give it a try. 
> Don't expect anything real soon tho.

If you do deliver, I for one will buy you a beer should we ever meet. I'm sure others would feel simlarly obliged.
-- 
Dan Langille : http://www.langille.org/

On Tue, Sep 23, 2003 at 12:34:00AM +0200, Andreas Joseph Krogh wrote:
> 
> I've never looked at the code-base of pgsql before, but I'll give it a try. 
> Don't expect anything real soon tho.
If you acheive to add the selection of tables to dump, you may
at the same time try to add a similar option but working schemas. And,
yes, there's a beer waiting for you here too!

-- 
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage