Slony-1 Documentation
[http://slony.info/ Slony-I Docs]
A Bit about Slony-1
Slony-1 is a database replication engine used for Postgres databases. There are others, but so far Slony-1 seems to be the leader. The JSOC will be running Slony-1 to handle remote DRMS replicas and possibly for other in-house tasks.
There are two different ways to implement Slony-1.
One is a traditional method, where each database to be replicated has Slony-1 fully installed and each cluster is designated with a node number. Each node communicates with the other nodes as an equal, though for data items, an "origin", or master, is defined. Running in this way requires each Slony-1 node to have database superuser privileges in order to talk to each other. If all nodes are in-house, this superuser access is less of a concern. For the JSOC, with nodes distributed worldwide, it would be more of a security risk.
The second Slony-1 implementation method is called "log shipping". Log shipping requires no external systems to have database superuser privileges. To use the log-shipping method, first decide if you wish to be a passive receiver or a log shipper. JSOC will be a log-shipper. To generate logs, you must have already installed Slony-1 fully, and designate one cluster a master node. Then you must create a slave node to receive the master's replications, as a regular slony node (above). The words master and slave are used here only to give an idea of which node is changing data. In Slony-1 parlance, all nodes are equal, but JSOC will designate a master and a slave. Now, when there is a traditional Slony-1 master node replicating to a slave node, the slave node can be asked to log every change that the master instructed. Those log files are a series of SQL commands that the slave was directed to make by the master. A specific "slon" demon is run to generate those logs. It will be necessary to customize one slon demon per remote DRMS/replica since each one will likely have different data needs.
If we direct a third database to carry out those logged SQL commands, it will, and in effect replicate the master's commanded changes. The log files can be shipped to other systems as desired. If a remote system (a "replica" or "remote DRMS") desires only to read (not write) these logs, they have only to run a basic script. The script, slony1_dump,sh (received from the master node upon request, and customized for each installation) installs two very basic tables and two functions in a new namespace, and then uploads the full data available for the tables desired. Further scripts are necessary to keep a steady flow of data going automatically between the master and the remote DRMS, but these are straightforward enough. If a remote replica/DRMS wishes to ship new changes back to the JSOC, they would have to install a traditional Slony-1 setup with a master and log-shipping slave node, and then only ship new data back to the JSOC (e.g. not something the JSOC is already sending as a log to the remote DRMS site).
In a traditional Slony-1 setup, changes are replicated across nodes very quickly, at least once per minute. In log-shipping, a new log file is generated at least once per minute. The size of the log file for a small database with no new activity is ~800K. A log file directing the insertion of 100,000 records into a table with five columns was 9 Mb. The speed of log shipping replication is determined by how often remote users download their logs, the amount of activity in the data tables in the cluster, and network traffic.
Difference Between a Slony-1 Replica and a Warm Standby Server
1. Scope: per-table vs whole database
2. Availability: available vs standby
Replication Lag Requirement
From Phil's notes:
1. Access to quicklook data:
- For Ops at LMSAL should be with lag of few minutes from real time. For NOAA, JSC, Air Force, etc., can be c. 5-10 minutes probably.
2. Clone for web access:
- This can have a lag of 10s of minutes, maybe hour or more. Except for quicklook images movies of certain series. I wonder if we prepare some data for general purpose wide access via web and move that to a web server that does not have the full database access. That could be done in parallel with a full access clone of drms that has a lag and is used for searches of archived data for export.
3. Remote replicas:
Locations of Remote Replicas
0. LM
1. MSSL
2. Germany
3. SAO
4. India
Test Tasks
1. Log shipping
2. What happens when DB server goes down and when DB server reboots
3. How to add tables to replication set while slon daemon is running?
3. Replica DB maintenance? e.g., rebuild index, vacuum, etc.
4. How to copy table schema and role information
5. Is there a way to subscribe to part of a series?