Thread: sync two databases
Hi, I have two databases (8.1.11) where the content of some tables should be synced nightly. I tried to dump the contents with a script (similar to a pg_backup script), and then load them again, but there is a DROP statement in the dump, that doesn't work on restore since there are dependencies between tables (restore says I should use DROP CASCADED, which I don't dare...). Is there a way to only dump the data? I can't seem to find the respective parameters in pg_dump documentation. Another thing: I have seen that database grow to 48Mio rows in one table (I didn't do the design ;)), so I'm afraid a standard dump using "COPY" could give me problems later on... though a "INSERT" is said to be slow? Any ideas on this?
If you're using pg_dump or pg_dumpall, I believe teh switch you want is "-a". <http://www.postgresql.org/docs/8.1/interactive/app-pgdump.html> <http://www.postgresql.org/docs/8.1/interactive/app-pg-dumpall.html> Be sure to also investigate whether or not you need --disable-triggers when you restore. It sounds like you might. Charley Oeschey, Lars (I/EK-142, extern) wrote: > Hi, > > I have two databases (8.1.11) where the content of some tables should be > synced nightly. I tried to dump the contents with a script (similar to a > pg_backup script), and then load them again, but there is a DROP > statement in the dump, that doesn't work on restore since there are > dependencies between tables (restore says I should use DROP CASCADED, > which I don't dare...). Is there a way to only dump the data? I can't > seem to find the respective parameters in pg_dump documentation. > Another thing: I have seen that database grow to 48Mio rows in one table > (I didn't do the design ;)), so I'm afraid a standard dump using "COPY" > could give me problems later on... though a "INSERT" is said to be slow? > > Any ideas on this? > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate >