PostgreSQL的Slony-I数据同步实践(基于触发器)

2016-07-12 10:17:37来源:oschina作者:Holiday_人点击

Slony-I是一个“一主多备”的复制系统,支持级联复制(ascading)和失效转移failover.

官方文档:

slony-I的系统分析:https://wiki.postgresql.org/wiki/Slony


slony-I安装配置使用


系统要求:http://slony.info/documentation/requirements.html


使用限制:http://slony.info/documentation/2.2/limitations.html


配置与安装:http://slony.info/documentation/administration.html#INSTALLATION


使用配置:http://slony.info/documentation/tutorial.html#FIRSTDB

环境:

主库:centos linux 32bit虚拟机,ip为192.168.100.240


PostgreSQL9.2.13


Slony-I 2.2


备库: centos linux 32bit虚拟机, ip为192.168.100.241


PostgreSQL9.2.13


Slony-I 2.2

1.源码编译安装PostgreSQL

在主库和备库都,进行源码编译、安装、配置PostgreSQL数据库如下:


源码编译安装PostgbreSQL9.2。


安装目录:/opt/pgsql2


编译安装过程请参考:PostgreSQL在Linux下的源码编译安装


源码安装完毕后,要配置pg_hba.conf和postgresql.conf确保主、备库可以远程访问。


注意:PG版本要必须是Slony支持的版本,详见http://slony.info/documentation/requirements.html 。



2.准备slony-I复制的主、备库
2.1声明环境变量

在主库和备库都,执行:


su-postgres
exportCLUSTERNAME=lyy_cluster1
exportMASTERDBNAME=masterdb
exportSLAVEDBNAME=slavedb
exportMASTERHOST=192.168.13.128
exportSLAVEHOST=192.168.100.236
exportREPLICATIONUSER=postgres

注意:


1.REPLICATIONUSER通常是PostgreSQL数据库的超级用户。


2.修改MASTERHOST和SLAVEHOST时,尽量不要使用localhost,因为可能会导致错误:ERROR remoteListenThread_1: db_getLocalNodeId() returned 2 - wrong database?。


2.2根据环境变量准备数据库

在主库和备库都,根据环境变量来创建相应对象:


--在主服务器中
cd/opt/pgsql2/bin
./createuser-p5432-Upostgres-SRD-P$PGBENCHUSER--若已创建则无需再创建
inputpasswordfornewuser:(此处输入新用户的密码即可)
inputpasswordagain:(此处输入新用户的密码即可)
password:(此处输入超级用户postgres的密码即可)
./createdb-p5432-Upostgres-O$PGBENCHUSER-h$MASTERHOST$MASTERDBNAME
password:(此处输入超级用户postgres的密码即可)
--在备用服务器中
cd/opt/pgsql2/bin
./createuser-p5432-Upostgres-SRD-P$PGBENCHUSER--若已创建则无需再创建
inputpasswordfornewuser:(此处输入新用户的密码即可)
inputpasswordagain:(此处输入新用户的密码即可)
password:(此处输入超级用户postgres的密码即可)
./createdb-p5432-Upostgres-O$PGBENCHUSER-h$SLAVEHOST$SLAVEDBNAME
password:(此处输入超级用户postgres的密码即可)
--在主服务器,创建要同步的数据表(数据表必须有主键或者唯一键,才能通过slony-i实现数据同步)
[postgres@localhostbin]$./psql-U$PGBENCHUSER-h$MASTERHOST-d$MASTERDBNAME
psql(9.2.13)
Type"help"forhelp..
masterdb=#CREATETABLElyy(idintprimarykey,namevarchar);
CREATETABLE


2.3创建pl/pgsql过程语言

Slony-I 需要数据库有 pl/pgSQL 过程语言,如果模板数据 template1已经安装了pl/pgSQL,那么新建的$MASTERDBNAME也就也有了pl/pgSQL,如果已经存在了,则不需要执行以下语句:


在主库和备库都执行:


--在bin目录下
./createlang-h$MASTERHOSTplpgsql$MASTERDBNAME 2.4手动从主库将表定义导入备库

当备库Slony-I 订阅主库之后,Slony-I不能自动从主库拷贝表定义到备库,所以我们需要把表定义从主库导入备库,我们通过 pg_dump来实现表定义的主、备同步:


--在主服务器中执行pg_dump,将表定义备份到备库中
--在bin目录下
[postgres@localhostbin]$./pg_dump-s-Upostgres-h192.168.100.240masterdb|./psql-Upostgres-h192.168.100.241slavedb 3.源码编译安装Slony-I

