Thank you for the answer. So if I understand correctly I would dump the slave databases in two steps: first schema only and then only the data that is not shared.
Restoring is: restore schema's and subscribe the node. wait untill it is in sync and then load the data. Three more questions then, hope you don't mind: 1 Is the schema dump from a running slave usable for recreation, or do I need to filter out the slony stuff from this schema only dump? 2. Is there any way of automating this? I have found the -l and -L option of pg_restore, but I couldn't find a similar option for pg_dump. 3. Should I use slony1_dump.sh for this datadump of the slave? Any help is much appreciated! Regards, Floris On 4/6/06, Jan Wieck <[EMAIL PROTECTED]> wrote: > On 4/5/2006 4:26 PM, F.Sluiter wrote: > > We have a setup where several databases share a schema with 5 tables. > > Many other tables are dependend with foreign keys on these 4 tables, > > but each database has its own unique set of these and the data in > > those is unique per database: The other schema's and tables between > > the slaves are therefore different from each other. > > Updates to the shared tables are infrequent (once a day a few records) > > and I can enforce that ids (pkeys) will not change or get deleted and > > all records are timestamped upon creation. > > Each database has its own maintainer(s) and is owned by a different > > organisation and each organisation has a few hundred users . > > > > To keep the 4 tables consistent between all the databases, I am > > considering slony to replicate the tables from a single source to the > > slaves. (Slaves are not allowed to change those tables only the master > > may). > > > > Now the catch: > > How do I backup the clients? Each night we do a pg_dump on all the > > databases. And in case of trouble we need to be able to separately > > restore a single database, sometimes even to a version from a few > > weeks ago. > > > > How do I get the shared tables in sync with the master, considering > > that emptying those tables for a rebuild is not a workable option > > because of the foreign key constraints? > > If you keep the shared tables in one schema and the "local" tables in > another, you can create a pg_dump of only the local tables. In case you > need to rebuild a node, you drop it completely, recreate the "shared" > part and let it subscribe. When the subscription is done and the shared > part is back in sync, you restore the dump of the local part. > > > Jan > > > > > > Is slony the way to go? or is dbmirror a better solution in this case, > > because that doesn't demand to start from an empty table, it just > > needs a table that is the same, which although a pain, can be done > > manually after the dump is restored and before bringing replication > > online again. I'm not even sure if dbmirror is stable enough, it does > > not seem to be used a lot. > > > > A third alternative is creating a trigger that contacts all databases > > through dblink directly, or just copy/past all the sql statements on > > the master via a script to all clients. When I log that with a > > timestamp in a file, I can recreate databases up to any point in time > > and reissue that sql to the shared tables to make them consistent > > again. But that sounds a lot like a manual replication system and I'd > > rather use something that is automated. > > > > Any answers, thoughts and comments are welcome! > > > > Floris > > _______________________________________________ > > Slony1-general mailing list > > [email protected] > > http://gborg.postgresql.org/mailman/listinfo/slony1-general > > > -- > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== [EMAIL PROTECTED] # > _______________________________________________ Slony1-general mailing list [email protected] http://gborg.postgresql.org/mailman/listinfo/slony1-general
