Replication for PostgreSQL
Master-slave
Asynchronous
Extension-based
Video OnDemand reporting system
Master
2 slaves
Disaster recovery
Read-only copies
Remote sites
Upgrades
Complicated
Need writes on copies
Preserve transactions
Disconnected operation
No schema replication
Database extensions
slon daemon
slonik configuration processor
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
Cluster
Node
Replication set
Origin
Provider
Subscriber
Only master is writable
Slaves are read-only
Table sets can have different topology
Slave can forward to other slaves
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 (id = 1, comment = 'Master Node');
create set (id = 1, origin = 1, comment = 'All pgbench tables');
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');
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 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 set (id = 1, provider = 1, receiver = 2, forward = no);
Truncate on slaves
Copy table
Create indexes
Other changes must wait to finish
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
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
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 (id = 1, backup node = 2);
Only use when master is unavailable
Master must be rebuilt
Vacuum
Locking
Schema installs
Long-running transactions
Tables with growth problems
Better in Slony 1.2
Exclusive locks
Plan your changes
Test your scripts
Take downtime
Move set trick