在主库和备库都,按照以下步骤安装slony-i。


slony下载地址:http://slony.info/downloads/2.2/source/


下载并解压slony-2.2,然后进行编译配置安装,要确保每一步安装正确后再进行下一步。


cd../slony-2.2
./configure--with-pgconfigdir=/opt/pgsql944/bin--with-perltools
gmakeall
gamkeinstall

注意:如果slony-I后面的配置过程使用altperl脚本,则configure时必须指定--with-perltools。

4. 配置slony并启动同步复制
4.0配置并启用slony同步复制的理论基础(仅供理解):

Configuring the Database For Replication.


Creating the configuration tables, stored procedures, triggers and configuration is all done through thesloniktool. It is a specialized scripting aid that mostly calls stored procedures in the master/slave (node) databases.


The example that follows usesslonikdirectly (or embedded directly into scripts). This is not necessarily the most pleasant way to get started; there exist tools for buildingslonikscripts under thetoolsdirectory, including:

Section 6.1.1- a set of Perl scripts that buildslonikscripts based on a singleslon_tools.conffile.


Section 6.1.2- a shell script (e.g.- works with Bash) which, based either on self-contained configuration or on shell environment variables, generates a set ofslonikscripts ‍‍to configure a whole cluster.

以下两种方法省略详细介绍,详见文档http://slony.info/documentation/tutorial.html#FIRSTDB。


方法一:Using slonik Command Directly


方法二:Using the altperl Scripts


扩展至:http://file:///C:/Users/Yuanyuan/Desktop/slony1-2.2.4/doc/adminguide/additionalutils.html#ALTPERL

4.1直接使用slonik命令(以上方法一)

在主库配置slony cluster


在主库,创建并执行slony_setup.sh文件,实现slony cluster的配置:


