Christopher B. Browne's Home Page

4.6. Slony-I Upgrade

Minor Slony-I versions can be upgraded using the slonik SLONIK UPDATE FUNCTIONS(7)> command. This includes upgrades from 2.0.x to a newer version 2.0.y version.

When upgrading Slony-I, the installation on all nodes in a cluster must be upgraded at once, using the slonik(1) command SLONIK UPDATE FUNCTIONS(7).

While this requires temporarily stopping replication, it does not forcibly require an outage for applications that submit updates.

The proper upgrade procedure is thus:

The overall operation is relatively safe: If there is any mismatch between component versions, the slon(1) will refuse to start up, which provides protection against corruption.

You need to be sure that the C library containing SPI trigger functions has been copied into place in the PostgreSQL build. There are multiple possible approaches to this:

The trickiest part of this is ensuring that the C library containing SPI functions is copied into place in the PostgreSQL build; the easiest and safest way to handle this is to have two separate PostgreSQL builds, one for each Slony-I version, where the postmaster is shut down and then restarted against the "new" build; that approach requires a brief database outage on each node.

While that approach has been found to be easier and safer, nothing prevents one from carefully copying Slony-I components for the new version into place to overwrite the old version as the "install" step. That might not work on Windows™ if it locks library files that are in use. It is also important to make sure that any connections to the database are restarted after the new binary is installed.

Run make install to install new Slony-I components on top of the old

If you build Slony-I on the same system on which it is to be deployed, and build from sources, overwriting the old with the new is as easy as make install. There is no need to restart a database backend; just to stop slon(1) processes, run the UPDATE FUNCTIONS script, and start new slon(1) processes.

Unfortunately, this approach requires having a build environment on the same host as the deployment. That may not be consistent with efforts to use common PostgreSQL and Slony-I binaries across a set of nodes.

Create a new PostgreSQL and Slony-I build

With this approach, the old PostgreSQL build with old Slony-I components persists after switching to a new PostgreSQL build with new Slony-I components. In order to switch to the new Slony-I build, you need to restart the PostgreSQL postmaster, therefore interrupting applications, in order to get it to be aware of the location of the new components.

4.6.1. Incompatibilities between 1.2 and 2.0 TABLE ADD KEY issue in Slony-I 2.0

The TABLE ADD KEY slonik command has been removed in version 2.0. This means that all tables must have a set of columns that form a unique key for the table. If you are upgrading from a previous Slony-I version and are using a Slony-I created primary key then you will need to modify your table to have its own primary key before installing Slony-I version 2.0 New Trigger Handling in Slony-I Version 2

One of the major changes to Slony-I is that enabling/disabling of triggers and rules now takes place as plain SQL, supported by PostgreSQL 8.3+, rather than via "hacking" on the system catalog.

As a result, Slony-I users should be aware of the PostgreSQL syntax for ALTER TABLE, as that is how they can accomplish what was formerly accomplished via SLONIK STORE TRIGGER(7) and SLONIK DROP TRIGGER(7). SUBSCRIBE SET goes to the origin

New in 2.0.5 (but not older versions of 2.0.x) is that SLONIK SUBSCRIBE SET(7) commands are submitted by slonik to the set origin not the provider. This means that you only need to issue SLONIK WAIT FOR EVENT(7) on the set origin to wait for the subscription process to complete. WAIT FOR EVENT requires WAIT ON

With version 2.0 the WAIT FOR EVENT slonik command requires that the WAIT ON parameter be specified. Any slonik scripts that were assuming a default value will need to be modified

4.6.2. Upgrading to Slony-I version 2

The version 2 branch is substantially different from earlier releases, dropping support for versions of PostgreSQL prior to 8.3, as in version 8.3, support for a "session replication role" was added, thereby eliminating the need for system catalog hacks as well as the not-entirely-well-supported xxid data type.

As a result of the replacement of the xxid type with a (native-to-8.3) PostgreSQL transaction XID type, the slonik(1) command SLONIK UPDATE FUNCTIONS(7) is quite inadequate to the process of upgrading earlier versions of Slony-I to version 2.

In version 2.0.2, we have added a new option to SLONIK SUBSCRIBE SET(7), OMIT COPY, which allows taking an alternative approach to upgrade which amounts to:


There is a large "foot gun" here: during part of the process, Slony-I is not installed in any form, and if an application updates one or another of the databases, the resubscription, omitting copying data, will be left with data out of sync.

The administrator must take care; Slony-I has no way to help ensure the integrity of the data during this process.

The following process is suggested to help make the upgrade process as safe as possible, given the above risks.

Contact me at