come from :http://bbs.chinaunix.net/thread-955564-1-1.html

Reference documents
http://bbs.chinaunix.net/viewthr ... page%3D1#pid6997819
http://topic.csdn.net/t/20050617/12/4089010.html
postgresql Data synchronization
slony1 Is based on postgresql Replication technology based on asynchronous notification mechanism , Its synchronization speed is very fast .  Using this replication technology for backup , ha-ha , Except the configuration is a little bit more complicated , Very easy to use !   
1. Overall demand
1.1. Current situation
With the increasing complexity of software system , Distributed deployment has become a popular way of software deployment . For the structure of the system ,
Program and data are the two main elements supporting the system . There are many good solutions for distributed deployment of programs , ad locum
Let me talk about distributed deployment of data . The distributed deployment of data is actually the distributed deployment of database .
1.2. System environment
ad locum , I'll describe the deployment process in detail in the following environment .
Master database server (master)
RHEL3
ip 192.168.0.200
mask 255.255.255.0
From the database server (slave)
RHEL3
ip 192.168.30.201
mask 255.255.255.0
We need to make sure that the two machines are interconnected .
Please go to the specified destination to download the specified package :
db postgresql 8.2.4.tar.gz(http://www.postgresql.org/download/)
slony1 slony1-1.2.6.tar(http://www.postgresql.org/download/)
The above URL is an entry address , Please select the appropriate and correct source package .
1.3. System installation
1.3.1 Master database server
1.3.1.1 mount this database
Groupadd postgres
Useradd postgres –g postgres –d /home/postgres
decompression , command tar -xvzf postgresql 8.2.4.tar.gz
Enter the corresponding postgresql-8.2.4 Catalog , command :cd postgresql-8.2.4
./configure  --prefix=/usr/local/pgsql –localstatedir=/home/postgres/data
gmake, command : gmake
Be careful , yes gmake
install , command gmake install
by postgres The environment parameters added by users are as follows :
Vi /home/postgres/.bash_profile
PGLIB=/usr/local/pgsql/lib
PGDATA=/test/spescso/data
PATH=$PATH:/usr/local/pgsql/bin
MANPATH=$MANPATH:/usr/local/pgsql/man
export PGLIB PGDATA PATH MANPATH
chown postgres.postgres /usr/local/pgsql –R
Su – postgres
Mkdir data
Use postgres establish Database cluster
/usr/local/pgsql/bin/initdb -E UTF-8 /home/postgres/data/
/usr/local/pgsql/bin/createuser -a -d ssuser
/usr/local/pgsql/bin/createlang plpgsql template1
Use postgres establish log Catalog
mkdir /test/spescso/data/log
modify /test/spescso/data/postgresql.conf file
Main configuration log file
log_destination = 'stderr'
redirect_stderr = true
log_directory = '/test/spescso/data/log/'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
modify /test/spescso/data/pg_hba.conf, Mainly to solve the problem of mutual authentication between two computers , Otherwise, we can't visit each other
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.0.0/24 trust
# IPv6 local connections:
host all all ::1/128 trust
If you can't understand the above , Please read... Carefully postgresql Database security authentication document .
Use postgres User background start postmaser Database master process
/usr/local/pgsql/bin/postmaster -i -D /test/spescso/data/ -p 5432 &
1.3.1.2 install slony1 Data synchronization tools ( Both master and slave need to be installed )
decompression , command tar -xvjf slony1-1.2.6.tar
Be careful , Use -j Parameters , There's a bit of inconsistency in the site's compressed packages .
Enter the corresponding slony1-1.2.6 Catalog , command :cd slony1-1.2.6
./configure  --with-pgsourcetree=<postgresql   Source code directory >
gmake, command : gmake
Be careful , yes gmake
install , command gmake install
1.3.2 From the database server
The installation mode is consistent with that of the main database server .
1.3.3 Building databases and tables
In the following Set up the main database and data table on the main database server test For example , For other databases and data tables, please refer to build .
su -c "/usr/local/pgsql/bin/createdb -U ssuser -E UTF-8 test -p 5432" -l postgres
su -c "/usr/local/pgsql/bin/psql -f /home/hzh/share/sql.txt -p 5432 -dtest -Ussuser" -l postgres
( Be careful ,sql.txt Is the command to create a data table , Please write your own .sql.txt The best thing to do is UTF-8 Format , Especially when there are Chinese characters )
example :sql.txt
CREATE TABLE tb_depart(
Id int primary key,
Name char();
Set up... In turn on the main database machine testslave1,testslave2.
On the slave database machine 5431 Port establishment testslave3
Guarantee testslave1/testslave2/testsalve3 Build and test The same table in tb_depart/tb_manager/tb_user……..
1.4 Configuration synchronization
1.4.1. Host configuration
To write configmaster sheul Script files , Set its executable properties , command chmod a+x+w+r configmaster, The contents of the document are as follows :
#!/bin/bash
basebin=/usr/local/pgsql/bin
# Cluster name
CLUSTER=slony_test1
# The name of the database participating in the synchronization ,master db yes test, The other three are slave
DBSERVER=test
DBSLAVE1=testslave1
DBSLAVE2=testslave2
DBSLAVE3=testslave3
# The address of the machine participating in the synchronization
HOSTSERVER=192.168.0.200
HOSTSLAVE1=192.168.0.200
HOSTSLAVE2=192.168.0.200
HOSTSLAVE3=192.168.0.201
# Database user name participating in synchronization
DBSERVER_USER=ssuser
DBSLAVE1_USER=ssuser
DBSLAVE2_USER=ssuser
DBSLAVE3_USER=ssuser
# Synchronous publishing configuration , Here is the order slonik Parameters of
$basebin/slonik<<_EOF_
cluster name=$CLUSTER;
# Define replication nodes
node 1 admin conninfo='dbname=$DBSERVER host=$HOSTSERVER user=$DBSERVER_USER port=5432';
node 2 admin conninfo='dbname=$DBSLAVE1 host=$HOSTSLAVE1 user=$DBSLAVE1_USER port=5432';
node 3 admin conninfo='dbname=$DBSLAVE2 host=$HOSTSLAVE2 user=$DBSLAVE2_USER port=5432';
node 4 admin conninfo='dbname=$DBSLAVE3 host=$HOSTSLAVE3 user=$DBSLAVE3_USER port=5431';
# Initialize cluster ,id from 1 Start
init cluster ( id=1, comment='Node 1' );
# Set the tables that participate in synchronization
# First create a replica set ,id Also from the 1 Start
# Add tables to your own replica set , One for each table that needs to be copied set command
#id from 1 Start , Step by step , Step by step to 1;
#fully qualified name It's the full name of the table : Schema name . Table name
# The replica set here id Need to be the same as the replica set created earlier id Agreement
create set ( id=1, origin=1, comment='All test tables' );
set add table ( set id=1, origin=1,id=1, fully qualified name='public.tb_depart',comment='Table tb_depart' );
set add table ( set id=1, origin=1,id=2, fully qualified name='public.tb_user',comment='Table tb_user' );
set add table ( set id=1, origin=1,id=3, fully qualified name='public.tb_manager',comment='Table tb_manager' );
# If a table has no primary key , But there's a single key , Then you can use it key keyword
# Specify it as the copy key word , Like the one below key Parameters
#set add table ( set id = 1, origin = 1,id = 4, fully qualified name = 'public.history',key = "column",comment = 'Table history' );
# For tables without unique Columns , It needs to be dealt with like this , Put this sentence in create set In front of
#table add key (node id = 1, fully qualified name = 'public.history');
# Set the result set like this
#set add table (set id=1, origin=1, id=4, fully qualified name = 'public.history', comment='history table', key = serial);
# Set up storage nodes
store node ( id=2, comment='Node 2' );
store node ( id=3, comment='Node 3' );
store node ( id=4, comment='Node 4' );
# Set storage path
store path ( server=1, client=2,conninfo='dbname=$DBSERVER host=$HOSTSERVER user=$DBSERVER_USER port=5432');
store path ( server=2, client=1,conninfo='dbname=$DBSLAVE1 host=$HOSTSLAVE1 user=$DBSLAVE1_USER port=5432');
store path ( server=1, client=3,conninfo='dbname=$DBSERVER host=$HOSTSERVER user=$DBSERVER_USER port=5432');
store path ( server=3, client=1,conninfo='dbname=$DBSLAVE2 host=$HOSTSLAVE2 user=$DBSLAVE2_USER port=5432');
store path ( server=1, client=4,conninfo='dbname=$DBSERVER host=$HOSTSERVER user=$DBSERVER_USER port=5432');
store path ( server=4, client=1,conninfo='dbname=$DBSLAVE3 host=$HOSTSLAVE3 user=$DBSLAVE3_USER port=5431');
# Set listening events and subscription direction , Role in replication , The master node is the original provider , The slave node is the recipient
store listen ( origin=1, provider=1, receiver=2 );
store listen ( origin=2, provider=2, receiver=1 );
store listen ( origin=1, provider=1, receiver=3 );
store listen ( origin=3, provider=3, receiver=1 );
store listen ( origin=1, provider=1, receiveR=4 );
store listen ( origin=4, provider=4, receiver=1 );
_EOF_
1.4.2. Submit data sets
To write commitdata shell Script files , Give executable permission , The contents are as follows :
#!/bin/bash
basebin=/usr/local/pgsql/bin
CLUSTER=slony_test1
DBSERVER=test
DBSLAVE1=testslave1
DBSLAVE2=testslave2
DBSLAVE3=testslave3
HOSTSERVER=192.168.0.200
HOSTSLAVE1=192.168.0.200
HOSTSLAVE2=192.168.0.200
HOSTSLAVE3=192.168.0.201
DBSERVER_USER=ssuser
DBSLAVE1_USER=ssuser
DBSLAVE2_USER=ssuser
DBSLAVE3_USER=ssuser
$basebin/slonik<<_EOF_
cluster name=$CLUSTER;
# Provide connection parameters
node 1 admin conninfo='dbname=$DBSERVER host=$HOSTSERVER user=$DBSERVER_USER port=5432';
node 2 admin conninfo='dbname=$DBSLAVE1 host=$HOSTSLAVE1 user=$DBSLAVE1_USER port=5432';
node 3 admin conninfo='dbname=$DBSLAVE2 host=$HOSTSLAVE2 user=$DBSLAVE2_USER port=5432';
node 4 admin conninfo='dbname=$DBSLAVE3 host=$HOSTSLAVE3 user=$DBSLAVE3_USER port=5431';
# Commit subscription replica set
subscribe set ( id=1, provider=1, receiver=2, forward=no);
subscribe set ( id=1, provider=1, receiver=3, forward=no);
subscribe set ( id=1, provider=1, receiver=4, forward=no);
_EOF_
1.4.3. Configure the synchronization process
Execute the configuration command on the host ./configmaster
On the host slon Background processes , Start primary database replication , command
/usr/local/pgsql/bin/slon slony_test1 "dbname=test host=192.168.0.200 user=ssuser port=5432" &
On the host slon Background processes , Start the first copy from the database , command
/usr/local/pgsql/bin/slon slony_test1 "dbname=testslave1 host=192.168.0.200 user=ssuser port=5432" &
On the host slon Background processes , Start the second copy from the database , command
/usr/local/pgsql/bin/slon slony_test1 "dbname=testslave2 host=192.168.0.200 user=ssuser port=5432" &
On the slave slon Background processes , Start a third copy from the database , command
/usr/local/pgsql/bin/slon slony_test1 "dbname=testslave3 host=192.168.0.201 user=ssuser port=5431" &
Execute the submit command on the host ./commitdata

POSTGRESQL Sync ——SLONY-I More articles on configuration

  1. PostgreSQL One master, many followers ( Multiple copies , Strong synchronization ) A concise manual - To configure 、 Pressure measurement 、 monitor 、 Switch 、 Prevention of cerebral fissure 、 Repair 、0 The loss of - Collection level

    Reference source : https://github.com/digoal/blog/blob/master/201803/20180326_01.md#postgresql- Simple manual of strong synchronization for one master, multiple slaves and multiple copies --- with ...

  2. PostgreSQL Synchronous replication

    Abstract : PostgreSQL Synchronous replication One . initialization master node 1. install PostgreSQL 2. initialization db initdb -D /data/pg940_data Two . To configure master node ...

  3. centos Next postgresql Installation and configuration [ turn ]

    This article excerpts from :http://blog.chinaunix.net/uid-24846094-id-78490.html One . install ( With root Identity ) 1. Check out the latest postgresql Of yum Configure from ht ...

  4. Use ZooKeeper Synchronous cluster configuration

    use ZooKeeper Synchronous cluster configuration , When all node configurations need to be modified , Update the configuration to  ZooKeeper A node of , Cause the data of this node to change , Local... On all other nodes that need to be configured synchronously Watcher You'll find out immediately ...

  5. PostgreSQL Synchronization scheme

    Windows Next Postgre SQL Database access Slony-I Realize the database double machine synchronous backup - Database, other synthesis - Red and black alliance postgresql Synchronous stream replication Hot Standby - CSDN Blog   send ...

  6. PostgreSQL recovery.conf Restore configuration

    PostgreSQL recovery.conf Restore configuration This chapter describes recovery.conf The settings available in the file . They're only for the recovery period . For any subsequent recovery you want to perform , They have to be reset . Once the recovery has begun , ...

  7. rsync Configuration of data synchronization tool

    rsync Configuration of data synchronization tool 1. rsync Introduce 1.1. What is? rsync rsync It's an open source, fast , Multifunctional , It is an excellent tool to realize full and incremental local or remote data synchronous backup .Rsync The software is suitable for unix ...

  8. Linux:DNS Lord 、 from 、 Cache server configuration 、DNS Synchronous encryption TSIG To configure 、DNS Separate resolution configuration

    DNS Main server configuration ( Forward analysis . Reverse DNS ) Forward analysis : Find the corresponding... According to the host name IP Address . When a user accesses a domain name ( Don't consider hosts Documents and other factors ), Under normal circumstances, the designated DNS The host sends a recursive query request for reverse parsing : according to IP Address ...

  9. Use Settings sync Sync VS Code To configure

    Use Settings sync Sync VS Code To configure Because it's going to be used on multiple computers VSCode, Want to simply manage VSCode To configure , You don't have to manually configure one by one every time , Keep multiple development environments in sync , So using Settings s ...

Random recommendation

  1. [lua] Android ndk How to compile lua library

    here lua The library is the standard lua library , It doesn't contain tolua, It doesn't contain cocos2dx Various lua Expand , It's clean lua. Reference resources : http://stackoverflow.com/questions/1229965 ...

  2. C# Yes Access General class of database operation

    ( Reprinted from bloggers Jerry It's simple ) //Access database -C# Operation class Code using System;using System.Collections.Generic;using System.Linq ...

  3. oracle Commonly used sql

    -- stay sql Double quotes are used when there are only aliases in select employee_id,last_name,12*salary as yearSalary from employees; select last_ ...

  4. JS Realize simple picture switching effect

    Use pictures to click and switch effects <!doctype html> <html lang="en"> <head> <meta charset=&q ...

  5. hdu 4681 string

    character string DP The question : Here are three strings a,b,c Find string d The length of . character string d Meet the requirements of : yes a and b The common subsequence of ,c It's its substring . Definition dp1[i][j] Express a Of the i Bit and b Of the j Same subsequence length before bit ( package ...

  6. Virtual reality on cardboard and... In code Cardboard

    Virtual Reality Technology Future perspective ? Google Cardboard Try and compare Please stamp the following words first   Poke me, poke me 2014 Year of Google I/O The conference , Always with Geek Self claimed Google Took out one called Cardboa ...

  7. Service Components summary + Binding theorem Service Three ways of implementation Messager + Binder + AIDL

    stay Android There are five types of processes in the process according to their priority , Prioritize from high to low : - Foreground process The process contains interface components that are interacting with the user , For example, a Activity - Visual process Although the components in the process do not interact with the user , But still ...

  8. JAVA Basics 5—— And String Related series (2)

    Difference point comparison String Use + Directly joining together This situation needs to be discussed in two ways : 1. It's all between certain string constants + When the number one was stitched together , Since the compiler can determine its specific value , So the compiler will splice these constants at compile time ...

  9. PDF to image

    http://www.verypdf.com/pdf2tif/pdf-to-image/help.htm http://www.softinterface.com/DL/DL_Alternate_Do ...

  10. Python Use pymysql Connect MySQL

    because MySQLdb The module doesn't support Python3.x, therefore Python3.x If you want to connect MySQL Need to install pymysql modular . pymysql Modules can be accessed through pip install . But if you are using p ...