PostgreSQL数据库双主同步

使用Bucardo搭建PG数据库同步1、Bucardo介绍Bucardo是针对PostgreSQL数据库开发的同步软件，可以实现PostgreSQL数据库的主从、主主、多主同步方案，不过bucardo中的同步都是异步的，它是通过触发器记录变化，甚至可以以postgresql为源库，可以和oracle、mysql、mongodb等很多数据库进行数据异步同步。2、Bucardo搭建前准备2.1搭建环境版本要求：(atleast5.8.3)(atleast8.2)(atleast1.51)(atleast2.0.0)(atleast5.8.3)(atleast8.2)(atleast1.51)(atleast2.0.0)(atleast1.2.4)2009(Core)PostgresDBImoduleDBD::PgmoduleDBIx::SafemoduleLinux版本cat/etc/redhat-releaseCentOSLinuxreleasePerl安装（所有节点都需要安装）本次系统linux版本是7.6Perl是linux系统自带；默认版本是v5.16.1；可行[root@node3Bucardo]#perl-vThisisperl,v5.16.1(*)builtforx86_64-linux-thread-multi如果perl版本低于5.8则执行如下操作####需要安装perl-5/src/README.htmlwget/src/5.0/perl-5.28.O.tar.gztar-zxvfperl-5.28.0.tar.gzcdperl-5.28.0./ConfigureMakemakeinstall进行Configure时务必不要指定-d参数，其中有2个重要选项不能采用默认配置：Buildasharedlibperl.so（y/n）[n]这里要选YBuildathreadingPerl?[n]这里要选Y安装完成后登陆数据库查看plperl语言组件是否能正确运行：createlanguageplperlu;createlanguageplperl2.3安装postgreSQL（所有节点都需要安装）在编译的过程需要带上-with-perl;./configure--prefix=/pgsql--with-perl2.4安装Pl/PerlU（所有节点都安装）之前安装Pg时已经加入了一with-perl则不需要再在此安装）yuminstallpostgresql-plperl3、安装依赖包（主节点安装）安装DBImodule安装DBI依赖Test-Simple；安装Test-SimpleTest-Simple下载地址搜索Test-Simple#解压Test-Simple源码包tar-zxvfTest-Simple-1.302183.tar.gz#进入Test-Simple目录进行安装cdTest-Simple-1.302183perlMakefile.PLmakemakeinstall#接下来安装DBI下载地址：/CPAN/authors/id/T/TI/TIMB/DBIT.630.tar.gztar-zxvfDBI-1.630.tar.gzcdDBI-1.630perlMakefile.PLmakemakeinstall安装DBD:Pgmodule安装最新的DBD::Pg；需要依赖Time::HiRes安装Time::HiRes下载地址：搜索Time::HiRestar-zxvfTime-HiRes-1.9764.tar.gzcdTime-HiRes-1.9764perlMakefile.PLmakemakeinstall#接下来安装DBD::Pg#安装DBD::Pg；需要确定已经安装PostgreSQL环境；即pg_config输出加载PostgreSQL环境变量tar-zxvfDBD-Pg-3.14.2.tar.gzcdDBD-Pg-3.14.2perlMakefile.PLmakemakeinstall安装DBIx::Safe下载地址：/DBIx-Safe/tar-zxvfDBIx-Safe-1.2.5.tar.gzcdDBIx-Safe-1.2.5perlMakefile.PLmakemakeinstall4、安装Bucardo在网站/Bucardo/下载tar-zxvfBucardo-5.6.0.tar.gzcdBucardo-5.6.0perlMakefile.PLmakemakeinstall#查看Bucardo版本[postgres@node3~]$bucardo--versionbucardoversion5.6.05、创建bucardo元数据库创建.bucardorc文件在系统用户home目录下创建；内容如下：TOC\o"1-5"\h\zlog_conflict_file=/home/postgres/bucardo/log/bucardo_conflict.logpiddir=/home/postgres/bucardo/runreason_file=/home/postgres/bucardo/log/bucardo.restart.reason.logwarning_file=/home/postgres/bucardo/log/bucardo.warning.logsyslog_facility=LOG_LOCAL1创建所需的目录及数据库mkdir-p/home/postgres/bucardo/logmkdir-p/home/postgres/bucardo/runmkdir-p/var/run/bucardochmod777/var/run/bucardo$psqlpostgres=#createuserbucardowithsuperuserpassword'bucardo';postgres=#createdatabasebucardowithowner=bucardo;5.3初始化bucardo管理库准备工作完成；开始执行bucardoinstall命令创建元数据库；#/usr/local/share/bucardo/bucardo.schema[postgres@node3~]$bucardoinstallThiswillinstallthebucardodatabaseintoanexistingPostgrescluster.PostgresmusthavebeencompiledwithPerlsupport,andyoumustconnectasasuperuserCurrentconnectionsettings:Host:<none>Port:5432User:bucardoDatabase:bucardoPIDdirectory:/home/postgres/bucardo/runEnteranumbertochangeit,Ptoproceed,orQtoquit:1—推荐使用unix套接字方式连接数据;可以使用dbuser:postgres;dbname:postgres去连接。Changethehostto:/tmpChangedhostto:/tmpCurrentconnectionsettings:Host:/tmpPort:6000User:postgresDatabase:postgresPIDdirectory:/home/postgres/bucardo/runEnteranumbertochangeit,Ptoproceed,orQtoquit:PAttemptingtocreateandpopulatethebucardodatabaseandschemaDatabasecreationiscompleteUpdatedconfigurationsetting"piddir"Installationisnowcomplete.Ifyouseeerrorsorneedhelp,pleaseemailbucardo-general@Youmaywanttocheckovertheconfigurationvariablesnext,byrunning:bucardoshowallChangeanysettingbyusing:bucardosetfoo=bar6、Bucardo配置同步（主节点postgre用户操作）6.1添加复制数据库（在db1上执行操作）执行bucardoadddB命令；记录同步数据库的连接信息bucardoadddbdb01host=1dbname=postgresport=5432user=postgrespass=123456bucardoadddbdb02host=2dbname=postgresport=5432user=postgrespass=123456添加数据库群组添加dbgroup数据库组；在一套复制系统中可以有多个源数据库、目标数据库。指定哪个数据库是源数据库，哪个是目标数据库[postgres@node3~]$bucardoadddbgroupdbgrp01db1:sourcedb2:targetCreateddbgroup"dbgrp01"Addeddatabase"db1"todbgroup"dbgrp01"assourceAddeddatabase"db2"todbgroup"dbgrp01"astarget添加需要同步的表及序列bucardoaddalltablesbucardoaddallsequences添加表群组#创建复制表tbl_lottuOl[postgres@node3~]$psqlmasterpostgres-c"createtabletbl_lottu01(idintprimarykey,infotext)"CREATETABLE[postgres@node3~]$psqlslavepostgres-c"createtabletbl_lottu01(idintprimarykey,infotext)"CREATETABLE#添加relgroup复制集；即要复制的表、序列的集合[postgres@node3~]$bucardoaddrelgrouprelgrp01tbl_lottu01Createdrelgroup"relgrp01"Addedthefollowingtablesorsequences:public.tbl_lottu01(DB:db1)Thefollowingtablesorsequencesarenowpartoftherelgroup"relgrp01":public.tbl_lottu01添加同步添加同步；在创建同步需要指定dbgroup和relgroup[postgres@node3~]$bucardoaddsyncsync01relgroup=relgrp01dbgroup=dbgrp01conflict_strategy=bucardo_latest2020-11-0317:08:51.494CST[6988]LOG:Startingvalidate_syncforsync012020-11-0317:08:51.494CST[6988]CONTEXT:PL/Perlfunction"validate_sync"SQLstatement"SELECTvalidate_sync('sync01')"PL/Perlfunction"validate_sync"2020-11-0317:08:51.494CST[6988]STATEMENT:INSERTINTObucardo.sync(herd,name,dbs,conflict_strategy)VALUES($1,$2,$3,$4)2020-11-0317:08:51.619CST[6988]LOG:Endingvalidate_syncforsync012020-11-0317:08:51.619CST[6988]CONTEXT:PL/Perlfunction"validate_sync"SQLstatement"SELECTvalidate_sync('sync01')"PL/Perlfunction"validate_sync"2020-11-0317:08:51.619CST[6988]STATEMENT:INSERTINTObucardo.sync(herd,name,dbs,conflict_strategy)VALUES($1,$2,$3,$4)Addedsync"sync01"启动bucardobill@docker->bucardostartCheckingforexistingprocessesRe