pg_dump data integrity & java
We are currently developing a java RMI Server to manage database backup on
FreeBSD 4.1 running Postgres 7.0.2 (we plan to upgrade to 7.1 when it is
released). The server will pg_dump individual tables and entire databases
at specified intervals and manage backup verification and backup file
maintenance.
The current postgres documentation indicates that pg_dump can either backup
one file at a time or an entire database within a single transaction in
order to maintain data integrity. My questions are:
If I pg_dump a single table which has a foreign key constraint, and
subsequently backup the constraint table, won't the data be out of sync?
Each pg_dump will run in it's own transaction, right?
Is there a way to dump multiple tables within a single transaction without
having to dump the entire database? If not, is it plausible for me to add
this option i.e. pg_dump -t <array_of_tables> ?
What happens when I restore if the data is out of sync?
As an aside, the current documentation does not reflect all of the pg_dump
options, specifically the "-f" option which will cause pg_dump to output to
a file instead of stdout. This information is very useful to java developers
who need to execute external commands and direct the output to somewhere
other than java... Is there anything I can do to help update documentation
for postgres configuration options in general?
Thanks for all comments.
Peter Schmidt
Prismedia Networks
pschmidt@prismedia.com
At 11:09 5/02/01 -0800, Peter Schmidt wrote:
If I pg_dump a single table which has a foreign key constraint, and
subsequently backup the constraint table, won't the data be out of sync?
Each pg_dump will run in it's own transaction, right?
Yes.
Is there a way to dump multiple tables within a single transaction without
having to dump the entire database? If not, is it plausible for me to add
this option i.e. pg_dump -t <array_of_tables> ?
The version in 7.1 should allow '-t *' or '--tables' which will dump all
tables. The ability to dump selected tables was not seen as a high priority.
What happens when I restore if the data is out of sync?
If it is dumped as inserts you will get RI errors. If done via copy then
some but not all constraints will be evaluated - I think that COPY does not
fire triggers.
As an aside, the current documentation does not reflect all of the pg_dump
options, specifically the "-f" option which will cause pg_dump to output to
Current docs do cover -f. Maybe you are looking at 7.0 docs?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
If it is dumped as inserts you will get RI errors. If done via copy then
some but not all constraints will be evaluated - I think that COPY does not
fire triggers.
Say what? COPY certainly does fire triggers ...
regards, tom lane
At 20:16 5/02/01 -0500, Tom Lane wrote:
Philip Warner <pjw@rhyme.com.au> writes:
If it is dumped as inserts you will get RI errors. If done via copy then
some but not all constraints will be evaluated - I think that COPY does not
fire triggers.Say what? COPY certainly does fire triggers ...
Is there any difference between COPY and INSERT other than speed?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
Say what? COPY certainly does fire triggers ...
Is there any difference between COPY and INSERT other than speed?
COPY does not fire ON INSERT rules. Other than that, AFAIR they're
the same from an integrity point of view.
regards, tom lane