Thread: 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
Attachment
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