| Slony-I 1.2.22 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Fast Forward | Next | |
Slony-I does not directly provide support for the PostgreSQL methodology of partitioning via inheritance, but it does not, by the same token, prevent the Gentle User from using that sort of replication scheme, and then replicating the underlying tables.
One of the tests in the Section 25, called testinherit, tests that Slony-I behaves as expected to replicate data across partitions. This test creates a master sales_data table, from which various children inherit:
us_east
us_west
canada
The example is somewhat simplistic as it only provides rules to handle initial insertion into the respective partitions; it does not then support allowing tuples to migrate from partition to partition if they are altered via an UPDATE statement. On the other hand, unlike with many partitioning cases, this one permits the "parent" table to contain tuples.
Things worth observing include:
Each partition table must be added to replication individually.
Slony-I is not aware of the relationship between partitions; it simply regards them as a series of individual tables.
One common "use case" of replication is to partition large data sets based on time period, whether weekly, monthly, quarterly, or annually, where there is therefore a need to periodically add a new partition.
The traditional approach taken to this in Slony-I would be the following:
SLONIK EXECUTE SCRIPT to add the new partition(s) on each node
SLONIK CREATE SET to create a temporary replication set
SLONIK SET ADD TABLE to add the table(s) to that set
SLONIK SUBSCRIBE SET, once for each subscriber node, to set up replication of the table on each node
SLONIK MERGE SET, once subscriptions are running, to eliminate the temporary set
In view of the fact that we can be certain that a thus-far-unused partition will be empty, we offer an alternative mechanism which evades the need to create extra replication sets and the need to submit multiple SLONIK SUBSCRIBE SET requests. The alternative is as follows; we use SLONIK EXECUTE SCRIPT, extending the DDL script thus:
Add the new partition(s) on each node
Run a Slony-I stored function to mark the new partition as being a replicated table
On the origin node, if the table is found to have tuples in it, the DDL script will be aborted, as the precondition that it be empty has been violated.
On subscriber nodes, we may safely TRUNCATE the new table.
There are several stored functions provided to support this;
the Gentle User may use whichever seems preferable. The "base
function" is add_empty_table_to_replication(); the others
provide additional structure and validation of the arguments
add_empty_table_to_replication (set_id, tab_id, nspname, tabname, idxname, comment);
This is the "base" function; you must specify the set ID, table ID, namespace name, table name, index name, and a comment, and this table will be added to replication.
Note that the index name is optional; if NULL, the function will look up the primary key for the table, assuming one exists, and fail if it does not exist.
replicate_partition(tab_id, nspname, tabname, idxname, comment);
If it is known that the table to be replicated inherits from a replicated parent table, then this function can draw set and origin information from that parent table.
Note: As has been observed previously, Slony-I is unaware that tables are partitioned. Therefore, this approach may also be used with confidence to add any table to replication that is known to be empty.