author: Ian Burrell
company: ianburrell@gmail.com
title: Slony

Slony
=====

Slony
-----

* <http://slony.info>

* Replication for PostgreSQL

* Master-slave

* Asynchronous

* Extension-based


Usage
-----

* [Rentrak Corp](http://www.rentrak.com)

* 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](http://pgpitrha.projects.postgresql.org/)
  - WAL shipping
  - Hot standby

* [PGCluster](http://www.pgcluster.org/)
  - Multi-master synchronous
  - Query replication

* [Postgres-R](http://gborg.postgresql.org/project/pgreplication/)
  - Multi-master synchronous
  - Spread group communication library
  - Modified old version