官網地址
一、Bucardo介紹
Bucardo 是基于表復制的系統
Bucardo 可以實作PostgreSQL資料庫的雙master/多master的方案
Bucardo的核心是一個Perl守護行程，它偵聽通知請求并對其進行操作，方法是連接到遠程資料庫并來回復制資料，
守護行程需要的所有特定資訊都存盤在主bucardo資料庫中，包括復制所涉及的所有資料庫的串列以及如何到達這些資料庫、要復制的所有表以及如何復制每個表，
運行Bucardo的第一步是向主Bucardo資料庫添加兩個或更多資料庫，
完成此操作后，將添加關于要復制哪些表的資訊以及表的任何分組，然后添加同步，
同步被稱為復制操作，將一組特定的表從一臺服務器復制到另一臺服務器或一組服務器，
一旦設定好Bucardo，觸發器就開始存盤有關所有相關的表中哪些行被更改的資訊，對于多主機，
程序是這樣的：
1、對表進行更改并記錄在bucardo_delta表中，
2、向主Bucardo守護行程發送通知，讓它知道表已經更改，
3、守護行程通知控制器進行同步，然后回傳偵聽，
4、控制器創建一個“kid”來處理復制，或者通知已經存在的復制，
5、孩子開始一個新的事務，禁用相關表上的觸發器和規則，
6、然后，它收集自上次復制以來哪些行發生了更改的串列，然后比較這兩個行以確定應該做什么，
7、如果存在沖突，則會運行標準沖突處理程式，或者為每個表設定的自定義處理程式來對內容進行排序，
8、觸發器和規則被重新啟用，事務被提交，
9、如果事務失敗，則運行任何自定義例外處理程式，
10、子程式向控制器發出它已經完成的信號，
二、Bucardo 的安裝
2.1、安裝依賴包
Bucardo 軟體依賴包有如下：
build, test, and install Perl 5 (at least 5.8.3)
build, test, and install Postgres (at least 8.2)
build, test, and install the DBI module (at least 1.51)
build, test, and install the DBD::Pg module (at least 2.0.0)
build, test, and install the DBIx::Safe module (at least 1.2.4)
1、安裝 Perl
本次系統linux版本是6.5 Perl是linux系統自帶；默認版本是v5.10.1；可行
[root@node3 Bucardo]# perl -v
This is perl, v5.10.1 (*) built for x86_64-linux-thread-multi
2、安裝 PostgreSQL軟體
在編譯的程序需要帶上 --with-perl;
3、安裝 DBI module
安裝DBI 依賴 Test-Simple；
# 安裝 Test-Simple
Test-Simple下載地址 https://metacpan.org 搜索 Test-Simple
# 解壓Test-Simple原始碼包
tar -zxvf Test-Simple-1.302183.tar.gz
# 進入Test-Simple目錄進行安裝
cd Test-Simple-1.302183
perl Makefile.PL
make
make install
# 接下來安裝DBI
# 下載地址：http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.630.tar.gz
tar -zxvf DBI-1.630.tar.gz
cd DBI-1.630
perl Makefile.PL
make
make install
4、 安裝DBD::Pg module
安裝最新的DBD::Pg；需要依賴 Time::HiRes
# 安裝 Time::HiRes
下載地址： https://metacpan.org 搜索 Time::HiRes
tar -zxvf Time-HiRes-1.9764.tar.gz
cd Time-HiRes-1.9764
perl Makefile.PL
make
make install
# 接下來安裝DBD::Pg
# 安裝DBD::Pg；需要確定已經安裝 PostgreSQL 環境；即pg_config輸出
# 加載 PostgreSQL 環境變數
tar -zxvf DBD-Pg-3.14.2.tar.gz
cd DBD-Pg-3.14.2
perl Makefile.PL
make
make install
5、安裝DBIx::Safe
# 下載地址：https://bucardo.org/DBIx-Safe/
tar -zxvf DBIx-Safe-1.2.5.tar.gz
cd DBIx-Safe-1.2.5
perl Makefile.PL
make
make install
2.2、安裝 Bucardo
# 在網站https://bucardo.org/Bucardo/下載
tar -zxvf Bucardo-5.6.0.tar.gz
cd Bucardo-5.6.0
perl Makefile.PL
make
make install
# 查看Bucardo版本
[postgres@node3 ~]$ bucardo --version
bucardo version 5.6.0
三、創建元資料庫bucardo
元資料庫存盤bucardo復制的配置資訊,使用 bucardo install 命令創建；
3.1、創建.bucardorc檔案
在系統用戶home目錄下創建；內容如下：
log_conflict_file = /home/postgres/bucardo/log/bucardo_conflict.log
piddir = /home/postgres/bucardo/run
reason_file = /home/postgres/bucardo/log/bucardo.restart.reason.log
warning_file = /home/postgres/bucardo/log/bucardo.warning.log
syslog_facility = LOG_LOCAL1
3.2、創建所需目錄
mkdir -p /home/postgres/bucardo/log
mkdir -p /home/postgres/bucardo/run
3.3、執行 bucardo install 命令
準備作業完成；開始執行 bucardo install 命令創建 元資料庫;
# /usr/local/share/bucardo/bucardo.schema
[postgres@node3 ~]$ bucardo install
This will install the bucardo database into an existing Postgres cluster.
Postgres must have been compiled with Perl support,
and you must connect as a superuser
Current connection settings:
1. Host: <none>
2. Port: 6000
3. User: bucardo
4. Database: bucardo
5. PID directory: /home/postgres/bucardo/run
Enter a number to change it, P to proceed, or Q to quit: 1
-- 推薦使用unix套接字方式連接資料;可以使用dbuser:postgres;dbname:postgres去連接，
Change the host to: /tmp
Changed host to: /tmp
Current connection settings:
1. Host: /tmp
2. Port: 6000
3. User: postgres
4. Database: postgres
5. PID directory: /home/postgres/bucardo/run
Enter a number to change it, P to proceed, or Q to quit: P
Attempting to create and populate the bucardo database and schema
Database creation is complete
Updated configuration setting "piddir"
Installation is now complete.
If you see errors or need help, please email [email protected]
You may want to check over the configuration variables next, by running:
bucardo show all
Change any setting by using: bucardo set foo=bar
四、Bucardo配置同步
本次簡單演示下；在同個資料庫服務器下不同的實體；
用資料庫master、slave來演示
[postgres@node3 ~]$ createdb master
[postgres@node3 ~]$ createdb slave
4.1、Add databases
執行bucardo add db命令；記錄同步資料庫的連接資訊
[postgres@node3 ~]$ bucardo add db db1 host=/tmp dbname=master user=postgres
Added database "db1"
[postgres@node3 ~]$ bucardo add db db2 host=/tmp dbname=slave user=postgres
Added database "db2"
#這里推薦使用unix套接字；若元資料庫跟需要復制的資料不是在同一臺;則需要使用TCP/IP連接的方式，
bucardo add db db1 host=IP port=PGPORT dbname=PGDB user=PGUSRR password=PGPASSWD
4.2、add dbgroup
添加 dbgroup 資料庫組； 在一套復制系統中可以有多個源資料庫、目標資料庫，指定哪個資料庫是源資料庫，哪個是目標資料庫
[postgres@node3 ~]$ bucardo add dbgroup dbgrp01 db1:source db2:target
Created dbgroup "dbgrp01"
Added database "db1" to dbgroup "dbgrp01" as source
Added database "db2" to dbgroup "dbgrp01" as target
4.3、add relgroup
# 創建復制表tbl_lottu01
[postgres@node3 ~]$ psql master postgres -c "create table tbl_lottu01(id int primary key, info text)"
CREATE TABLE
[postgres@node3 ~]$ psql slave postgres -c "create table tbl_lottu01(id int primary key, info text)"
CREATE TABLE
# 添加 relgroup 復制集；即要復制的表、序列的集合
[postgres@node3 ~]$ bucardo add relgroup relgrp01 tbl_lottu01
Created relgroup "relgrp01"
Added the following tables or sequences:
public.tbl_lottu01 (DB: db1)
The following tables or sequences are now part of the relgroup "relgrp01":
public.tbl_lottu01
4.4、Add syncs
添加同步；在創建同步需要指定dbgroup 和relgroup
[postgres@node3 ~]$ bucardo add sync sync01 relgroup=relgrp01 dbgroup=dbgrp01 conflict_strategy=bucardo_latest
2020-11-03 17:08:51.494 CST [6988] LOG: Starting validate_sync for sync01
2020-11-03 17:08:51.494 CST [6988] CONTEXT: PL/Perl function "validate_sync"
SQL statement "SELECT validate_sync('sync01')"
PL/Perl function "validate_sync"
2020-11-03 17:08:51.494 CST [6988] STATEMENT: INSERT INTO bucardo.sync (herd,name,dbs,conflict_strategy) VALUES ($1,$2,$3,$4)
2020-11-03 17:08:51.619 CST [6988] LOG: Ending validate_sync for sync01
2020-11-03 17:08:51.619 CST [6988] CONTEXT: PL/Perl function "validate_sync"
SQL statement "SELECT validate_sync('sync01')"
PL/Perl function "validate_sync"
2020-11-03 17:08:51.619 CST [6988] STATEMENT: INSERT INTO bucardo.sync (herd,name,dbs,conflict_strategy) VALUES ($1,$2,$3,$4)
Added sync "sync01"
由于bucardo是解決PostgreSQL雙主/多主的方案，可能會存在主鍵/唯一約束的沖突
"conflict_strategy"
The conflict resolution strategy to use in the sync. Supported
values:
"bucardo_source"
The rows on the "source" database always "win". In other words,
in a conflict, Bucardo copies rows from source to target.
"bucardo_target"
The rows on the "target" database always win.
"bucardo_skip"
Any conflicting rows are simply not replicated. Not recommended
for most cases.
"bucardo_random"
Each database has an equal chance of winning each time. This is
the default.
"bucardo_latest"
The row that was most recently changed wins.
"bucardo_abort"
The sync is aborted on a conflict.
4.5、test sync
啟動bucardo服務
[postgres@node3 ~]$ bucardo start
Checking for existing processes
Starting Bucardo
[postgres@node3 ~]$ 2020-11-03 17:21:07.440 CST [7178] LOG: Starting validate_sync for sync01
2020-11-03 17:21:07.440 CST [7178] CONTEXT: PL/Perl function "validate_sync"
2020-11-03 17:21:07.440 CST [7178] STATEMENT: SELECT validate_sync('sync01')
2020-11-03 17:21:07.494 CST [7178] LOG: Ending validate_sync for sync01
2020-11-03 17:21:07.494 CST [7178] CONTEXT: PL/Perl function "validate_sync"
2020-11-03 17:21:07.494 CST [7178] STATEMENT: SELECT validate_sync('sync01')
[postgres@node3 ~]$ ps -ef | grep bucardo
postgres 7178 6376 0 17:21 ? 00:00:00 postgres: bucardo bucardo [local] idle
postgres 7184 6376 0 17:21 ? 00:00:00 postgres: bucardo bucardo [local] idle
postgres 7187 6376 0 17:21 ? 00:00:00 postgres: bucardo bucardo [local] idle
postgres 7191 6376 0 17:21 ? 00:00:00 postgres: bucardo bucardo [local] idle
postgres 7196 7066 0 17:21 pts/1 00:00:00 grep bucardo
在源庫插入記錄
[postgres@node3 ~]$ psql master postgres
psql (12.2)
Type "help" for help.
master=# insert into tbl_lottu01 values (1, 'lottu01');
INSERT 0 1
master=# insert into tbl_lottu01 values (2, 'lottu02');
INSERT 0 1
master=# \q
在目標庫查看記錄
[postgres@node3 ~]$ psql slave postgres
psql (12.2)
Type "help" for help.
slave=# select * from tbl_lottu01;
id | info
----+---------
1 | lottu01
2 | lottu02
(2 rows)
五、Bucardo應用場景
5.1、雙master架構
Bucardo 可以實作PostgreSQL資料庫的雙master/多master的方案；
其實邏輯復制可以設計雙主架構；在原生邏輯流復制針對沖突檢查需要手動處理；但是在pglogical有對沖突處理，bucardo也有沖突處理，
以上面為例
# 添加資料庫組；指定db2是源資料庫，db1是目標資料庫
bucardo add dbgroup dbgrp02 db2:source db1:target
# 添加同步
bucardo add sync sync02 relgroup=relgrp01 dbgroup=dbgrp02 conflict_strategy=bucardo_latest
這樣可以完成雙主架構
master=# insert into tbl_lottu01 values (3, 'lottu03');
INSERT 0 1
slave=# insert into tbl_lottu01 values (4, 'lottu04');
INSERT 0 1
查看master/slave結果是
=# select * from tbl_lottu01;
id | info
----+---------
1 | lottu01
2 | lottu02
4 | lottu04
3 | lottu03
(4 rows)
5.2、測驗資料同步
在生產環境資料庫；都應有測驗環境；有時需要在測驗環境需要最新的資料驗證新加的代碼正確性，
# 關閉bucardo服務
bucardo stop
# 更新同步為增量同步；
bucardo update sync sync01 onetimecopy=2
"onetimecopy"
0: 關閉
1: fullcopy；采用delete/copy的方式
2: 增量copy；
# 啟動bucardo服務
bucardo start
六、bucardo日常維護
參考《PostgreSQL修煉之道》第二版
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/202457.html
標籤：PostgreSQL
下一篇：redis五種資料型別的應用