[HACKERS] 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 [EMAIL PROTECTED]
Re: [HACKERS] pg_dump data integrity java
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 |/
Re: [HACKERS] pg_dump data integrity java
Philip Warner [EMAIL PROTECTED] 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
Re: [HACKERS] pg_dump data integrity java
At 20:16 5/02/01 -0500, Tom Lane wrote: Philip Warner [EMAIL PROTECTED] 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 |/
Re: [HACKERS] pg_dump data integrity java
Philip Warner [EMAIL PROTECTED] 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
