Replicating PostgreSQL Databases Using Slony-I

Christopher Browne

Afilias Canada

An introduction to the use of Slony-I, an asynchronous single-master to multiple slaves replication system for use with PostgreSQL

What is Slony-I?

Slony-I is an asynchronous single master to multiple slave replication system for PostgreSQL supporting multiple database releases, cascaded replication, and slave promotion.

What is Slony-I?

Slony-I is a replication system for PostgreSQL

Replication: Updates applied to one database are applied to another database

INSERT, DELETE, UPDATE

Some replication systems intercept transaction logs; Slony-I uses triggers on tables to collect changes

Why Replicate?

Redundancy – allowing rapid recovery from some hardware failures

Separate reporting from on-line activity: Performance

Separate reporting from on-line system: Security

What is Slony-I?

Slony-I is an asynchronous replication system

Asynchronous: Updates are COMMITted to one database, and are applied to others later (possibly much later)

Contrast with synchronous, where updates must commit on multiple hosts at the same time

What is Slony-I?

Slony-I is an asynchronous single master to multiple slave replication system

Updates are applied at the origin, and are replicated to a set of subscribers

Slony-I allows each table to have a different origin – but only one origin!

Alternative: multimaster – more complex, heavy locking costs and/or conflict resolution problems

What is Slony-I?

Slony-I supports multiple PostgreSQL releases

Supports PostgreSQL versions 7.3.3 and higher

Earlier versions not supported – no namespaces

Preparing version upgrade

Prepare upgrade...

7.3.9

8.0.3

Master

Replicate

S

Slave

Slony-I may take 48h to populate the replica, but

that requires no outage on the master system

Database version upgrade (2)

Once “in sync,” MOVE SET takes seconds

7.3.9

8.0.3

Slave

move set

S

Master

The former master can become slave; this provides a

fall back strategy “Just in Case”

What is Slony-I?

Slony-I supports cascaded replication

NY1

Master

NY2

NYC Data Center

LA1

LA2

LA Data Center

WAN

Several LA servers feeding from just one source

LA3

What is Slony-I?

Slony-I supports slave promotion

NY1

NY2

NYC Data Center

LA1

Master

LA2

LA Data Center

LA3

MOVE SET shifts origin to LA

Uses for Slave Promotion

Upgrades: Set up subscriber running new PG version, then promote it to “master”

Maintenance: Shift origin to a new server allowing extended maintenance

Avoid failure: Shift origin from a failing server to one that is “less ailing.”

Fail Over

For extreme cases of problems with master, Slony-I supports full scale fail-over

Since Slony-I is asynchronous, some transactions committed on the master won't have made it to other nodes

As a result, FAIL OVER must lead to dropping the failed node

Slony-I Components

Databases – one for each node

Slon daemon – one for each node

Slonik – configuration controller

Virtual: Network configuration

Components: Databases

Each node is a PostgreSQL database with:

C libraries to implement trigger functions

pl/pgsql functions for non-time-critical code

Database namespace/schema for configuration

On origin nodes – triggers to capture updates

On subscriber nodes – triggers to protect data

Slony-I processes connect as a superuser, e.g. slony

Components: slon daemons

Each node has a slon instance.

This is a C program that propagates events between nodes

Most event types are for configuring Slony-I

SYNC events are where data “providers” tell subscribers that they have new data to replicate

Components: slonik

The slonik command processor takes configuration scripts in an SQL-like language and submits them to the cluster

STORE NODE, STORE PATH, STORE LISTEN

CREATE SET, SET (ADD|DROP|MOVE) TABLE, SET (ADD|DROP|MOVE) SEQUENCE

SUBSCRIBE SET, LOCK SET, MOVE SET, FAIL OVER, EXECUTE SCRIPT

Slonik is only used when modifying configuration; when system is stable, it remains unused

Components: Network Configuration

Configuration paths from “admin workstation” to all nodes – connections may be temporary and/or slow but must be comprehensive

Communications paths between slon daemons and Slony-I nodes – need to be fast and reliable; create only the links you need

Administrative Connections

slonik needs to communicate with every node

Replication Connections

Persistent, reliable, fast 2-way connections between some nodes

Redundancy

wanted!

Configuration Terminology

Cluster – the set of databases

Node – each database in the cluster

Replication set – a set of tables and sequences replicated from a single origin

Origin, subscribers, providers – the shape of the replication “network”

Paths – routes slon uses to talk to DBs

Listen paths – determine path usage

Cluster

A Slony-I cluster is the set of database instances where replication takes place.

Give the thing being replicated a name:

ORG, Payroll, PriceDB, STorm

