Slony

Ian Burrell

ianburrell@gmail.com

Slony

  • http://slony.info

  • Replication for PostgreSQL

  • Master-slave

  • Asynchronous

  • Extension-based

Usage

  • Rentrak Corp

  • Video OnDemand reporting system

    • 4 billion transactions
    • 8 million/day

  • Master

    • 1.5 TB
    • Imports and website

  • 2 slaves

    • 700 GB
    • Reporting

Why Slony?

  • Disaster recovery

  • Read-only copies

  • Remote sites

  • Upgrades

Why Not?

  • Complicated

  • Need writes on copies

  • Preserve transactions

  • Disconnected operation

  • No schema replication

Components

  • Database extensions

    • C triggers
    • PL/pgSQL management functions

  • slon daemon

  • slonik configuration processor

Basic Operation

  • Triggers save changes in log table

  • slon polls for events

  • slon on master periodically generates SYNC events

  • Slave slon copies changes from master

  • slonik runs management functions and inserts events

Terms

  • Cluster

  • Node

  • Replication set

  • Origin

  • Provider

  • Subscriber

Topology

  • Only master is writable

  • Slaves are read-only

  • Table sets can have different topology

  • Slave can forward to other slaves

Slonik

cluster name = example;

node 1 admin conninfo = 'dbname=master host=masterdb user=slony';
node 2 admin conninfo = 'dbname=slave host=slavedb user=slony';

  • Preamble goes in every slonik script

  • Connection info from management machine

Init Cluster

init cluster (id = 1, comment = 'Master Node');

create set (id = 1, origin = 1, comment = 'All pgbench tables');

Tables & Sequences

set add table (set id = 1, origin = 1, id = 1, 
    fully qualified name = 'public.accounts', 
    comment = 'accounts table');
set add sequence (set id = 1, origin = 1, id = 2,
    fully qualified name = 'public.account_no_seq',
    comment = 'accounts seqeunce');
  • Tables must have primary key

Nodes & Paths

store node (id = 2, comment = 'Slave node');

store path (server = 1, client = 2, 
    conninfo='dbname=master host=masterdb user=slony');
store path (server = 2, client = 1, 
    conninfo='dbname=slave host=slavedb user=slony');

store listen (origin = 1, provider = 1, receiver = 2);
store listen (origin = 2, provider = 2, receiver = 1);

Slon Daemon

slon example "dbname=master user=slony host=masterdb"
slon example "dbname=slave user=slony host=slavedb"

  • Best practice to run slon on master machine

  • Need watchdog scripts

Subscribe

subscribe set (id = 1, provider = 1, receiver = 2, forward = no);

  • Truncate on slaves

  • Copy table

  • Create indexes

  • Other changes must wait to finish

Adding Tables

create set (id = 99, origin = 1, comment = 'Temporary set');
set add table (set id = 99, origin = 1, id = 2, 
    fully qualified name = 'public.branches', 
    comment = 'branches table');

subscribe set (id = 99, provider = 1, receiver = 2, forward = no);
wait for event (origin = 2, confirmed = 1);
sync (id = 1);
wait for event (origin = 1, confirmed = 2);
merge set (id = 1, add id = 99, origin = 1);

  • Can’t add tables to subscribed set

  • Create new set, subscribe to same nodes, and merge together

Schema Changes

execute script (set id = 1, event node = 1, 
    filename='add_column.sql');

  • Required when change to structure of table

  • Serialized with transactions

  • Disables triggers on slaves

Switchover

lock set (id = 1, origin = 1);
wait for event (origin = 1, confirmed = 2);
move set (id = 1, old origin = 1, new origin = 2);
wait for event (origin = 1, confirmed = 2);

  • Slave becomes master

  • Master becomes slave

  • Must be connected

Failover

failover (id = 1, backup node = 2);

  • Only use when master is unavailable

  • Master must be rebuilt

Problems

  • Vacuum

  • Locking

  • Schema installs

Vacuum

  • Long-running transactions

  • Tables with growth problems

    • pg_listener
    • sl_log_1

  • Better in Slony 1.2

Locking

  • Exclusive locks

    • execute script: on set
    • set add table: on table
    • move set: on old and new origin
    • lock set: on origin
    • subscribe set: on subscriber

Schema Install

  • Plan your changes

  • Test your scripts

  • Take downtime

  • Move set trick

Alternatives

  • PITR Replication

    • WAL shipping
    • Hot standby

  • PGCluster

    • Multi-master synchronous
    • Query replication

  • Postgres-R

    • Multi-master synchronous
    • Spread group communication library
    • Modified old version