Postgresql+slony-i installation Configuration Master-Slave

label:

slon software:
slony1-1.2.6

http://slony.info/downloads/1.2/source/

postgresql:

http://www.postgresql.org/download/
http://www.postgresql.org/ftp/source/v8.1.23/

 

First, postgresql installation

 

Method 1.rpm package install postfresql:
Required packages:
postgresql92-9.2.10-1PGDG.rhel5.i386.rpm
postgresql92-server-9.2.10-1PGDG.rhel5.i386.rpm
postgresql92-contrib-9.2.10-1PGDG.rhel5.i386.rpm
postgresql92-devel-9.2.10-1PGDG.rhel5.i386.rpm
postgresql92-libs-9.2.10-1PGDG.rhel5.i386.rpm
uuid-1.5.1-3.el5.i386.rpm

Hardware IP: 192.168.30.121 (primary)
192.168.20.122 (from)

The master and slave server installation methods are the same:

1, linux create postgres users and user groups
groupadd postgres
useradd -g postgres postgres

2. Installation package installation sequence:
rpm -ivh postgresql92-libs-9.2.10-1PGDG.rhel5.i386.rpm
rpm -ivh postgresql92-9.2.10-1PGDG.rhel5.i386.rpm
rpm -ivh uuid-1.5.1-3.el5.i386.rpm #Install the packages that contrib depends on
rpm -ivh postgresql92-devel-9.2.10-1PGDG.rhel5.i386.rpm #Depending on the package
rpm -ivh postgresql92-server-9.2.10-1PGDG.rhel5.i386.rpm
rpm -ivh postgresql92-contrib-9.2.10-1PGDG.rhel5.i386.rpm

3. Initialize the PostgreSQL database:
service postgresql-9.2 initdb

4. start
service postgresql-9.2 start

5. Add the PostgreSQL service to the startup list
chkconfig postgresql-9.2 on
chkconfig --list | grep postgres

 

Method 2. Source installation:

1, linux create postgres users and user groups
groupadd postgres
useradd -g postgres postgres


2, decompress the compressed package

[[email protected]] # tar -xzvf /var/local/pgsql/postgresql-9.2.10.tar.gz

Enter the decompression directory: cd /var/local/pgsql/postgresql-9.2.10
3. Compile and install:

Create installation and data directories

mkdir / usr / local / pgsql
mkdir / home / postgres / data

[[email protected] postgresql-9.2.10] # ./configure --prefix = / usr / local / pgsql -localstatedir = / home / postgres / data

Handle the error message:
checking for readline ... no
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure. It is possible the compiler is n‘t looking in the proper directory.
Use --without-readline to disable readline support.

solve:

Missing readline-devel dependency package

Install the readline-devel package

I choose the yum installation here, you can go online to download a version suitable for you to install
yum install readline-devel-5.1-3.el5

Compile again after installation

Recompile:

[[email protected] postgresql-9.2.10] # ./configure --prefix = / usr / local / pgsql -localstatedir = / home / postgres / data

 Compilation can be installed without error

[[email protected] postgresql-9.2.10] # make
All of PostgreSQL successfully made. Ready to install.

 

[[email protected] postgresql-9.2.10] # make install
PostgreSQL installation complete.

4. Modify the data directory permissions after installation
chown -R postgres: postgres / usr / local / pgsql /
chown -R postgres: postgres / home / postgres / data /


Modify the .bash_profile file for the postgres user:


[[email protected] ~] $ vi .bash_profile
Add to:

PGLIB = / usr / local / pgsql / lib
PGDATA = / home / postgres / data
PATH = $ PATH: / usr / local / pgsql / bin
MANPATH = $ MANPATH: / usr / local / pgsql / man
export PGLIB PGDATA PATH MANPATH

[[email protected] ~] $ source .bash_profile

 

5. Initial postgresql and start postgresql

initialization:
[[email protected] ~] $ / usr / local / pgsql / bin / initdb / home / postgres / data
Success. You can now start the database server using:

/ usr / local / pgsql / bin / postmaster -D / home / postgres / data
or
/ usr / local / pgsql / bin / pg_ctl -D / home / postgres / data -l logfile start

 start up:

[[email protected] ~] $ / usr / local / pgsql / bin / pg_ctl -D / home / postgres / data start

======================================================== =============================================

The installation from the library is the same as the main library postgresql installation

======================================================== =============================================

Second, compile and install slony-i

1. Unzip the software package:


[[email protected] local] # tar -xjvf /var/local/slony1-1.2.6.tar.bz2

 

2.Compile and install the software package

[[email protected] ~] # cd /var/local/slony1-1.2.6
[[email protected] slony1-1.2.6] # ./configure --with-pgsourcetree = / usr / local / pgsql / bin

 

[[email protected] slony1-1.2.6] # make
All of Slony-I is successfully made. Ready to install

 

