#### PostgreSQL Logical Replication (邏輯復(fù)制) ####
Logical Replication (邏輯復(fù)制) 為PostgreSQL自己提供的一種方案,主要是通過(guò)一方 發(fā)布，一方訂閱，來(lái)實(shí)現(xiàn)彼此共同關(guān)注的一種技術(shù)。
服務(wù)器 | 功能
| 10.10.56.16 | 發(fā)布者 P1_pub表 | | 10.10.56.17 | 發(fā)布者 P2_pub表 | | 10.10.56.19 | 訂閱者 P_sub表 |
一些數(shù)據(jù)根據(jù)業(yè)務(wù)拆分成一部分P1表、一部分P2表或者多個(gè)，A庫(kù)操作P1表，B庫(kù)操作P2表
現(xiàn)需要可以查詢整個(gè)P表，即需要一個(gè)p表的匯總庫(kù) 數(shù)據(jù)需求，所以通過(guò)邏輯復(fù)制來(lái)實(shí)現(xiàn)。
PostgreSQL 安裝環(huán)境可見(jiàn)之前的搭建環(huán)境教程
分別在3臺(tái)服務(wù)器 pg_hba.conf 配置文件新增
host all all 10.10.56.0/0 md5 host replication all 10.10.56.0/0 trust
允許在 10.10.56.0 同一網(wǎng)段的服務(wù)器所有用戶 進(jìn)行復(fù)制、連接等操作
分別在 16、17、19 服務(wù)器啟動(dòng)服務(wù)，連接數(shù)據(jù)庫(kù)執(zhí)行sql,檢查單個(gè)數(shù)據(jù)庫(kù)是否為單實(shí)例主庫(kù)
pocdb=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row)
如上查詢是否為備庫(kù) ，f 為表示false,即為主庫(kù)，反之 t 為從庫(kù)
分別在 16、17、19 單實(shí)例數(shù)據(jù)上創(chuàng)建數(shù)據(jù)庫(kù)、用戶、分配權(quán)限
postgres@clw-db3:/home/postgres> /opt/pgsql-10/bin/psql -p 5432 postgres psql (10.3) Type "help" for help. postgres=# create database pocdb; CREATE DATABASE pocdb=# CREATE USER l_repl PASSWORD '123456' REPLICATION; CREATE ROLE
在 16、17 服務(wù)器上分別創(chuàng)建 p 表(即表示各自業(yè)務(wù)的表),并給邏輯復(fù)制的用戶 l_repl 分配權(quán)限。
pocdb=# CREATE TABLE p(id bigint primary key,ival int);
CREATE TABLE
pocdb=# GRANT ALL ON p TO l_repl;
pocdb=#
pocdb=#
pocdb=#
pocdb=#
pocdb=# \d+ p
Table "public.p"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | bigint | | not null | | plain | |
ival | integer | | | | plain | |
Indexes:
"p_pkey" PRIMARY KEY, btree (id)
在 16、17 服務(wù)器上分別創(chuàng)建發(fā)布者,即把自己的 p 表發(fā)布出去,使 訂閱者 可以訂閱
pocdb=# CREATE PUBLICATION p_pub FOR TABLE p WITH (publish = 'insert,update,delete'); CREATE PUBLICATION
在19上創(chuàng)建訂閱者,即分別訂閱 16、17 服務(wù)器各自發(fā)布的表
CREATE SUBSCRIPTION p1_sub CONNECTION 'host=10.10.56.16 port=5432 user=l_repl dbname=pocdb password=123456' PUBLICATION p_pub; CREATE SUBSCRIPTION p2_sub CONNECTION 'host=10.10.56.17 port=5432 user=l_repl dbname=pocdb password=123456' PUBLICATION p_pub;
16 服務(wù)器插入奇數(shù)id 數(shù)據(jù)
pocdb=# INSERT INTO p (id,ival) VALUES (1,1); INSERT 0 1 pocdb=# pocdb=# select * from p; id | ival ----+------ 1 | 1 (1 row)
17 服務(wù)器插入偶數(shù)id數(shù)據(jù)
pocdb=# INSERT INTO p (id,ival) VALUES (2,2); INSERT 0 1 pocdb=# pocdb=#
注 意
在16和17服務(wù)器插入數(shù)據(jù)時(shí)，必須主鍵不能沖突，否則會(huì)出錯(cuò)，在實(shí)際業(yè)務(wù)中，插入的數(shù)據(jù)主鍵永遠(yuǎn)不會(huì)沖突
此處我們?cè)?6服務(wù)器插入主鍵為 奇數(shù) 的數(shù)據(jù)，17為 偶數(shù) 的數(shù)據(jù)
此時(shí)19服務(wù)器查看P表匯總的數(shù)據(jù)
pocdb=# select * from p; id | ival ----+------ 1 | 1 2 | 2 (2 rows)
發(fā)現(xiàn)數(shù)據(jù)已經(jīng)復(fù)制過(guò)來(lái)，達(dá)到我們需要的需求
背 景
在實(shí)際業(yè)務(wù)需求中，我們可能會(huì)有一些數(shù)據(jù)字典之類的公共表，即現(xiàn)有A、B、C 獨(dú)立的數(shù)據(jù)庫(kù)，但是他們都需要共同的字典表 R 表,且R表的入口只能有一個(gè)，比如只能在A庫(kù)進(jìn)行寫入，其他庫(kù)則需要同步該表，故通過(guò)邏輯復(fù)制來(lái)實(shí)現(xiàn)。
pocdb=# CREATE PUBLICATION r_pub FOR TABLE r WITH (publish = 'insert,update,delete,TRUNCATE'); CREATE PUBLICATION pocdb=#
邏輯復(fù)制不支持 TRUNCATE 級(jí)聯(lián)刪除表數(shù)據(jù)
在16、17、19 分別創(chuàng)建 R表
CREATE TABLE R (id bigint ,age int);
配置文件上述已經(jīng)配置，同上，16 發(fā)布 R 表，17 與 19 分別 訂閱16服務(wù)器的R表，即可實(shí)現(xiàn)上述業(yè)務(wù)。
補(bǔ)充：PostgreSQL邏輯復(fù)制壓測(cè)方案
本次壓力測(cè)試過(guò)程基于以上
56.16 –> 56.19 監(jiān)控延遲腳本
創(chuàng)建腳本 query_logical_lag.sh，并分配權(quán)限
#!/bin/bash /opt/pgsql-10/bin/psql pocdbEOF select now(); select client_addr, application_name, write_lag, flush_lag, replay_lag from pg_stat_replication where usename='l_repl' and application_name='p1_sub'; \q EOF
postgres@clw-db2:~> chmod +x query_logical_lag2.sh postgres@clw-db2:~> ls -l total 4 -rwxr-xr-x 1 postgres postgres 218 May 8 16:49 query_logical_lag2.sh
56.17 –> 56.19 延遲監(jiān)測(cè)腳本
創(chuàng)建腳本 query_logical_lag2.sh
#!/bin/bash /opt/pgsql-10/bin/psql pocdbEOF select now(); select client_addr, application_name, write_lag, flush_lag, replay_lag from pg_stat_replication where usename='l_repl' and application_name='p2_sub'; \q EOF
分配權(quán)限，不然無(wú)法執(zhí)行腳本
postgres@clw-db2:~> chmod +x query_logical_lag2.sh postgres@clw-db2:~> ls -l total 4 -rwxr-xr-x 1 postgres postgres 218 May 8 16:49 query_logical_lag2.sh
在16服務(wù)器執(zhí)行：
clw_db1
postgres@clw-db1:~> for i in {1..100000}
> do
> /home/postgres/query_logical_lag.sh >> /home/postgres/query_logical_lag1
> sleep 5
> done
表示：執(zhí)行query_logical_lag.sh腳本 100000次，每執(zhí)行一次等待5s，把查詢結(jié)果記錄到
query_logical_lag1文件
在17服務(wù)器執(zhí)行
clw_db2
crontab -e
for i in {1..10000000}
do
/home/postgres/query_logical_lag.sh >> /home/postgres/query_logical_lag2
sleep 5
done
創(chuàng)建sequence，使用sequence保證不同節(jié)點(diǎn)插入的數(shù)值是奇數(shù)或偶數(shù)
56.16
create sequence p_seq1 increment by 2 minvalue 1 maxvalue 100000000000000 cache 50 no cycle; ----cache是否需要調(diào)大
56.17 服務(wù)器創(chuàng)建序列
create sequence p_seq1 increment by 2 minvalue 2 maxvalue 100000000000000 cache 50 no cycle;
16、17 服務(wù)器分別創(chuàng)建壓測(cè)腳本 bench_script_for_insert.sql
\sleep 500ms
\set ival random(1, 500000000)
INSERT INTO p(id, ival) VALUES (nextval('p_seq1'),:ival);
16、17分別執(zhí)行 pgbench 壓測(cè)命令
/opt/pgsql-10/bin/pgbench -c 150 -j 120 -T 600 -f /pgdata/10/poc/scripts/bench_script_for_insert.sql pocdb
nmon監(jiān)控cpu、內(nèi)存、網(wǎng)絡(luò)
chmod +x /home/super/pgsoft/nmon_x86_64_sles11 /home/super/pgsoft/nmon_x86_64_sles11 -f -c 120 -s 10
注：
-f 將結(jié)果存儲(chǔ)在當(dāng)前目錄的文件中，以nmon結(jié)尾，自動(dòng)生成
-c 總共統(tǒng)計(jì)120次
-s 每隔10s統(tǒng)計(jì)一次
chmod +x /home/pgsoft/nmon_x86_64_sles11 chmod +x /home/pgsoft/nmon_x86_64_sles11 /home/pgsoft/nmon_x86_64_sles11 -f -c 120 -s 10 /home/super/pgsoft/nmon_x86_64_sles11 -f -c 120 -s 10
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
標(biāo)簽：烏海 晉城 蚌埠 來(lái)賓 衡陽(yáng) 錦州 株洲 珠海
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《PostgreSQL 邏輯復(fù)制 配置操作》，本文關(guān)鍵詞 PostgreSQL,邏輯,復(fù)制,配置,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
