亚洲国产欧美一区二区三区f,亚洲A∨精品永久无码青草网,亚洲暴爽 av人人爽日日碰,亚洲AV永久无码精心天堂久久_无码

當前位置：首頁 > 數據庫 > PostgreSQL > 詳細頁面

PostgreSQL 邏輯復制配置操作

時間：2021-01-06來源：www.outletmksalestore.com作者：電腦系統城

#### PostgreSQL Logical Replication (邏輯復制) ####

Logical Replication (邏輯復制) 為PostgreSQL自己提供的一種方案,主要是通過一方發布，一方訂閱，來實現彼此共同關注的一種技術。

服務器 | 功能

1
2
3 | 10.10.56.16 | 發布者 P1_pub表 |
| 10.10.56.17 | 發布者 P2_pub表 |
| 10.10.56.19 | 訂閱者 P_sub表 |

背景

一些數據根據業務拆分成一部分P1表、一部分P2表或者多個，A庫操作P1表，B庫操作P2表

現需要可以查詢整個P表，即需要一個p表的匯總庫數據需求，所以通過邏輯復制來實現。

PostgreSQL 安裝環境可見之前的搭建環境教程

分別在3臺服務器 pg_hba.conf 配置文件新增

1 2	`host` `all` `all` `10.10.56.0/0 md5` `host replication` `all` `10.10.56.0/0 trust`

允許在 10.10.56.0 同一網段的服務器所有用戶進行復制、連接等操作

分別在 16、17、19 服務器啟動服務，連接數據庫執行sql,檢查單個數據庫是否為單實例主庫

1
2
3
4
5 pocdb=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)

如上查詢是否為備庫，f 為表示false,即為主庫，反之 t 為從庫

分別在 16、17、19 單實例數據上創建數據庫、用戶、分配權限

1
2
3
4
5
6
7 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 服務器上分別創建 p 表(即表示各自業務的表),并給邏輯復制的用戶 l_repl 分配權限。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 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 服務器上分別創建發布者,即把自己的 p 表發布出去,使訂閱者可以訂閱

1 2	`pocdb=#` `CREATE` `PUBLICATION p_pub` `FOR` `TABLE` `p` `WITH` `(publish =` `'insert,update,delete');` `CREATE` `PUBLICATION`

在19上創建訂閱者,即分別訂閱 16、17 服務器各自發布的表

1 2	`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 服務器插入奇數id 數據

1
2
3
4
5
6
7
8 pocdb=# INSERT INTO p (id,ival) VALUES (1,1);
INSERT 0 1
pocdb=#
pocdb=# select * from p;
id | ival
----+------
1 | 1
(1 row)

17 服務器插入偶數id數據

1
2
3
4 pocdb=# INSERT INTO p (id,ival) VALUES (2,2);
INSERT 0 1
pocdb=#
pocdb=#

注意

在16和17服務器插入數據時，必須主鍵不能沖突，否則會出錯，在實際業務中，插入的數據主鍵永遠不會沖突

此處我們在16服務器插入主鍵為奇數的數據，17為偶數的數據

此時19服務器查看P表匯總的數據

1
2
3
4
5
6 pocdb=# select * from p;
id | ival
----+------
1 | 1
2 | 2
(2 rows)

發現數據已經復制過來，達到我們需要的需求

創建R表

背景

在實際業務需求中，我們可能會有一些數據字典之類的公共表，即現有A、B、C 獨立的數據庫，但是他們都需要共同的字典表 R 表,且R表的入口只能有一個，比如只能在A庫進行寫入，其他庫則需要同步該表，故通過邏輯復制來實現。

1
2
3 pocdb=# CREATE PUBLICATION r_pub FOR TABLE r WITH (publish = 'insert,update,delete,TRUNCATE');
CREATE PUBLICATION
pocdb=#

邏輯復制不支持 TRUNCATE 級聯刪除表數據

R 表的邏輯復制

在16、17、19 分別創建 R表

1	`CREATE` `TABLE` `R (id` `bigint` `,age` `int);`

配置文件上述已經配置，同上，16 發布 R 表，17 與 19 分別訂閱16服務器的R表，即可實現上述業務。

補充：PostgreSQL邏輯復制壓測方案

邏輯復制延遲壓測

本次壓力測試過程基于以上

56.16 –> 56.19 監控延遲腳本

創建腳本 query_logical_lag.sh，并分配權限

1
2
3
4
5
6 #!/bin/bash
/opt/pgsql-10/bin/psql pocdb<<EOF
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

1
2
3
4 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 延遲監測腳本

創建腳本 query_logical_lag2.sh

1
2
3
4
5
6 #!/bin/bash
/opt/pgsql-10/bin/psql pocdb<<EOF
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

分配權限，不然無法執行腳本

1
2
3
4 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服務器執行：

1
2
3
4
5
6 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

表示：執行query_logical_lag.sh腳本 100000次，每執行一次等待5s，把查詢結果記錄到

query_logical_lag1文件

在17服務器執行

1
2
3
4
5
6
7 clw_db2
crontab -e
for i in {1..10000000}
do
/home/postgres/query_logical_lag.sh >> /home/postgres/query_logical_lag2
sleep 5
done

創建sequence，使用sequence保證不同節點插入的數值是奇數或偶數

56.16

1	`create` `sequence` `p_seq1 increment` `by` `2 minvalue 1 maxvalue 100000000000000 cache 50` `no` `cycle;` `----cache是否需要調大`

56.17 服務器創建序列

1	`create` `sequence` `p_seq1 increment` `by` `2 minvalue 2 maxvalue 100000000000000 cache 50` `no` `cycle;`

16、17 服務器分別創建壓測腳本 bench_script_for_insert.sql

1
2
3 \sleep 500ms
\set ival random(1, 500000000)
INSERT INTO p(id, ival) VALUES (nextval('p_seq1'),:ival);

16、17分別執行 pgbench 壓測命令

1	`/opt/pgsql-10/bin/pgbench -c 150 -j 120 -T 600 -f /pgdata/10/poc/scripts/bench_script_for_insert.sql pocdb`

nmon監控cpu、內存、網絡

1 2	`chmod +x /home/super/pgsoft/nmon_x86_64_sles11` `/home/super/pgsoft/nmon_x86_64_sles11 -f -c 120 -s 10`

注：

-f 將結果存儲在當前目錄的文件中，以nmon結尾，自動生成

-c 總共統計120次

-s 每隔10s統計一次

1
2
3
4 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

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。

相關信息

2022-02-25
系統城教小白如何在Centos8-stream安裝PostgreSQL13
2021-04-22
自定義函數實現單詞排序并運用于PostgreSQL(實現代碼)
2021-04-19
MySQL命令行操作時的編碼問題詳解

Postgresql設置遠程訪問的方法(需要設置防火墻或者關閉防火墻)
Postgresql設置遠程訪問的方法(需要設置防火墻或者關閉防火墻)...

2021-03-21
Win10玩游戲輸入法自動彈出怎么辦?(附解決辦法)
玩游戲的時候最怕跳出什么程序來干擾游戲，很多玩家在玩游戲過程中會遇到輸入法彈出來，影響游戲體驗，Win10玩游戲老跳出來輸入法怎么辦？按照以下的方法操作就可以解決這個問題了...

2021-02-19

熱門系統總排行

裝機必備

系統教程欄目

欄目熱門教程

人氣教程排行

站長推薦

熱門系統下載

亚洲国产欧美一区二区三区f,亚洲A∨精品永久无码青草网,亚洲暴爽 av人人爽日日碰,亚洲AV永久无码精心天堂久久_无码日本少妇又色又爽又高潮