The name identifies the schema used to store Slony-I data, thus _ORG, _Payroll, _PriceDB, ...

Slonik scripts specify: cluster name=ORG;

Slon daemons are passed the cluster name:

slon -d4 -g80 STorm 'host=db1 db=nws_storm'

Node

Each PostgreSQL database being used for replication is a Slony-I “node”

Each has a schema containing Slony-I-specific tables, sequences, functions

Cluster T1 has the following tables:

_T1.sl_config_lock _T1.sl_confirm _T1.sl_event _T1.sl_listen

_T1.sl_log_1 _T1.sl_log_2 _T1.sl_log_status _T1.sl_node _T1.sl_path

_T1.sl_seqlastvalue _T1.sl_seqlog _T1.sl_sequence _T1.sl_set

_T1.sl_setsync _T1.sl_status _T1.sl_subscribe _T1.sl_table

_T1.sl_trigger

Slonik commands: store node, drop node, uninstall node

Replication Sets

Replication sets are the “container” for each set of tables and sequences being replicated

Each set's data originates on one node and is published to other nodes

By having multiple sets with multiple origins, you can get a sort of multimaster replication

Slonik commands: create set, drop set, subscribe set, merge set, set add table, set add sequence, ...

Weak Form of Multimaster Replication

DB1 is origin for table tab1 in set 1

DB2 is origin for table tab2 in set 2

DB3 is origin for table tab3 in set 3

DB1

DB2

DB3

tab1

tab2

tab2

tab3

tab3

Three replication sets can have different propagation networks

Origin, Provider, Subscriber

The terms “master” and “slave” become inaccurate if there are more than 2 nodes

Nodes are not themselves either master or slave

For each replication set, and hence for each table, there is exactly one “origin”

All the other nodes can be “subscribers”

Each subscriber draws data from a “provider” which may either be the origin, or a subscriber downstream from the origin.

Admin Paths

One variety of “communications path” is the one used by slonik from “admin server” to all nodes

These are encoded in a preamble to each slonik script

There needs to be one 'admin conninfo' path to each Slony-I node

These are used sporadically, just to do configuration, so they could be temporary (e.g. - SSH tunnels)

Paths Between Nodes

The store path command stores the paths used so the slon for node X knows how to access the database for node Y

If there are n nodes, there could be as many as n(n-1) paths in sl_path; no less than 2n

Multiple sites with complex firewall policies may mean nonuniform communications paths

But there still must be some form of 2-way communications between sites

Complex Path Scenario

db1

db2

db3

network #1

db4

db5

db6

network #2

Tightly interconnected

in network #1

Tightly interconnected

in network #2

But between the networks, only

db2 and db4 talk to one another

Security Considerations

slonik and slon must connect as PostgreSQL superuser because they do DB alterations

In practice, events propagate so any action could come from anywhere

Connection issues and mechanisms are the same as for any software using libpq

Slony-I doesn't mess around with users or roles; you manage security your way

.pgpass for Password Storage

Use .pgpass for storing passwords

Removes passwords from command lines

Removes passwords from environment variables

Removes passwords from sl_path

All of those are vulnerable to capture

SSH Connections

Consider using ssh connections across untrusted networks

Slony-I opens connections infrequently – costs are amortized over much usage

Presently, PostgreSQL has decent support for server certificates but client certificates not usable for authentication

Use of client certificates for authentication would provide a more “opaque” token than
.pgpass – future PostgreSQL enhancement

Slony-I User

Use of “slony” PG user to run Slony-I processes is highly recommended

Makes replication connections highly identifiable

Makes it easy to lock out all but the slony user when running COPY_SET

Separating maintenance roles to multiple users (molly, dumpy, slony) has proven useful.

Security – Log Shipping

Conventional nodes require 2-way communications between participating servers

Log shipping allows serializing updates to files

Transmit (1-way!) via FTP, scp, rsync, DVD-ROM, USB Key, RFC 1149, 2549

Sometimes Slony-I isn't the Answer

If you truly, honest to goodness, need multimaster, watch for Slony-II...

If you need DDL to be handled automagically, look at PG 8.0 PITR

If you have a loosely run environment, Slony-I will not go well

No answer yet for async multimaster!

More Cases Not to Use Slony-I

Slony-I needs to define a node (and spawn a slon + connections) for each database – replicating 80 databases on one cluster may not turn out

If you have 300 sequences, all 300 are propagated with each sync – this may not turn out well

Summary

What is Slony-I and what does it do?

What are the components of Slony-I?

What are some security issues surrounding the use of Slony-I?

2005-06-22T17:24:08