Adding new tables to a Slony cluster
I have been using Slony for quite a while now and its been just very reliable and consistent for me. The only thing that I find complicated with its usage is doing DDL changes (i.e adding new tables to the cluster, doing changes to tables like adding new columns etc etc..).
The tables which are already there can be easily modified using Slony EXECUTE SCRIPT for ALTER TABLE commands. But the problem comes when you need to add a new table to am existing Slony set. Right now there is no way to add the new tables to the set once the set has been created, you can only delete a table from the set but not add it. But yes there is a way around that…
– Before we start anything make sure Slony cluster is in good shape by querying “select * from $clustername.sl_status” on master node and check for any abnormal lag times. If there is nothing abnormal, then proceed with adding new tables.
– Create the new tables on all the nodes involved in the Slony cluster using PSQL.
– Now create a new set (you can call it a temporary set) in the Slony cluster with the news tables/sequences.
– Once the set is created do the subscriptions for set exactly the same as the other set for which you wanted to add your tables to originally.
– After the subscriptions have been done, merge the temporary set into your original set (merge will only work if the subscriptions for old and new set are same)
Now you have the new tables/sequences added to your original set and the temporary set is no longer in $cluster.sl_set, you can verify this by querying $cluster.sl_tables …
select tab_set from _test_cluster.sl_table where tab_relname = ‘<your_new_table_name>’;