Sample Replication: RT/3

Christopher Browne

Afilias Canada

for OSCON 2005

Sample Application: RT/3

RT/3 is a popular open source ticket-based request manager

Used to manage bug reports for Perl http://rt.perl.org

As of version 3, it supports PostgreSQL

Database Schema: 19 Tables

All these tables have well-defined primary

keys, as needed by Slony-I

Database Schema: 18 Sequences

Network Layout

dba1

DBA workstation

Software Buildout

Need PostgreSQL with Slony-I libraries on each database server

Need slon, libpq on the application server

Need slonik, libpq on the DBA workstation

Dump Schema

Use “pg_dump -s” to get the set of tables and sequences to be replicated

Slony-I doesn't handle DDL changes automatically

Slonik preamble

To be used in each Slonik script:

cluster name = rt; # indicates namespace _rt

node 1 admin conninfo = 'host=rtdb1 db=rt user=slony';

node 2 admin conninfo = 'host=rtdb2 db=rt user=slony';

node 3 admin conninfo = 'host=bkdb1 db=rt user=slony';

These statements indicate how to connect from the “admin” workstation to each db

ID Selection

If there are just 2 nodes, it is easy to deal with “node 1” and “node 2”

If there are more nodes, it is not intuitive to assign numbers via “most lately created node within this cluster” numbering scheme

Design a numbering scheme

In Slony-I 1.1, one may use “define” in slonik to assign mnemonic names

ID Selection - II

Nodes, Sets, Tables, Sequences are all 32 bit values, giving room to use all sorts of numbering systems

Node numbering...
1000 to 1999
1100 series – NYC, 1200 – LA, ...
Zip codes...

Set numbering...
Work Ticket #32121 leads to set 32121

Initialize Nodes

include <preamble.slonik>;

init cluster (id=1, comment='RT master');

store node (id=2,comment='RT Reports');

store node (id=3,comment='RT Backup');

This sets up the Slony-I _rt schema in each of these databases.

Note that initialization of the first node is a little special...

Store Paths

Include <preamble.slonik>;

store path (client=2, server=1 conninfo = 'host=rtdb1 db=rt user=slony');

store path (client=3, server=1 conninfo = 'host=rtdb1 db=rt user=slony');

store path (client=1, server=2 conninfo = 'host=rtdb2 db=rt user=slony');

store path (client=3, server=2 conninfo = 'host=rtdb2 db=rt user=slony');

store path (client=1, server=3 conninfo = 'host=bkdb1 db=rt user=slony');

store path (client=2, server=3 conninfo = 'host=bkdb1 db=rt user=slony');

Here we have completely connected nodes – unlikely in complex networks

Before the slons start

Look at the configuration in sl_path, sl_listen

Each node only has partial configuration

Start the Slons

This is the point at which to start slon processes on the application server:

slon -d2 rt 'host=rtdb1 db=rt user=slony' &

slon -d2 rt 'host=rtdb2 db=rt user=slony' &

slon -d2 rt 'host=bkdb1 db=rt user=slony' &

Event propagation broadcasts the rest of the configuration around

Replication Set

include <preamble.slonik>;

create set (id=1,origin=1,comment='RT data');

Add Tables to Set #1

include <preamble.slonik>;

set add table (set id=1,origin=1,id=1,fully qualified name='public.acl', comment='Access controls');

set add table(set id=1,origin=1,id=2, fully qualified name='public.scripactions', comment='Actions');

... and so forth for the 19 tables ...

Add Sequences

include <preamble.slonik>

set add sequence(set id=1,origin=1,id=1,fully qualified name='public.acl_id_seq', comment='ID on ACL table');

set add sequence(set id=1,origin=1,id=2,fully qualified name='public.attachments_id_seq', comment='ID on Attachments table');

... and about 16 more ...

Notice sequences have separate ID numbering from tables – separate namespace

Start Replication Subscription

include <preamble.slonik>

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

