PostgreSQL數(shù)據(jù)庫雙主同步

使用Bucardo搭建PG數(shù)據(jù)庫同步1、Bucardo介紹Bucardo是針對(duì)PostgreSQL數(shù)據(jù)庫開發(fā)的同步軟件，可以實(shí)現(xiàn)PostgreSQL數(shù)據(jù)庫的主從、主主、多主同步方案，不過bucardo中的同步都是異步的，它是通過觸發(fā)器記錄變化，甚至可以以postgresql為源庫，可以和oracle、mysql、mongodb等很多數(shù)據(jù)庫進(jìn)行數(shù)據(jù)異步同步。2、Bucardo搭建前準(zhǔn)備2.1搭建環(huán)境版本要求：(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安裝（所有節(jié)點(diǎn)都需要安裝）本次系統(tǒng)linux版本是7.6Perl是linux系統(tǒng)自帶；默認(rèn)版本是v5.16.1；可行[root@node3Bucardo]#perl-vThisisperl,v5.16.1(*)builtforx86_64-linux-thread-multi如果perl版本低于5.8則執(zhí)行如下操作####需要安裝perl-5/src/README.htmlwget/src/5.0/perl-5.28.O.tar.gztar-zxvfperl-5.28.0.tar.gzcdperl-5.28.0./ConfigureMakemakeinstall進(jìn)行Configure時(shí)務(wù)必不要指定-d參數(shù)，其中有2個(gè)重要選項(xiàng)不能采用默認(rèn)配置：Buildasharedlibperl.so（y/n）[n]這里要選YBuildathreadingPerl?[n]這里要選Y安裝完成后登陸數(shù)據(jù)庫查看plperl語言組件是否能正確運(yùn)行：createlanguageplperlu;createlanguageplperl2.3安裝postgreSQL（所有節(jié)點(diǎn)都需要安裝）在編譯的過程需要帶上-with-perl;./configure--prefix=/pgsql--with-perl2.4安裝Pl/PerlU（所有節(jié)點(diǎn)都安裝）之前安裝Pg時(shí)已經(jīng)加入了一with-perl則不需要再在此安裝）yuminstallpostgresql-plperl3、安裝依賴包（主節(jié)點(diǎn)安裝）安裝DBImodule安裝DBI依賴Test-Simple；安裝Test-SimpleTest-Simple下載地址搜索Test-Simple#解壓Test-Simple源碼包tar-zxvfTest-Simple-1.302183.tar.gz#進(jìn)入Test-Simple目錄進(jìn)行安裝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；需要確定已經(jīng)安裝PostgreSQL環(huán)境；即pg_config輸出加載PostgreSQL環(huán)境變量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在網(wǎng)站/Bucardo/下載tar-zxvfBucardo-5.6.0.tar.gzcdBucardo-5.6.0perlMakefile.PLmakemakeinstall#查看Bucardo版本[postgres@node3~]$bucardo--versionbucardoversion5.6.05、創(chuàng)建bucardo元數(shù)據(jù)庫創(chuàng)建.bucardorc文件在系統(tǒng)用戶home目錄下創(chuàng)建；內(nèi)容如下：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創(chuàng)建所需的目錄及數(shù)據(jù)庫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管理庫準(zhǔn)備工作完成；開始執(zhí)行bucardoinstall命令創(chuàng)建元數(shù)據(jù)庫；#/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套接字方式連接數(shù)據(jù);可以使用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配置同步（主節(jié)點(diǎn)postgre用戶操作）6.1添加復(fù)制數(shù)據(jù)庫（在db1上執(zhí)行操作）執(zhí)行bucardoadddB命令；記錄同步數(shù)據(jù)庫的連接信息bucardoadddbdb01host=1dbname=postgresport=5432user=postgrespass=123456bucardoadddbdb02host=2dbname=postgresport=5432user=postgrespass=123456添加數(shù)據(jù)庫群組添加dbgroup數(shù)據(jù)庫組；在一套復(fù)制系統(tǒng)中可以有多個(gè)源數(shù)據(jù)庫、目標(biāo)數(shù)據(jù)庫。指定哪個(gè)數(shù)據(jù)庫是源數(shù)據(jù)庫，哪個(gè)是目標(biāo)數(shù)據(jù)庫[postgres@node3~]$bucardoadddbgroupdbgrp01db1:sourcedb2:targetCreateddbgroup"dbgrp01"Addeddatabase"db1"todbgroup"dbgrp01"assourceAddeddatabase"db2"todbgroup"dbgrp01"astarget添加需要同步的表及序列bucardoaddalltablesbucardoaddallsequences添加表群組#創(chuàng)建復(fù)制表tbl_lottuOl[postgres@node3~]$psqlmasterpostgres-c"createtabletbl_lottu01(idintprimarykey,infotext)"CREATETABLE[postgres@node3~]$psqlslavepostgres-c"createtabletbl_lottu01(idintprimarykey,infotext)"CREATETABLE#添加relgroup復(fù)制集；即要復(fù)制的表、序列的集合[postgres@node3~]$bucardoaddrelgrouprelgrp01tbl_lottu01Createdrelgroup"relgrp01"Addedthefollowingtablesorsequences:public.tbl_lottu01(DB:db1)Thefollowingtablesorsequencesarenowpartoftherelgroup"relgrp01":public.tbl_lottu01添加同步添加同步；在創(chuàng)建同步需要指定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"啟動(dòng)bucardobill@docker->bucardostartCheckingforexistingprocessesRe