[[email protected] slony1-1.2.6] # make install
All of Slony-I is successfully installed

 

======================================================== =============================================


Slony should be installed in both the master and slave libraries. The installation method is the same as above.

====================================================== ====================================

 Third, the master-slave configuration

1.postgresql add replication user

 su-under the postgres user

[[email protected] ~] $ psql
psql (9.2.10)
Type "help" for help.

postgres = # create role repl password ‘123456’ login superuser replication;

Both master and slave execute this statement

 

2.postgresql configuration file

postgresql.conf

Both master and slave changed:

vi /home/postgres/data/postgresql.conf

Add: listen_addresses = ‘*’


Main library pg_hba.conf

 vi /home/postgres/data/pg_hba.conf
Add to:
host all repl 192.168.30.122/32 md5

 

From the library pg_hba.conf

 vi /home/postgres/data/pg_hba.conf
Add to:
host all repl 192.168.30.121/32 md5

 

 After modifying the configuration, restart both the master and slave servers to restart the postgresql service:

[[email protected] ~] $ / usr / local / pgsql / bin / pg_ctl -D / home / postgres / data restart

 

3. Establish test database and test table

 

Both master and slave libraries need to create databases and tables. Slony cannot synchronize DDL statements.

The following takes the establishment of the main database and data table test on the main database server as an example. For other databases and data tables, please refer to the establishment.

 

/ usr / local / pgsql / bin / createdb test

cat sql.txtpsql -Urepl test -W123456
sql.txt file is best in UTF-8 format, especially when Chinese characters exist) Example: sql.txt
CREATE TABLE tb_depart (Id int primary key, Name char (8));
Create the same database test on the slave database server as on the master database server


View after creation:
[[email protected] ~] $ psql -Urepl test


test = # \ d
List of relations
Schema | Name | Type | Owner
-------- + ----------- + ------- + --------
public | tb_depart | tabl
e | repl
(1 row)

 

test = # \ d tb_depart;
Table "public.tb_depart"
Column | Type | Modifiers
-------- + -------------- + -----------
id | integer | not null
name | character (8) |
Indexes:
"tb_depart_pkey" PRIMARY KEY, btree (id)

test = # \ q

 

Create the same database test on the slave database server as on the master database server

 4.slony-i configuration master-slave synchronization

Just configure it in the slave library:
Create a script file in the / home / postgres / directory:

The contents of the slony_0.sh file are as follows:

 

#! / bin / sh
/ usr / local / pgsql / bin / slonik << _END_
#
# Define cluster namespace and node connection information #
#Cluster name
cluster name = testdb;
# Define replication node
node 1 admin conninfo = ‘dbname = test host = 192.168.30.121 port = 5432 user = repl’;
node 2 admin conninfo = ‘dbname = test host = localhost port = 5432 user = repl’;
DROP SET (id = 1, origin = 1);
uninstall node (id = 1);
uninstall node (id = 2);
echo ‘Drop testdb set’;
_END_


The contents of the slony_1.sh file are as follows:

#! / bin / sh
/ usr / local / pgsql / bin / slonik << _END_
cluster name = testdb;
# Define replication node
node 1 admin conninfo = ‘dbname = test host = 192.168.30.121 port = 5432 user = repl’;
node 2 admin conninfo = ‘dbname = test host = localhost port = 5432 user = repl’;
echo ‘Cluster defined, nodes identified’;
# Initialize the cluster, id starts from 1
init cluster (id = 1, comment = ‘Master Node’);
# Set storage node
store node (id = 2, comment = ‘Slave Node’);
echo ‘Nodes defined’;
# Set storage path
store path (server = 1, client = 2, conninfo = ‘dbname = test host = 192.168.30.121 port = 5432 user = repl’);
store path (server = 2, client = 1, conninfo = ‘dbname = test host = localhost port = 5432 user = repl’);
#Set the listening event and subscription direction, the role in replication, the master node is the original provider, and the slave node is the receiver
store listen (origin = 1, provider = 1, receiver = 2);
store listen (origin = 2, provider = 2, receiver = 1);
_END_

 

The content of slony_2.sh file is as follows:

#! / bin / sh
/ usr / local / pgsql / bin / slonik << _END_ #
# Define cluster namespace and node connection information #
cluster name = testdb;
node 1 admin conninfo = ‘dbname = test host = 192.168.30.121 port = 5432 user = repl’;
node 2 admin conninfo = ‘dbname = test host = localhost port = 5432 user = repl’;
# Set the data table to participate in synchronization
#First create a replication set, id also starts from 1
#Add tables to your own replication set, one set command for each table that needs to be replicated
#id starts from 1 and increments in steps, with a step of 1.
#fully qualified name is the full name of the table: schema name. table name
#The replication set id here needs to be the same as the replication set id created earlier
#If a table does not have a primary key, but has a unique key, then you can use the key keyword
#Specify it as a copy key, such as the key parameter below
#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, this needs to be handled, this sentence is placed before the create set
#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);

