Slony-I 2.1.0 同步postgreSQL

常炯

2023-12-01

环境：

主机 win7 CPU：Intel(R) Core(TM) i5 2.5GHz 内存：4G

VMware虚拟2台 CentOS(512M 20G)

Master IP：192.168.20.138

Slaver IP：192.168.20.139

软件：slony1-2.1.0.tar.bz2 postgresql-9.0.4.tar.bz2

一、源码安装postgreSQL数据库

tar -jxvf postgresql-9.0.4.tar.bz2

cd postgresql-9.0.4

configure --prefix=/user/pgsql9.0.4/

make all

make

二、安装slony-I

tar -jxvf slony1-2.1.0.tar.bz2

cd slony1-2.1.0

configure --with-pgconfigdir=/user/pgsql9.0.4/bin

make all

make install

在数据库edb afc模式下建立表bake

create table bake(

id character varying(10) not null,

pid character varying(10),

constraint pk_mk primary key(id)

);

以上两步主从机器都需要安装

三、主库机器上编写 /home/work/configSlonyMaster.sh

#!/bin/bash

SLONIK=/user/pgsql9.0.4/bin/slonik

CLUSTER=mycluster

MASTER_ID=1

MASTER_HOST=192.168.20.138

MASTER_DBNAME=edb

MASTER_USER=enterprisedb

SLAVER_ID=2

SLAVER_HOST=192.168.20.204

SLAVER_DBNAME=edb

SLAVER_USER=enterprisedb

$SLONIK<<_EOF_

cluster name = $CLUSTER;

node $MASTER_ID admin conninfo = 'dbname=$MASTER_DBNAME host=$MASTER_HOST user=$MASTER_USER';

node $SLAVER_ID admin conninfo = 'dbname=$SLAVER_DBNAME host=$SLAVER_HOST port=5444 user=$SLAVER_USER';

init cluster(id = 1, comment = 'Primary Cluster');

create set ( id = 1, origin = $MASTER_ID, comment = 'Data Provider');

set add table ( set id = 1, origin = $MASTER_ID, id = 1, fully

qualified name = 'afc.bake', comment='Repl Table' );

store node ( id = $SLAVER_ID, comment = 'Slave Node', event node = 1);

store path ( server=1, client=2,

conninfo = 'dbname=$MASTER_DBNAME host=$MASTER_HOST user=$MASTER_USER');

store path ( server=2, client=1,

conninfo = 'dbname=$SLAVER_DBNAME host=$SLAVER_HOST port=5444 user=$SLAVER_USER');

store listen ( origin = 1, provider = 1, receiver = 2 );

store listen ( origin = 2, provider = 2, receiver = 1 );

_EOF_

赋予脚本可执行权限，并运行脚本

脚本运行后，使用psql查看主库bake表，会发现bake上添加了几个触发器

四、主库机器启动slon后台进程

[root@mycentos bin]# ./slon mycluster "dbname=edb host=192.168.20.139 port=5444 user=enterprisedb" &

192.168.20.139为从库机器IP，port为从库pg端口

五、从库机器启动slon后台进程

[root@mycentos bin]# ./slon mycluster "dbname=edb host=192.168.20.138 user=enterprisedb" &

192.168.20.138为主库机器IP，主库port端口为默认5432，可以不写

六、主库机器编写运行订阅同步集脚本 syn.sh