This runs a COPY_SET event, copying data “en masse”

Once complete, SYNC events from node 1 tell node 2 what updates to draw in from then on

Subscription – Node #3

include <preamble.slonik>;

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

Note that node 1 can only feed one COPY_SET event at a time...

We're Done Now...

Assuming everything is running OK, replication setup is now complete. You can walk away.

Replication configuration is persistent – resides in each DB in schema _rt

If DB servers are shut down, restarting replication merely requires restarting slon processes

No need to run slonik again until there is a change in replication configuration

If we need to change things...

... We do not need to start from scratch.

[Brainstorm: What changes would mandate starting over???]

Adding nodes, adding tables, reconfiguring subscriber network, can all be done incrementally

Oops – I Forgot A Table

Here's the recipe:

create set – to create a new set

set add table, set add sequence – to populate the set

subscribe set – to get the set replicated everywhere

merge set – once identically replicated, the two sets can be merged

Oops – Wrong Table

If data shouldn't be replicated...

set drop table

set drop sequence

Move Set Origin for Maintenance

include <preamble.slonik>

lock set (id = 1);

move set (id=1, old origin=1, new origin=3);

What does this actually do?

Locks tables on the origin

Changes triggers on the new origin

Changes triggers on the old origin – now it's a subscriber

Kaboom!!! Fail Over

rtdb1 catches on fire

The database is gone

move set is not an option because that needs for rtdb1 to be “alive”

This is the “use case” for fail over.

What Failover Does

New origin takes over; waits to get latest updates from other nodes

Subscriptions automatically reshaped to point towards the new origin

Failed node has no action taken against it; it is merely “shunned”

Failed node DEAD – it cannot return until rebuilt from scratch

Altperl Admin Scripts

Writing slonik takes a bit of getting used to...

tools/altperl contains Perl scripts that take slon_tools.conf, listing some configuration, and generate slonik code for you

slonik_init_cluster, slonik_create_set, slonik_subscribe_set

slonik_drop_node, slonik_drop_set, slonik_execute_script, slonik_failover, slonik_merge_sets, slonik_move_set

To run slon daemons with “watchdogs”

slon_start, slon_kill, slon_watchdog

Modifying data flow - Cascade

Switching data flow around for node 3

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

1

2

3

1

2

3

DDL Changes

Slony-I does not automatically make DDL changes

It is not overly friendly for this...

Create an SQL DDL script

Slonik command:
execute script (set id=1, filename='/tmp/upgrade-ddl.sql');

What Execute Script Does

On each node...

Request a lock on each table in the set

Restores each table's rules/triggers

Run the SQL script

Return each table to “replicated mode”

This raises the “ugly specter” of table locking – locks affect all nodes

Ideally, run execute script during an application outage

Locking

Slony-I actions often don't need intrusive locks

MVCC means that PostgreSQL activities typically do not need visible locks; unfortunately, schema changes require locks

Some locks transient - set add table

For execute script, move set, locks are intrusive, attaching to all tables

Multiple Sets

Reasons to have several replication sets:

Split a very large replica into pieces – alleviates performance issues, cut risks

If you have a DDL change affecting just one table, put the table in its own set to alleviate lock contention

STORE TRIGGER

Slony-I deactivates triggers on the replicated tables on subscriber nodes

This is desirable for foreign key triggers – they are already verified on the origin

Triggers are modified on subscribers to point to the “primary key” index

If you do want the trigger, STORE TRIGGER restores it

Primary Keys

Slony-I requires a primary key on each replicated table

If there are candidate primary keys, you may specify one in SET ADD TABLE

If there is no candidate for primary key, Slony-I can define one for you via TABLE ADD KEY – new application failure mode!

Summing Up

We have taken a sample application and looked at how to apply Slony-I to it

It is a simple case, but this simple case has been seen in “real life”

We have looked at a number of extensions to the initially simplistic configuration

You CAN take the configuration further

2005-06-22T17:24:12