I have been experimenting with exactly this sort of scheme. It's not simple but in tests it has worked well enough.
I can't share the code but the general approach is that each partition is processed in a number of distinct stages. All of the processing is done within the database, at the provider node, in plpgsql functions. For each new partition the steps taken are: 1) Create the table (eg mytab_200603) 2) Create a temporary replication set 3)* Add the new table to the temporary replication set 4)* Subscribe all subscriber nodes to the temporary replication set 5)* Merge the temporary replication set with the main set 6) Activate the partition The steps marked with an asterisk must wait until the previous steps have been completed at all slony nodes. I do this be scheduling each step a number of days in advance of the time the partition must become active, and running queries at the subscriber nodes, using dblink, to test that the previous step has completed. From plpgsql, I use: _cluster.ddlscript() to perform ddl (create the partition table, adjust views on it, etc) _cluster.storeset() to create the temporary replication set _cluster.setaddtable() to add the new partition to the temporary replicationset _cluster.subscribeset() to subscribe the temporary set to a subscriber node _cluster.mergeset() to merge the temporary set with the main set _cluster.setdroptable() to remove old, inactive, partitions from the main replication set <contrib>.dblink() to check the status of the temporary set, etc at each node All of this is automated and fired off by a daily cron script, which will only do real work when executing on the provider node for the main replication set. All per-partition ddl and dml is recorded in tables, and executed dynamically. If you have specific questions I will try to answer them. __ Marc On Thu, 2006-03-30 at 12:00 +0100, [EMAIL PROTECTED] wrote: > essage: 3 > Date: Wed, 29 Mar 2006 16:42:36 -0800 > From: "Nick Johnson" <[EMAIL PROTECTED]> > Subject: [Slony1-general] Dynamic table subscription > To: <[email protected]> > Message-ID: > <[EMAIL PROTECTED]> > Content-Type: text/plain; charset="us-ascii" > > Hi, > > > > We're attempting to apply Slony1 to our database for replication. > However, we use a table partitioning system that causes new tables to > be > created at runtime. Slony1 doesn't directly support runtime schema > changes, I realize, but we think we have a solution: There's a master > 'partition descriptor' table, which has a row for each table in the > system. If we were to create triggers on insert/update/delete for this > table on master and slave, we could, on the master, invoke a trigger > that adds the table to the set and configures it, and on the slave > invoke a trigger that creates the table (since it always follows a > defined template), and subscribes to its replication set. > > >
signature.asc
Description: This is a digitally signed message part
_______________________________________________ Slony1-general mailing list [email protected] http://gborg.postgresql.org/mailman/listinfo/slony1-general