[postgres@localhostdata]$vislony_setup.sh
CLUSTERNAME=lyy_cluster1
MASTERDBNAME=masterdb
SLAVEDBNAME=slavedb
MASTERHOST=192.168.100.240
SLAVEHOST=192.168.100.241
REPLICATIONUSER=postgres
/opt/pgsql92/bin/slonik<<_EOF_
#--
#definethenamespacethereplicationsystem
#usesinourexampleitisslony_example
#--
clustername=$CLUSTERNAME;
#--
#adminconninfo'sareusedbysloniktoconnectto
#thenodesoneforeachnodeoneachsideofthecluster,
#thesyntaxisthatofPQconnectdbin
#theC-API
#--
node1adminconninfo='dbname=$MASTERDBNAME/
host=$MASTERHOSTuser=$REPLICATIONUSER';
node2adminconninfo='dbname=$SLAVEDBNAME/
host=$SLAVEHOSTuser=$REPLICATIONUSER';
#--
#initthefirstnode.ItsidMUSTbe1.Thiscreates
#theschema_$CLUSTERNAMEcontainingallreplication
#systemspecificdatabaseobjects.
#--
initcluster(id=1,comment='MasterNode');
#--
#Slony-Iorganizestablesintosets.Thesmallestunit
#anodecansubscribeisaset.Themasterororiginof
#thesetisnode1.
#--
createset(id=1,origin=1,comment='Allmasterdbtables');
setaddtable(setid=1,origin=1,id=1,
fullyqualifiedname='public.lyy',
comment='lyytable');
#setaddsequence(setid=1,origin=1,id=1,
#fullyqualifiedname='public.t1_id_seq',
#comment='t1idsequence');
#--
#Createthesecondnode(theslave)tellthe2nodeshow
#toconnecttoeachotherandhowtheyshouldlistenforevents.
#--
storenode(id=2,comment='SlaveNode',eventnode=1);
storepath(server=1,client=2,conninfo='dbname=$MASTERDBNAME/
host=$MASTERHOSTuser=$REPLICATIONUSER');
storepath(server=2,client=1,conninfo='dbname=$SLAVEDBNAME/
host=$SLAVEHOSTuser=$REPLICATIONUSER');
_EOF_ [postgres@localhostdata]$shslony_setup.sh

注释:本步执行完毕后,初始化完毕一个名为lyy_cluster1的slony集群。


并相应的产生一个名为_lyy_cluster1的模式,里面含有slony运行所需的配置表、序列、函数、触发器等(主要是通过slony-i安装过程中在/opt/pgsql92/share下生成的slony1_base.2.2.4.sql和slony1_funcs.2.2.4.sql)。




还会在同步表lyy下创建相应的触发器。


在主库:


masterdb=#/dlyy
Table"public.lyy"
Column|Type|Modifiers
--------+-------------------+-----------
id|integer|notnull
name|charactervarying|
Indexes:
"lyy_pkey"PRIMARYKEY,btree(id)
Triggers:
_lyy_cluster1_logtriggerAFTERINSERTORDELETEORUPDATEONlyyFOREACHROWEXECUTEPROCEDURE_lyy_cluster1.logtrigger('_lyy_cluster1','1','k')
_lyy_cluster1_truncatetriggerBEFORETRUNCATEONlyyFOREACHSTATEMENTEXECUTEPROCEDURE_lyy_cluster1.log_truncate('1')
_lyy_cluster_logtriggerAFTERINSERTORDELETEORUPDATEONlyyFOREACHROWEXECUTEPROCEDURE_lyy_cluster.logtrigger('_lyy_cluster','1','k')
_lyy_cluster_truncatetriggerBEFORETRUNCATEONlyyFOREACHSTATEMENTEXECUTEPROCEDURE_lyy_cluster.log_truncate('1')
Disabledtriggers:
_lyy_cluster1_denyaccessBEFOREINSERTORDELETEORUPDATEONlyyFOREACHROWEXECUTEPROCEDURE_lyy_cluster1.denyaccess('_lyy_cluster1')
_lyy_cluster1_truncatedenyBEFORETRUNCATEONlyyFOREACHSTATEMENTEXECUTEPROCEDURE_lyy_cluster1.deny_truncate()
_lyy_cluster_denyaccessBEFOREINSERTORDELETEORUPDATEONlyyFOREACHROWEXECUTEPROCEDURE_lyy_cluster.denyaccess('_lyy_cluster')
_lyy_cluster_truncatedenyBEFORETRUNCATEONlyyFOREACHSTATEMENTEXECUTEPROCEDURE_lyy_cluster.deny_truncate()

在备库:


slavedb=#/dlyy
Table"public.lyy"
Column|Type|Modifiers
--------+-------------------+-----------
id|integer|notnull
name|charactervarying|
Indexes:
"lyy_pkey"PRIMARYKEY,btree(id)
Triggers:
_lyy_cluster1_denyaccessBEFOREINSERTORDELETEORUPDATEONlyyFOREACHROWEXECUTEPROCEDURE_lyy_cluster1.denyaccess('_lyy_cluster1')
_lyy_cluster1_truncatedenyBEFORETRUNCATEONlyyFOREACHSTATEMENTEXECUTEPROCEDURE_lyy_cluster1.deny_truncate()
Disabledtriggers:
_lyy_cluster1_logtriggerAFTERINSERTORDELETEORUPDATEONlyyFOREACHROWEXECUTEPROCEDURE_lyy_cluster1.logtrigger('_lyy_cluster1','1','k')
_lyy_cluster1_truncatetriggerBEFORETRUNCATEONlyyFOREACHSTATEMENTEXECUTEPROCEDURE_lyy_cluster1.log_truncate('1')

在主库启动主库节点及监视器


在主库,执行以下命令启动slon daemon:


[postgres@localhostbin]$./slonlyy_cluster1"dbname=masterdbuser=postgreshost=192.168.100.240"&

执行当前命令的终端会不断地返回检测信息,所以该终端不要关闭也不要再执行其他操作。

在备库启动备库节点及监视器


在备库。执行以下命令启动slon deamon:


[postgres@localhostbin]$./slonslony_example"dbname=slavedbuser=postgreshost=192.168.100.241"&

执行当前命令的终端会不断地返回检测信息,所以该终端不要关闭也不要再执行其他操作。

在主库执行备库订阅主库


在主库,执行订阅过程, 通过脚本文件subscribe_set.sh来执行订阅过程:


[postgres@localhostdata]$visubscribe_set.sh
CLUSTERNAME=lyy_cluster1
MASTERDBNAME=masterdb
SLAVEDBNAME=slavedb
MASTERHOST=192.168.100.240
SLAVEHOST=192.168.100.241
REPLICATIONUSER=postgres
/opt/pgsql92/bin/slonik<<_EOF_
#----
#Thisdefineswhichnamespacethereplicationsystemuses
#----
clustername=$CLUSTERNAME;
#----
#Adminconninfo'sareusedbytheslonikprogramtoconnect
#tothenodedatabases.SothesearethePQconnectdbarguments
#thatconnectfromtheadministratorsworkstation(where
#slonikisexecuted).
#----
node1adminconninfo='dbname=$MASTERDBNAMEhost=$MASTERHOST/
user=$REPLICATIONUSER';
node2adminconninfo='dbname=$SLAVEDBNAMEhost=$SLAVEHOST/
user=$REPLICATIONUSER';
#----
#Node2subscribesset1
#----
subscribeset(id=1,provider=1,receiver=2,forward=no);
_EOF_ [postgres@localhostdata]$shsubscribe_set.sh

目前,本次数据库的slony的同步复制已经配置完毕。

验证slony-I同步复制生效


在主库,向表lyy插入数据:


[postgres@localhostbin]$./psql-Upostgres-dmasterdb
psql(9.2.13)
Type"help"forhelp.
masterdb=#insertintolyyvalues(1,'lyy');
INSERT01

在备库,查询表lyy中的数据情况:


postgres@localhostbin]$./psql-Upostgres-dslavedb
psql(9.2.13)
Type"help"forhelp.
slavedb=#select*fromlyy;
id|name
----+------
(0rows)
slavedb=#select*fromlyy;
id|name
----+------
1|lyy
(1row)

可以在主库执行增删操作,然后在备库执行查询操作,进行比对。


注意:slony-I 同步复制的备库是不能对同步的表数据进行修改的:


slavedb=#insertintotestvalues(4,'fff');
ERROR:Slony-I:Tabletestisreplicatedandcannotbemodifiedonasubscribernode-role=0 4.2使用altperl脚本配置并启动(以上方法二):

配置slony_tool.conf并初始化slony集群


默认情况下,slony的配置文件样本已默认安装至/usr/local/etc/slon_tools.conf-sample。


cd/usr/local/etc
--复制一个slon_tools.conf-sample,命名为slon_tools.conf
[root@localhostetc]#cpslon_tools.conf-sample/usr/local/etc/slon_tools.conf
--开始编辑slon_tools.conf
[root@localhostetc]#vislon_tools.conf

配置详情:


#修改CLUSTER_NAME为前面我们设置的
#Thenameofthereplicationcluster.Thiswillbeusedto
#createaschemanamed_$CLUSTER_NAMEinthedatabasewhichwill
#containSlony-relateddata.
$CLUSTER_NAME='lyy_cluster1';
#slony的pid文件目录。如果没有,则根据提示创建,并授予要求的权限。
#ThedirectorywhereSlonystorePIDfiles.This
#directorywillneedtobewritablebytheuserthatinvokes
#Slony.
$PIDFILE_DIR='/var/run/slony1';
#日志文件存储目录,如果没有,则修改配置或者根据配置创建目录。
#ThedirectorywhereSlonyshouldrecordlogmessages.This
#directorywillneedtobewritablebytheuserthatinvokes
#Slony.
$LOGDIR='/var/log/slony1';
#修改节点信息为我们的主、备节点的信息,样本中提供的多出的节点可以注释或者删除掉。
#Includeadd_nodelinesforeachnodeinthecluster.Besureto
#usehostnamesthatwillresolveproperlyonallnodes
#(i.e.onlyuse'localhost'ifallnodesareonthesamehost).
#Also,notethattheusermustbeasuperuseraccount.
add_node(node=>1,
host=>'162.168.100.240',
dbname=>'masterdb',
port=>5432,
user=>'postgres',
password=>'postgres');
add_node(node=>2,
host=>'192.168.100.241',
dbname=>'slavedb',
port=>5432,
user=>'postgres',
password=>'postgres');
#修改要同步的表或者序列
#Thisarraycontainsalistoftablesthatalreadyhaveprimarykeys.
"pkeyedtables"=>[
'public.lyy',
],
#如果没有序列和无主键表需要同步,则将以下示例注释掉。
#Fortablesthathaveuniquenotnullkeys,butnoprimary
#key,entertheirnamesandindexeshere.
#lyycommentthis
#"keyedtables"=>{
#'table3'=>'index_on_table3',
#'table4'=>'index_on_table4',
#},
#Sequencesthatneedtobereplicatedshouldbeenteredhere.
#lyycommentthis
#"sequences"=>['sequence1',
#'sequence2',
#],
}, #在主库,初始化slonycluster
[root@localhostetc]#slonik_init_cluster|/opt/pgsql92/bin/slonik
:10:Setupreplicationnodes
:13:Next:configurepathsforeachnode/origin
:16:Replicationnodesprepared
:17:Pleasestartaslonreplicationdaemonforeachnode

