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