Replication & Upgrades - PostgreSQL 9 Administration - page 322

These last three steps are where the actual execution takes place. After all of these have
executed, the databases are fully replicated with slon daemons running.
F 01_create_init.sh creates the bash scripts for later use. You can edit $SLON_
TMP / my_replica.sh and add/remove tables, if needed. So selective replication is
a simple matter of editing those files.
F create_struct.sh performs a dump from Master and a restore to the slave of the
database schema. This dumps the whole database schema.
F 02_exec_init.sh executes the scripts. This copies the data, so may take a while.
Maintaining replication
Slony doesn't replicate the following items:
F Table definition changes (DDL commands)
F Changes to users and roles
F Changes to large objects (BLOBS)
F Changes made by the TRUNCATE command (may change in future)
So, once you've set up Slony, you'll need to make any further changes to the replicated tables
onto each of the nodes in the configuration. Slony provides a tool named slonik which allows
you to perform EXECUTE SCRIPT , which executes a change script in a single transaction on
each node.
You can create new non-unique indexes of any kind onto tables onto the subscriber without a
problem. This can be used to tune the Standby for different workloads. Make sure you don't
put unique indexes on the subscriber side that don't exist on the Master, because this can
cause replication failures.
Non-replicated tables on the Standby can still be written to, allowing post-processing of
incoming changes or creation of materialized view-style tables.
How it works...
When data changes are made, they are placed in log tables named sl_log_1 and sl_
log_2 using database triggers on the origin. Slony will flip/flop between those two tables, so
it can perform truncate data on the non-current log table. That avoids the need for deletion
and vacuuming of the log tables. Triggers are also placed on replicated tables on the Standby
to prevent writes to tables.
For each node in the cluster, there will be one slon process which sends data and
configuration changes from the Master to the Standby. The slon daemon reads the log table
on the Origin/Provider, and then executes the SQL onto the Subscriber (Standby). Changes are
applied in batches, with batch size as a tunable parameter.