| Slony-I 2.2.10 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.
Every object in the script must be fully qualified or have the right search_path set. This will avoid any namespace confusion during replication.
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.
Slony-I 2.2.x and higher replicate the SQL requests of an EXECUTE SCRIPT alongside other logged replication activity as part of an ordinary SYNC. Scripts that perform an ALTER TABLE to a replicated table are replicated in the correct order with respect to other concurrent activities on that table, and this is guaranteed because of the exclusive lock that the ALTER TABLE acquired on the origin node. If your EXECUTE SCRIPT does not obtain exclusive locks on all of the tables it uses, then you need to make sure that any transactions running concurrently with the script are not making changes that can adversely affect the computations in the script.
Warning For example, if your script performs
nextval('some_replicated_seq'), and that sequence is concurrently being incremented by another transaction, then it is possible that when the queries are replayed on a replica, the sequence may have different values on the replica than it had on the origin.Warning In addition, it is crucial that DML requests propagated by EXECUTE SCRIPT be deterministic in their behaviour, otherwise the requests may (legitimately) be processed differently on different nodes, thereby corrupting data.
For instance, the following queries are all not deterministic, as they do not clearly indicate which of the tuples in the table will be affected:
Insufficiently specified insert into table2 (id, data) select id, data from table1 limit 10;
This query may legitimately take any 10 tuples from table1, no reason for it to behave the same across nodes.
Ridiculously different behaviour insert into table2 (id, data) select id, data from table1 order by random () limit 10;
This query makes it more obvious that it could draw any 10 tuples from table1, in any order.
Node-local results insert into table3 (id, data, transaction_date, value) select id, data, now(), random() from table1 order by id limit 10;
This query will compute
now()based on the time the query runs on each node, and values ofrandom()varying each time, so that while the order by id clause would have eliminated the ambiguity in the earlier cases, we can be quite certain that this query will put substantially different data into table3 on every node on which it is executed.
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()
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.