注释:同样的,本步执行完毕后,初始化完毕一个名为lyy_cluster1的slony集群。


并相应的产生一个名为_lyy_cluster1的模式,里面含有slony运行所需的配置表、序列、函数、触发器等(主要是通过slony-i安装过程中在/opt/pgsql92/share下生成的slony1_base.2.2.4.sql和slony1_funcs.2.2.4.sql)。

启动slon并进行数据集合订阅:


#在主库,启动节点1的slon
[root@localhostetc]#slon_start1
Invokeslonfornode1-/opt/pgsql92/bin//slon-p/var/run/slony1/lyy_cluster2_node1.pid-s1000-d2lyy_cluster1'host=192.168.100.240dbname=masteruser=postgresport=5432password=postgres'>/var/log/slony1/node1/master-2015-09-15.log2>&1&
Slonsuccessfullystartedforclusterlyy_cluster1,nodenode1
PID[7839]
Startthewatchdogprocessaswell...
#在备库,启动节点2的slon
[root@localhostetc]#slon_start2
Invokeslonfornode2-/opt/pgsql92/bin//slon-p/var/run/slony1/cluster1_node2.pid-s1000-d2cluster1'host=192.168.100.241dbname=slavedbuser=postgresport=5432password=postgres'>/var/log/slony1/node2/slavedb-2015-09-15.log2>&1&
Slonsuccessfullystartedforclusterlyy_cluster1,nodenode2
PID[7613]
Startthewatchdogprocessaswell...
#在主库,创建数据集合(此处1是一个set集合号)
[root@localhostetc]#slonik_create_set1|/opt/pgsql92/bin/slonik
:11:Subscriptionset1(set1_name)created
:12:Addingtablestothesubscriptionset
:16:Addprimarykeyedtablepublic.lyy
:19:Addingsequencestothesubscriptionset
:20:Alltablesadded
#在主库,订阅集合1到节点2(1=setID,2=nodeID)
[root@localhostetc]#slonik_subscribe_set12|/opt/pgsql92/bin/slonik
:6:Subscribednodestoset1

