Install and configure slony-I for PostgreSQL database Synchronization

 

Author: icyriver
Source: http://icyriver.net /? P = 177

 

 

As MySQL was acquired by Sun, many web developers and architects began to pay attention to PostgreSQL. Indeed, PostgreSQL, slony-I, PL/proxy, and pgbouncer can provide us with a complete enterprise-level database storage solution. Its Web architecture is shown in:

The installation and configuration of PostgreSQL and PL/Proxy have been introduced in the previous blog. The following is A Concise Guide to the installation and configuration of slony-I to synchronize the Primary and Secondary databases. I will introduce the installation and configuration of pgbouncer later.

1. Primary and secondary database machines

MASTER:
Hostname: m_db
Inet ADDR: 10.0.0.11
OS: Linux 2.6.9-42. elsmp
CPU: Intel (r) Xeon (r) CPU l5320 @ 1.86 GHz
Memtotal: 254772 KB
Pgsql: postgresql-8.3.0

SLAVE:
Hostname: s_db
Inet ADDR: 10.0.0.12
OS: Linux 2.6.9-42. elsmp
CPU: Intel (r) Xeon (r) CPU l5320 @ 1.86 GHz
Memtotal: 514440 KB
Pgsql: postgresql-8.3.0

# Install the postgresql-8.3.0 on m_db and s_db. for installation and configuration, see my previous blog to ensure that the superuser is Postgres and the database name is urt.

# Check whether the Super User ipvs on m_db and s_db can access the target machine.

# Execution on m_db and s_db respectively
Sudo-u Postgres/home/y/pgsql/bin/createlang plpgsql URT

# Create the same table accounts in the URT database of m_db and s_db respectively.

2. Install slony-I

# Install slony-I on m_db and s_db respectively
Tar xfj slony1-1.2.13.tar.bz2
CD slony1-1.2.13
./Configure-with-pgconfigdir =/home/y/pgsql/bin
Gmake all
Sudo gmake install

3. slony config

Create the urt_replica_init.sh file:
##############################
#! /Bin/sh

Slonik =/home/y/pgsql/bin/slonik
# Slonik Executable File Location

Cluster = URT
# Name of your cluster

Set_id = 1
# Your replica Set Name

Master = 1
# Master server ID

Host1 = m_db
# Source database IP address or host name

Dbname1 = URT
# Source database to be copied

Slony_user = Postgres
# Super User Name of the source database

Slave = 2
# Slave server ID

Host2 = s_db
# Destination database IP address or host name

Dbname2 = URT
# Target database to be copied

Pgbench_user = Postgres
# Target library Username

$ Slonik <_ EOF _

# Define the cluster name
Cluster name = $ cluster;

# Define a replication Node
Node $ master admin conninfo = 'dbname = $ dbname1 host = $ host1 user = $ slony_user ';
Node $ slave admin conninfo = 'dbname = $ dbname2 host = $ host2 user = $ pgbench_user ';

# Initialize the cluster and master node. The ID starts from 1. If there is only one cluster, it must be 1.
# You can write comments of your own in comment.
Init cluster (ID = $ master, comment = 'Primary node ');

# Below are slave nodes
Store node (ID = $ slave, comment = 'slave node ');

# Configuring the connection information between the master and slave nodes is to tell the slave server how to access the master server.
# The following are the connection parameters of the master node.
Store path (Server = $ master, client = $ slave,
Conninfo = 'dbname = $ dbname1 host = $ host1 user = $ slony_user ');

# The following are the connection parameters of the slave node.
Store path (Server = $ slave, client = $ master,
Conninfo = 'dbname = $ dbname2 host = $ host2 user = $ pgbench_user ');

# Set the role in replication. The master node is the original provider and the slave node is the receiver.
Store listen (origin = $ master, provider = 1, consumer ER = 2 );
Store listen (origin = $ slave, provider = 2, consumer ER = 1 );

# Create a replica set. The Id also starts from 1.
Create set (ID = $ set_id, origin = $ master, comment = 'all pgbench tables ');

# Add a table to your own replica set. Add a set command for each table to be copied. The ID starts from 1 and increments progressively, step by step to 1;
# Fully qualified name is the full name of the Table: schema name. Table Name
# The replica Set ID here must be consistent with the previous replica Set ID
Set Add Table (Set ID = $ set_id, origin = $ master,
Id = 1, fully qualified name = 'public. accounts ',
Comment = 'table accounts ');

_ EOF _
########################

# Run the following command on m_db or s_db:
./Urt_replica_init.sh

4. slony start

Create the master. Slon file:
########################
Cluster_name = "URT"
Conn_info = "dbname = URT host = m_db user = S"
########################

Create the slave. Slon file:
########################
Cluster_name = "URT"
Conn_info = "dbname = URT host = s_db user = S"
########################

# Execute on m_db
/Home/y/pgsql/bin/Slon-F master. Slon> master. log &

# Execute on s_db
/Home/y/pgsql/bin/Slon-F slave. Slon> slave. log &

5. slony subscribe

Create the urt_replica_subscribe.sh file:
########################
#! /Bin/sh

Slonik =/home/y/pgsql/bin/slonik
# Slonik Executable File Location

Cluster = URT
# Name of your cluster

Set_id = 1
# Your replica Set Name

Master = 1
# Master server ID

Host1 = m_db
# Source database IP address or host name

Dbname1 = URT
# Source database to be copied

Slony_user = Postgres
# Super User Name of the source database

Slave = 2
# Slave server ID

Host2 = s_db
# Destination database IP address or host name

Dbname2 = URT
# Target database to be copied

Pgbench_user = Postgres
# Target library Username

$ Slonik <_ EOF _

# Define the cluster name
Cluster name = $ cluster;

# Define a replication Node
Node $ master admin conninfo = 'dbname = $ dbname1 host = $ host1 user = $ slony_user ';
Node $ slave admin conninfo = 'dbname = $ dbname2 host = $ host2 user = $ pgbench_user ';

# Submit a replica set
Subscribe set (ID = $ set_id, provider = $ master, worker ER = $ slave, forward = No );
_ EOF _
########################

# Run the following command on m_db or s_db:
./Urt_replica_subscribe.sh

6. Test
Modify the accounts table in the URT data on m_db. The accounts table on s_db also changes.

It is the internal architecture of slony-I. We can see that slony-I uses many threads to transfer and distribute events and data.

 

 

 

References:
Http://www.slony.info/documentation/

Http://blog.163.com/super_lpc/blog/static/677778920084285550671/

Http://www.cnblogs.com/coffee_cn/articles/1075568.html