pgAdmin 1.6 online documentation

Slony-I is a “master to multiple slaves” replication system supporting cascading and slave promotion. The big picture for the development of Slony-I is as a master-slave system that includes the sorts of capabilities needed to replicate large databases to a reasonably limited number of slave systems. “Reasonable,” in this context, is on the order of a dozen servers. If the number of servers grows beyond that, the cost of communications increases prohibitively, and the incremental benefits of having multiple servers will be falling off at that point.

See also Section 2, “ Slony-I Communications Costs” for a further analysis of costs associated with having many nodes.

Slony-I is a system intended for data centers and backup sites, where the normal mode of operation is that all nodes are available all the time, and where all nodes can be secured. If you have nodes that are likely to regularly drop onto and off of the network, or have nodes that cannot be kept secure, Slony-I is probably not the ideal replication solution for you.

Thus, examples of cases where Slony-I probably won't work out well would include:

There is also a file-based log shipping extension where updates would be serialized into files. Given that, log files could be distributed by any means desired without any need of feedback between the provider node and those nodes subscribing via “log shipping.” “Log shipped” nodes do not add to the costs of communicating events between Slony-I nodes.

But Slony-I, by only having a single origin for each set, is quite unsuitable for really asynchronous multiway replication. For those that could use some sort of “asynchronous multimaster replication with conflict resolution” akin to what is provided by Lotus Notes™ or the “syncing” protocols found on PalmOS systems, you will really need to look elsewhere.

These other sorts of replication models are not without merit, but they represent different replication scenarios that Slony-I does not attempt to address.

Slony-I was born from an idea to create a replication system that was not tied to a specific version of PostgreSQL, which is allowed to be started and stopped on an existing database without the need for a dump/reload cycle.

All that being said, there are tools available to help with some of these things, and there is a plan under way for a subsequent system, Slony-II, to provide “multimaster” capabilities. But that represents a different, separate project, being implemented in a rather different fashion than Slony-I, and expectations for Slony-I should not be based on hopes for future projects.

Determining whether a node has “failed” is properly the responsibility of network management software, not Slony-I. The configuration, fail-over paths, and preferred policies will be different for each site. For example, keep-alive monitoring with redundant NIC's and intelligent HA switches that guarantee race-condition-free takeover of a network address and disconnecting the “failed” node will vary based on network configuration, vendor choices, and the combinations of hardware and software in use. This is clearly in the realm of network management and not Slony-I.

Furthermore, choosing what to do based on the “shape” of the cluster represents local business policy, particularly in view of the fact that FAIL OVER requires discarding the failed node. If Slony-I imposed failover policy on you, that might conflict with business requirements, thereby making Slony-I an unacceptable choice.

As a result, let Slony-I do what it does best: provide database replication services.

Slony-I does not automatically propagate schema changes, nor does it have any ability to replicate large objects. There is a single common reason for these limitations, namely that Slony-I collects updates using triggers, and neither schema changes, large object operations, nor TRUNCATE requests are able to have triggers suitable to inform Slony-I when those sorts of changes take place. As a result, the only database objects where Slony-I can replicate updates are tables and sequences.

Note that with the use of triggers comes some additional fiddling around with triggers. On the “origin” for each replicated table, an additional trigger is added which runs the stored procedure schemadoc.logtrigger( ). On each subscriber, tables are augmented with a trigger that runs the schemadocdenyaccess( ) function; this function prevents anything other than the slon process from updating data in replicated tables. In addition, any other triggers and rules on replicated tables are suppressed on the subscribers: This is done by pointing them, in the system table, to the primary key index instead of to the table itself. This represents something of a “corruption” of the data dictionary, and is why you should not directly use pg_dump to dump schemas on subscribers.

There is a capability for Slony-I to propagate other kinds of database modifications, notably DDL changes, if you submit them as scripts via the slonik EXECUTE SCRIPT operation. That is not handled “automatically;” you, as a database administrator, will have to construct an SQL DDL script and submit it, via EXECUTE SCRIPT and there are a number of further caveats.

If you have those sorts of requirements, it may be worth exploring the use of PostgreSQL 8.X PITR (Point In Time Recovery), where WAL logs are replicated to remote nodes. Unfortunately, that has two attendant limitations:

There are a number of distinct models for database replication; it is impossible for one replication system to be all things to all people.

Slony-I implements a particular model, namely that of asynchronous replication, using triggers to collect table updates, where a single “origin” may be replicated to multiple “subscribers” including cascaded subscribers.

There are a number of other replication models which are different ; it is worth pointing out other approaches that exist. Slony-I is certainly not the only approach, and for some applications, it is not the optimal approach.