Overview

Slony works by adding an AFTER UPDATE/INSERT/DELETE trigger to the master copy (that is, the origin) of every table that you want to replicate. An AFTER UPDATE/ INSERT/DELETE TRigger is a function that executes after an UPDATE, INSERT, or DELETE command modifies a table. The new trigger pushes each change from the origin into a history table. When a subscriber comes online, it pulls recent changes from the history table. The history table contains a record of every modification. If you UPDATE a row in a replicated table, and then DELETE that row, the history table contains an UPDATE command followed by a DELETE command. When a subscriber pulls the modification history from the provider, it applies each change, in order from earliest change to most recent. Since the original table changed from its earlier form to its current form by executing a sequence of commands (in a specific order), it stands to reason that applying the same changes to an identical copy of the table will produce an exact copy of the new form of the original table.

To illustrate the process, we'll pretend that your video store is expanding. The home office is located in Springfield and you are opening two new stores (one in Boomtown and one in Snoozeville). You have PostgreSQL installed at each site and you've named the hosts springfield, boomtown, and snoozeville (respectively). You want to replicate the customers table to each of the new branches. For the moment, I'll skip over the process of configuring the replication cluster and pretend that your cluster already is up and running. You've created a cluster (named branches) that pushes data from springfield to boomtown, and then from boomtown to snoozeville (in other words, springfield is the origin of springfield.customers, boomtown is a direct subscriber, and snoozeville is a chained subscriber).

I mentioned earlier that every node can act as a provider and a subscriber at the same time. If you were running a group of real-world video stores, you would replicate the boomtown and snoozeville databases as well. That way, each store would have a modifiable copy of its own data and a read-only copy of the data for every other store.

Let's say that an existing customer arrives at the springfield branch and tells you that he has a new phone number: You log in to the local server (springfield) and execute the following commands:

movies=# BEGIN WORK; BEGIN movies=# UPDATE customers SET phone = '555-3322' WHERE customer_id = '1'; UPDATE 1 movies=# COMMIT; COMMIT

When you execute the COMMIT command, the PostgreSQL server running on host springfield executes the AFTER UPDATE TRigger that Slony added to the customers table. The AFTER UPDATE trigger builds a second UPDATE command[1] and writes the text of the new command to the modification history table. (Specifically, the trigger writes the second UPDATE command to _branches.sl_log_1.) Notice that the trigger does not push the new UPDATE command to boomtown itself. Instead, a background process named slon, running on springfield, watches the sl_log_1 table. The slon daemon spends much of its time sleeping, but every 10 seconds, it wakes up and searches for new entries in the sl_log_1 table. If slon finds a new entry (meaning that someone has modified a replicated table), it writes a SYNC event into the _branches.sl_event table and executes a NOTIFY command that wakes up any active subscribers (in this case, boomtown).

[1] In this case, the new UPDATE command is identical to the one that you typed in, but that's not always the case. Consider an UPDATE statement that modifies more than one row. Such a command would generate a new UPDATE command for every row modified (each command would modify a single row).

On the subscriber side, boomtown is also running a slon daemon. boomtown's daemon knows that it subscribes to a SET that originates on springfield and connects to the PostgreSQL server running on springfield. You can see that connection for yourselfwhen you start the daemon on boomtown, a new listener appears in the pg_listeners table on host springfield. boomtown.slon listens for the notification signaled by springfield.slon. When boomtown.slon receives that notification, it searches through springfield's sl_log_1 table to find recent modifications ("recent" meaning a modification that boomtown has not already replicated). If it finds any recent modifications, boomtown applies those changes to its own copy of the replicated data. In this example, boomtown.slon finds the UPDATE command generated by springfield's AFTER UPDATE trigger and executes that command. When boomtown.slon has applied all of the modifications found in springfield.sl_log_1, it sends a confirmation back to springfield. The confirmation tells springfield that, as far as boomtown is concerned, the modification records in springfield.sl_log_1 are ancient history and may be purged.

If boomtown happens to be offline (that is, if there is no slon daemon servicing boomtown), it won't receive the notification signaled by springfield. When boomtown comes back online, it eventually receives a SYNC event from springfield and begins the process of "catching up" with springfield. (springfield sends a SYNC event every so often even if the replication set has not been modified.)

It's also possible that the slon daemon that services springfield may not be running at the time you UPDATE the customers table. In that case, the AFTER UPDATE trigger still pushes the modification record into _branches.sl_log_1, but it remains there until all (direct) subscribers have confirmed that they have applied the changes to their own copies.

You can see that Slony remains robust even when the origin and/or subscribers are offline. If a node doesn't receive a SYNC event immediately, it receives a SYNC event eventually and that's sufficient to ensure that modification history is never lost.

Now let's look at the process of creating the branches replication cluster.