目前,本次数据库的slony的同步复制已经配置完毕。

验证slony-I同步复制生效


在主库,向表lyy插入数据:


[postgres@localhostbin]$./psql-Upostgres-dmasterdb
psql(9.2.13)
Type"help"forhelp.
masterdb=#insertintolyyvalues(1,'lyy');
INSERT01

在备库,查询表lyy中的数据情况:


postgres@localhostbin]$./psql-Upostgres-dslavedb
psql(9.2.13)
Type"help"forhelp.
slavedb=#select*fromlyy;
id|name
----+------
(0rows)
slavedb=#select*fromlyy;
id|name
----+------
1|lyy
(1row)

可以在主库执行增删操作,然后在备库执行查询操作,进行比对。


5.Slony-I的其他操作


slony的switchover操作(也就是把主节点改成从节点,从节点升级为主节点):


slonik_move_setset1node1node2|/opt/pgsql92/bin/slonik

slony的failver操作:


slonik_failovernode1node2|/opt/pgsql92/bin/slonik 6.在pgadmin中配置使用slony-I

转至:http://my.oschina.net/liuyuanyuangogo/blog/507936

7.slony-I的使用限制

Slony-I只能同步以下内容:


1.表数据(不能同步DDL,表必须含有主键或者唯一键)


2.序列


Slony-I不能自动同步以下内容:


1.对大对象(BLOBS)的变更


2.对DDL(数据定义语句)的变更


3.对用户和角色的变更


这些使用限制的主要原因是:slony-I是通过触发器收集变更情况的,而触发器不能够捕获定义和大对象的修改。对于DDL的变更,slony-I提供了SLONIK EXECUTE SCRIPT命令来执行DDL的SQL脚本,但slony-I不会自动执行,你得手动组织变更的DDL sql语句并用SLONIK EXECUTE SCRIPT(当然这也可以通过直接到备库执行sql语句来实现定义修改)。


另外,如果表定义中with oid,那么原库中的表记录oid的取值不能同步为相同的值。


建议:如果您无法接受以上使用限制,那您值得尝试一下PostgrSQL 8.0之后的PITR(Point in Time Recovery),PITR对于远程节点是基于WAL日志的。

参考资料:

http://www.cnblogs.com/gaojian/p/3196244.html


http://blog.chinaunix.net/uid-15145533-id-2775796.html


也可以只用pgbench来测试slony-i的数据同步。


pgbench使用:http://www.postgresql.org/docs/9.2/static/pgbench.html


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台