How to Setup Slony-I Replication with Postgres Plus

Slony is a master to multiple slaves replication system with cascading and failover capabilities. When used with Postgres Plus, it becomes a powerful tool used for safeguarding data, failover/standby capabilities, optimizing online transaction processing, offloading reporting/BI queries, backup and restore, and migrating systems. This tutorial shows how to efficiently setup a basic master-slave system that can be applied to a variety of applications.

This Quick Tutorial is designed to help you expedite your Technical Evaluation of Postgres Plus Standard Server or Postgres Plus Advanced Server. For more informational assets on conducting your evaluation of Postgres Plus, visit the self-service portion of the EnterpriseDB web site: Postgres Plus Open Source Adoption center.

In this Quick Tutorial you will learn how to do the following:

understand key Slony replication terms and concepts

understand the basic components of a Slony Cluster

prepare Slony replication nodes

configure the replication cluster

start replication

Feature Description

Slony-I is a pre-bundled enterprise module installed by default with Postgres Plus Standard Server and Postgres Plus Advanced Server. Slony-I or Slony, as it will be referred to hereafter, provides the ability to replicate transactions from one master database to one or more slave databases. Slony is a trigger-based replication solution that asynchronously replicates every transaction that occurs on a source table or sequence object (sequence number generator created by the CREATE SEQUENCE statement) to the subscribed slave databases.

Slony replication is very versatile and can be used for a number of different applications:

Safeguard data with failover/standby capabilities

Optimize online transaction processing

Offload reporting/BI queries

Backup and restore

Migrate systems

The following is a summary of some of the basic characteristics of Slony:

Replication is unidirectional from master to slave databases.

Replication is from a single master.

Replicated tables in slave databases cannot be modified by other applications.

Replication is asynchronous. Transactions are committed independently on the master and each slave in near real time.

The types of database objects that can be replicated are tables and sequence objects. Replicated tables must have a primary key or a unique, non-null index that can substitute for a primary key.

Replication can be cascaded. Instead of receiving replicated data directly from the master, a slave may receive replicated data from another slave, instead.

The following is a brief description of some of the other features of Slony.

Controlled Switchover

At times, it may be necessary to bring the master node offline for a variety of reasons such as to perform system maintenance or a system upgrade. In that case, you would want one of the slave nodes to temporarily take over the role of the master node while the original master becomes a slave node and could hence, be taken offline.

This exchange of roles is called controlled switchover. When you perform a controlled switchover, the old master becomes a slave node and the slave node becomes the master node. All other slave nodes are notified of the change and become subscribers of the new master node.

Failover

In the event of a catastrophic failure of the master node, Slony supports the failover of the master node to a slave node. Failover is an irreversible action so it should only be done if the master node is not recoverable. Once the failover process is completed, the old master can be removed from the configuration. The new master takes over replication to the other slaves in the cluster.

Tutorial Steps

For a detailed description of the steps that show how to set up a Slony-I replication with Postgres Plus, please download the PDF: