Database Replication with Slony-I
We first create the database we will replicate. This database contains a single table and sequence. Let's create a user contactuser, the contactdb database and activate the plpgsql programming language to this newly created PostgreSQL database by proceeding with the following commands:
% su - postgres % createuser --pwprompt contactuser Enter password for user "contactuser": (specify a password) Enter it again: Shall the new user be allowed to create databases? (y/ n) y Shall the new user be allowed to create more new users? (y/ n) n % createdb -O contactuser contactdb % createlang -U postgres -h localhost plpgsql \ contactdb
Then, we create the sequence and the table in the database we will replicate and insert some information in the table:
% psql -U contactuser contactdb contactdb=> create sequence contact_seq start with 1; contactdb=> create table contact ( cid int4 primary key, name varchar(50), address varchar(255), phonenumber varchar(15) ); contactdb=> insert into contact (cid, name, address, phonenumber) values ((select nextval('contact_seq')), 'Joe', '1 Foo Street', '(592) 471-8271'); contactdb=> insert into contact (cid, name, address, phonenumber) values ((select nextval('contact_seq')), 'Robert', '4 Bar Roard', '(515) 821-3831'); contactdb=> \q
For the sake of simplicity, let's create a second database on the same system in which we will replicate the information from the contactdb database. Proceed with the following commands to create the database, add plpgsql programming language support and import the schema without any data from the contactdb database:
% su - postgres % createdb -O contactuser contactdb_slave % createlang -U postgres -h localhost plpgsql \ contactdb_slave % pg_dump -s -U postgres -h localhost contactdb | \ psql -U postgres -h localhost contactdb_slave
Once the databases are created, we are ready to create our database cluster containing a master and a single slave. Create the Slonik cluster_setup.sh script and execute it. Listing 1 shows the content of the cluster_setup.sh script.
Listing 1. cluster_setup.sh
#!/bin/sh CLUSTER=sql_cluster DB1=contactdb DB2=contactdb_slave H1=localhost H2=localhost U=postgres slonik <<_EOF_ cluster name = $CLUSTER; node 1 admin conninfo = 'dbname=$DB1 host=$H1 user=$U'; node 2 admin conninfo = 'dbname=$DB2 host=$H2 user=$U'; init cluster (id = 1, comment = 'Node 1'); create set (id = 1, origin = 1, comment = 'contact table'); set add table (set id = 1, origin = 1, id = 1, full qualified name = 'public.contact', comment = 'Table contact'); set add sequence (set id = 1, origin = 1, id = 2, full qualified name = 'public.contact_seq', comment = 'Sequence contact_seq'); store node (id = 2, comment = 'Node 2'); store path (server = 1, client = 2, conninfo = 'dbname=$DB1 host=$H1 user=$U'); store path (server = 2, client = 1, conninfo = 'dbname=$DB2 host=$H2 user=$U'); store listen (origin = 1, provider = 1, receiver = 2); store listen (origin = 2, provider = 2, receiver = 1);
The first slonik command (cluster name) of Listing 1 defines the namespace where all Slony-I-specific functions, procedures, tables and sequences are defined. In Slony-I, a node is a collection of a database and a slon process, and a cluster is a collection of nodes, connected using paths between each other. Then, the connection information for node 1 and 2 is specified, and the first node is initialized (init cluster). Once completed, the script creates a new set to replicate, which is essentially a collection containing the public.contact table and the public.contact_seq sequence. After the creation of the set, the script adds the contact table to it and the contact_seq sequence. The store node command is used to initialize the second node (id = 2) and add it to the cluster (sql_cluster). Once completed, the scripts define how the replication system of node 2 connects to node 1 and how node 1 connects to node 2. Finally, the script tells both nodes to listen for events (store listen) for every other node in the system.
Once the script has been executed, start the slon replication processes. A slon process is needed on the master and slave nodes. For our example, we start the two required processes on the same system. The slon processes must always be running in order for the replication to take place. If for some reason they must be stopped, simply restarting allows them to continue where they left off. To start the replication engines, proceed with the following commands:
% slon sql_cluster "dbname=contactdb user=postgres" & % slon sql_cluster "dbname=contactdb_slave user=postgres" &
Next, we need to subscribe to the newly created set. Subscribing to the set causes the second node, the subscriber, to start replicating the information of the contact table and contact_seq sequence from the first node. Listing 2 shows the content of the subscription script.
Webinar: 8 Signs You’re Beyond Cron
11am CDT, April 29th
Join Linux Journal and Pat Cameron, Director of Automation Technology at HelpSystems, as they discuss the eight primary advantages of moving beyond cron job scheduling. In this webinar, you’ll learn about integrating cron with an enterprise scheduler.Join us!
- Return of the Mac
- DevOps: Better Than the Sum of Its Parts
- Drupageddon: SQL Injection, Database Abstraction and Hundreds of Thousands of Web Sites
- Not So Dynamic Updates
- Play for Me, Jarvis
- Users, Permissions and Multitenant Sites
- Non-Linux FOSS: .NET?
- Designing Foils with XFLR5
- diff -u: What's New in Kernel Development