create set (id = 1, origin = 1, comment = ‘testdb 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’);
set add table (set id = 1, origin = 1, id = 4, fully qualified name = ‘public.tb_test’, comment = ‘Table tb_test’);
echo ‘set 1 of testdb tables created’;
_END_

 


The contents of the slony_3.sh file are as follows:

# / bin / sh
/ usr / local / pgsql / bin / slon testdb "dbname = test host = 192.168.30.121 port = 5432 user = repl"> ~ / slon_gb_1.out 2> & 1 &
/ usr / local / pgsql / bin / slon testdb "dbname = test host = localhost port = 5432 user = repl"> ~ / slon_gb_2.out 2> & 1 &

/ usr / local / pgsql / bin / slonik << _END_
# Define cluster namespace and node connection information #
cluster name = testdb; #Provide connection parameters
node 1 admin conninfo = ‘dbname = test host = 192.168.30.121 port = 5432 user = repl’;
node 2 admin conninfo = ‘dbname = test host = localhost port = 5432 user = repl’;

# Submit subscription replication set
subscribe set (id = 1, provider = 1, receiver = 2, forward = no);
echo ‘set 1 of gb tables subscribed by node 2’;
_END_


[[email protected] cluster_shells] $ chmod u + x slony _ *. sh

 

 

The contents of the slony_main.sh file are as follows:

#! / bin / sh
case $ 1 in
start)
cd / home / postgres /
sh slony_3.sh
;;
stop)
killall -KILL slon
;;
rebuild)
cd / home / postgres
killall -KILL slon
sh slony_0.sh >> / dev / null 2> & 1
sh slony_1.sh
sh slony_2.sh
sh slony_3.sh
;;
*)
echo "Please input start or stop or rebuild !!"
;;
esac

 

[[email protected] cluster_shells] $ chmod u + x slony_main.sh

 

 

Test synchronization:


On the slave side:

Follow the execution sequence below

./slony_0.sh


 ./slony_1.sh
<stdin>: 5: Cluster defined, nodes identified
<stdin>: 10: Nodes defined

 

 ./slony_2.sh
<stdin>: 8: set 1 of testdb tables created

 

 ./slony_3.sh
<stdin>: 7: set 1 of gb tables subscribed by node 2

 

Before modifying the data:
Main library side:
[[email protected] ~] $ psql test


test = # \ d
List of relations
Schema | Name | Type | Owner
-------- + ----------- + ------- + --------
public | tb_depart | table | repl
(1 row)

 

 

test = # \ d tb_depart
Table "public.tb_depart"
Column | Type | Modifiers
-------- + -------------- + -----------
id | integer | not null
name | character (8) |
Indexes:
"tb_depart_pkey" PRIMARY KEY, btree (id)
Triggers:
_testdb_logtrigger_1 AFTER INSERT OR DELETE OR UPDATE ON tb_depart FOR EACH ROW EXECUTE PROCEDURE _testdb.logtrigger (‘_ testdb’, ‘1’, ‘kv’)

A trigger has been created for synchronization.




No data for the time being;
test = # select * from tb_depart;
id | name
---- + ------
(0 rows)

 

  

Standby side:
[[email protected] ~] $ psql test
Welcome to psql 8.1.23, the Pos
tgreSQL interactive terminal.

Type: \ copyright for distribution terms
\ h for help with SQL commands
\? for help with psql commands
\ g or terminate with semicolon to execute query
\ q to quit

test = # \ d
List of relations
Schema | Name | Type | Owner
-------- + ----------- + ------- + --------
public | tb_depart | table | repl
(1 row)

 


test = # \ d tb_depart
Table "public.tb_depart"
Column | Type | Modifiers
-------- + -------------- + -----------
id | integer | not null
name | character (8) |
Indexes:
"tb_depart_pkey" PRIMARY KEY, btree (id)
Triggers:
_testdb_denyaccess_1 BEFORE INSERT OR DELETE OR UPDATE ON tb_depart FOR EACH ROW EXECUTE PROCEDURE _testdb.denyaccess (‘_ testdb’)
slony created a trigger-two-way synchronization;


No data temporarily
test = # select * from tb_depart;
id | name
---- + ------
(0 rows)

test = #

 

Add data to the main library:


test = # insert into tb_depart values (1, ‘aaa’);
INSERT 0 1

 

test = # select * from tb_depart;
id | name
---- + ----------
1 | aaa
(1 row)

test = #


View on the reserve side:
test = # select * from tb_depart;
id | name
---- + ----------
1 | aaa
(1 row)


Synchronization succeeded.

postgresql + slony-i