There are several things you might want to do involving dropping things from Slony-I replication.
If you wish to drop an entire node from replication, the slonik command DROP NODE should do the trick.
This will lead to Slony-I dropping the triggers (generally that deny the ability to update data), restoring the "native" triggers, dropping the schema used by Slony-I, and the slon process for that node terminating itself.
As a result, the database should be available for whatever use your application makes of the database.
This is a pretty major operation, with considerable potential to cause substantial destruction; make sure you drop the right node!
The operation will fail if there are any nodes subscribing to the node that you attempt to drop, so there is a bit of a failsafe to protect you from errors.
sl_log_1 isn't getting purged documents some extra maintenance that may need to be done on sl_confirm if you are running versions prior to 1.0.5.
If you wish to stop replicating a particular replication set, the slonik command DROP SET is what you need to use.
Much as with DROP NODE, this leads to Slony-I dropping the Slony-I triggers on the tables and restoring "native" triggers. One difference is that this takes place on all nodes in the cluster, rather than on just one node. Another difference is that this does not clear out the Slony-I cluster's namespace, as there might be other sets being serviced.
This operation is quite a bit more dangerous than DROP NODE, as there isn't the same sort of "failsafe." If you tell DROP SET to drop the wrong set, there isn't anything to prevent potentially career-limiting "unfortunate results." Handle with care...
The UNSUBSCRIBE SET operation is a little less invasive than either DROP SET or DROP NODE; it involves dropping Slony-I triggers and restoring "native" triggers on one node, for one replication set.
Much like with DROP NODE, this operation will fail if there is a node subscribing to the set on this node.
| Warning |
For all of the above operations, "turning replication back on" will require that the node copy in a full fresh set of the data on a provider. The fact that the data was recently being replicated isn't good enough; Slony-I will expect to refresh the data from scratch. |
If the altperl utilities are installed, you can use the slonik_drop_table helper script to drop a table from replication. Simply run slonik_drop_table with no arguments to review the correct usage of the script. After dropping the table, you should also remove it from slon_tools.conf.
In Slony-I 1.0.5 and above, there is a Slonik command SET DROP TABLE that allows dropping a single table from replication without forcing the user to drop the entire replication set.
If you are running an earlier version, there is a "hack" to do this:
You can fiddle this by hand by finding the table ID for the table you want to get rid of, which you can find in sl_table, and then run the following three queries, on each host:
select _slonyschema.alterTableRestore(40); select _slonyschema.tableDropKey(40); delete from _slonyschema.sl_table where tab_id = 40;
The schema will obviously depend on how you defined the Slony-I cluster. The table ID, in this case, 40, will need to change to the ID of the table you want to have go away.
You'll have to run these three queries on all of the nodes, preferably firstly on the origin node, so that the dropping of this propagates properly. Implementing this via a slonik statement with a new Slony-I event would do that. Submitting the three queries using EXECUTE SCRIPT could do that; see Section 15 for more details. Also possible would be to connect to each database and submit the queries by hand.
Just as with SET DROP TABLE, version 1.0.5 introduces the operation SET DROP SEQUENCE.
If you are running an earlier version, here are instructions as to how to drop sequences:
The data that needs to be deleted to stop Slony-I from continuing to replicate the two sequences identified with Sequence IDs 93 and 59 are thus:
delete from _oxrsorg.sl_seqlog where seql_seqid in (93, 59); delete from _oxrsorg.sl_sequence where seq_id in (93,59);
Those two queries could be submitted to all of the nodes via schemadocddlscript_complete( integer, text, integer ) / EXECUTE SCRIPT, thus eliminating the sequence everywhere "at once." Or they may be applied by hand to each of the nodes.