| Slony-I 2.1.4 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 3. Administration Tasks | Fast Forward | Next |
3.3. Database Schema Changes (DDL)
When changes are made to the database schema, e.g. - adding fields to a table, it is necessary for this to be handled rather carefully, otherwise different nodes may get rather deranged because they disagree on how particular tables are built.
Slony-I can not automatically detect and replicate database schema changes however, Slony-I does provide facilities to assist in making database schema changes. Schema changes can be done on a replicated database either by using the Slony-I SLONIK EXECUTE SCRIPT (slonik) command or by manually applying the changes to each node.
3.3.1. DDL Changes with Execute Script
The SLONIK EXECUTE SCRIPT (slonik) command allows you to submit a SQL script (that can, but is not required to) contain DDL commands. This script will be executed on the event node and then (optionally) replicated to every other node in the cluster. You should keep the following in mind when using SLONIK EXECUTE SCRIPT
The script must not contain transaction BEGIN or END statements, as the script is already executed inside a transaction though nested transactions are allowed as long are processed within the scope of a single transaction whose BEGIN and END you do not control.
If there is anything broken about the script, or about how it executes on a particular node (other than the event node), this will cause the slon daemon for that node to panic and crash. You may see various expected messages (positive and negative) in Section 5.5.6.2. If you restart the slon, it will, most likely, try to repeat the DDL script, which will, almost certainly, fail the second time in the same way it did the first time.
The implication of this is that it is vital that modifications not be made in a haphazard way on one node or another. The schemas must always stay in sync. If slon; fails due to a failed DDL change then you should manually (via psql) make the required changes so that the DDL change succeeds the next time slon attempts it.
Slony-I 2.0.x and 2.1.x suffer from an issue where concurrent transactions involving the same tables as are referenced in the script might not be replayed in exactly the same order on the replica nodes. It is advisiable to not be concurrently inserting,deleting or updating rows to a table while a script changing that table (adding or deleting columns) is also running.
3.3.2. Applying DDL Changes Directly
DDL changes can be applied directly on a node through an application such as psql. The DDL changes will not be replicated by Slony-I and therefore must be manually applied to every relevant node. The following points should be kept in mind when applying DDL changes directly.
While DDL changes are not automatically replicated, any INSERT,UPDATE,DELETE statements that you execute will be captured for replication, when run against the origin node. This means that you should not include DDL changes and DML inside the same script when apply DDL directly, because the script will not behave properly when you execute it on other nodes.
If you, instead, apply DDL using EXECUTE SCRIPT, it is fine to intersperse DDL and DML within the script, as Slony-I handles that appropriately.
You are responsible for ensuring that your scripts get applied on all other nodes at the correct point in the replication stream (e.g. - on or before the appropriate SYNC event). The best way of doing this with respect to adding and deleting columns is to make sure that new columns always get added on the replica nodes first and that columns being removed are dropped from the master before they are dropped from the replicas. That way, new columns are always available on the subscriber on or before the time they will be needed, and obsolete ones remain on the subscriber until after the last possible reference to them has been replicated.
Warning If columns being added or dropped are mandatory (NOT NULL) or have default values, you will need to go through a longer process to ensure constraints are satisfied at each point in time on all nodes.
For instance, if dropping a column that has a NOT NULL constraint, it may take multiple ALTER TABLE statements on each node in order to successfully accomplish this, as the constraint needs to be relaxed first.
DDL changes that rename a replicated table do not inform Slony-I of the new table name. If you change then name of a replicated table you must allow Slony-I to find the new table name by calling schemadocupdaterelname(p_only_on_node integer, p_set_id integer)
DDL changes that alter either a primary key, a unique constraint that slony is using, or DDL changes that drop columns that come before the key or unique constraint that Slony-I is using will require Slony-I too reconfigure the arguments on the logtrigger. The function schemadocrepair_log_triggers(only_locked boolean) will reconfigure the trigger arguments of any Slony-I log triggers that are out of date. If true is passed to this function it will only adjust tables that are already locked by the current transaction (if you perform your alter table within a transaction and then call
repair_log_triggers()as part of the same transaction then the altered tables will be locked). If you pass false to this function then the function will obtain an exclusive lock on any table that needs the trigger to be reconfigured.