240815-PostgreSQL自帶邏輯複製簡單使用

零の守墓人發表於2024-08-18
SQL

PostgreSQL自帶邏輯複製簡單使用

一、邏輯複製說明

角色 IP 埠 資料庫名 使用者名稱 版本
釋出端 192.168.198.165 8432 pubdb repuser PostgreSQL 13.13
訂閱端 192.168.198.162 8432 subdb repuser PostgreSQL 13.13

二、搭建邏輯複製環境

2.1 釋出端配置

釋出端 postgresql.conf 配置

在釋出端的 postgresql.conf 配置檔案設定一下引數：

listen_addresses = '*'
wal_level = logical
max_replication_slots = 8
max_wal_senders = 10

引數設定說明如下：

  • wal_level：設定成 ‘logical’ 才支援邏輯複製，該引數的含義是，讓資料庫在 WAL 日誌中記錄邏輯解碼所需的更多資訊，低於這個級別邏輯複製不能工作。
  • max_replication_slots：設定值必須大於訂閱的數量。
  • max_wal_senders：由於每個訂閱在主庫都會佔用主庫的一個 WAL 傳送程序，因此引數設定值必須大於max_replication_slots 引數值加上物理備庫數。
釋出端 pg_hba.conf 配置

在釋出端的 pg_hba.conf 配置檔案中設定一下引數：

host    replication     repuser       192.168.198.165/24         md5

含義是：允許用 rep從 192.168.6.22 的網路上發起到本資料庫的流複製連線，使用 md5 密碼認證。

2.2 訂閱端配置

訂閱端postgresql.conf 配置

在訂閱端的 postgresql.conf 配置檔案設定一下引數：

listen_addresses = '*'
wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

引數設定說明如下：

  • max_replication_slots：設定資料庫複製槽數量，應該大於訂閱節點的數量。
  • max_logical_replication_workers：設定邏輯複製程序數，應大於訂閱節點的數量，並且給表同步預留一些程序數量。max_logical_replication_workers 會消耗後臺程序數，並且從 max_worker_precesses 引數設定的後臺程序數中消費，因此 max_worker_precesses 引數需要設定的大些。

2.3 釋出節點建立邏輯複製使用者

2.3.1 建立邏輯複製使用者

釋出節點上的邏輯複製使用者需要具備replication許可權。釋出端建立邏輯複製使用者的命令：

postgres=# create user repuser replication login connection limit 8 password '123456';

注：用於邏輯複製的使用者必須是 replication 角色或 superuser 角色。

2.3.2 為複製表建立釋出

釋出節點為複製表建立釋出的命令如下：

postgres=# create database pubdb;
postgres=# \c pubdb repuser
You are now connected to database "pubdb" as user "repuser".

pubdb=> create table tt(id int4 primary key,name text);
CREATE TABLE

pubdb=> insert into tt values(1,'aa');
INSERT 0 1

--用 postgres 使用者建立釋出
postgres=# \c pubdb fbase
You are now connected to database "pubdb" as user "fbase".

pubdb=# create publication pub1 for table tt;
CREATE PUBLICATION

如果需要釋出多張表，則表名間用逗號（,）分隔，如果需要釋出所有表，則將“for table"調整為“for all tables”。

2.3.3 檢視建立的釋出

命令如下：

pubdb=# select * from pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 16395 | pub1    |       10 | f            | t         | t         | t         | t           | f
(1 row)

配置引數說明如下：

  • pubname：指釋出的名稱。
  • pubowner：指釋出的屬主，可以和 pg_user 檢視的 usesyid 欄位關聯查詢屬主的具體資訊。
  • puballtables：是否釋出資料庫中的所有表，”t“ 表示釋出資料庫中的所有已存在的表和以後新建的表。
  • pubinsert：”t“ 表示僅釋出表上的 insert 操作。
  • pubupdate：”t“ 表示僅釋出表上的 update操作。
  • pubdelete："t" 表示僅釋出表上的 delete 操作。
  • pubtruncate："t" 表示僅釋出表上的 truncate 操作。
2.3.4 釋出節點為複製使用者授權

命令如下：

pubdb=# \c pubdb fbase
You are now connected to database "pubdb" as user "postgres".

pubdb=# grant connect on database pubdb to repuser;
GRANT
pubdb=# grant usage on schema public to repuser;
GRANT
pubdb=# grant select on tt to repuser;
GRANT
2.3.5 訂閱節點建立接收表

命令如下：

[postgres@docker1 ~]$ psql -h 192.168.198.162 -p 8432

postgres=# create database subdb;
CREATE DATABASE
postgres=# create user repuser replication login connection limit 8 password '123456';
CREATE ROLE
postgres=# \c subdb repuser
You are now connected to database "subdb" as user "repuser".

subdb=> create table tt(id int4 primary key,name text);
CREATE TABLE
2.3.6 訂閱節點建立訂閱

命令如下：

subdb=> \c subdb fbase
You are now connected to database "subdb" as user "fbase".

subdb=# create subscription sub1 connection 'host=192.168.198.164 port=8432 dbname=pubdb user=repuser password=123456' publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
2.3.7 檢視訂閱

命令如下：

subdb=# select * from pg_subscription;
  oid  | subdbid | subname | subowner | subenabled |                               subconninfo                                | subslotname 
| subsynccommit | subpublications 
-------+---------+---------+----------+------------+--------------------------------------------------------------------------+-------------
+---------------+-----------------
 16395 |   16385 | sub1    |       10 | t          | host=192.168.198.164 port=8432 dbname=pubdb user=repuser password=123456 | sub1        
| off           | {pub1}
(1 row)
2.3.8 釋出節點為複製使用者授權

命令如下：

subdb=# grant connect on database subdb to repuser;
GRANT
subdb=# grant usage on schema public to repuser;
GRANT
subdb=# grant select on tt to repuser;
GRANT
2.3.9 釋出點查詢連線資訊

建立成功後，可以在釋出節點查詢到以下資訊：

pubdb=# select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots where slot_name='sub1';
 slot_name |  plugin  | slot_type | database | active | restart_lsn 
-----------+----------+-----------+----------+--------+-------------
 sub1      | pgoutput | logical   | pubdb    | t      | 0/C010B18
(1 row)

配置完成後，釋出節點向表中插入、刪除資料

pubdb=> insert into tt values(2,'tt');
INSERT 0 1
pubdb=> delete from tt where id = 1;
DELETE 1

訂閱節點檢視資料：

subdb=# select * from tt;
 id | name 
----+------
  2 | tt
(1 row)
2.3.10 新增複製所需的表

示例如下：

在釋出節點主庫和訂閱節點從庫均新增一張新表，並新增到釋出列表中。

--釋出節點建立表結構，命令如下：
pubdb=> create table tb(id int primary key,addr varchar(100));
CREATE TABLE

--訂閱節點建立表結構，命令如下：
subdb=> create table tb(id int primary key,addr varchar(100));
CREATE TABLE

--在釋出節點中給邏輯複製賬號授權，命令如下：
pubdb=> grant select on tb to repuser;
GRANT

--新增新表到釋出列表
pubdb=> \c pubdb fbase
pubdb=# alter publication pub1 add table tb;
ALTER PUBLICATION

--在釋出節點檢視釋出列表中的表名，命令如下：
pubdb=# select * from pg_publication_tables;
 pubname | schemaname | tablename 
---------+------------+-----------
 pub1    | public     | tt
 pub1    | public     | tb
(2 rows)

此時已將一張表新增到釋出列表中。

--此時釋出節點寫入資料
pubdb=> insert into tb values(1,'beijing');
INSERT 0 1

--訂閱節點檢視資料
subdb=> select * from tb;
 id | addr 
----+------
(0 rows)

--訂閱節點卻沒查到資料，需要在訂閱節點庫重新整理一下訂閱
subdb=> \c subdb fbase
You are now connected to database "subdb" as user "fbase".
subdb=# alter subscription sub1 refresh publication ;
ALTER SUBSCRIPTION

--重新整理完成後檢視，訂閱節點已經有插入的資料了
subdb=# select * from tb;
 id |  addr   
----+---------
  1 | beijing
(1 row)
2.3.11 清除複製設定

訂閱端執行。

命令如下：

subdb=# drop subscription sub1;
NOTICE:  dropped replication slot "sub1" on publisher
DROP SUBSCRIPTION
2.3.12 禁用和啟用訂閱

訂閱端執行。

命令如下：

subdb=# alter subscription sub1 disable;
subdb=# alter subscription sub1 enable;

注意：

​ 兩個或多個釋出端的主鍵不能有重複的內容，並且相同的有唯一約束的欄位不能有重複的內容，否則之後釋出的會報錯，訂閱端發生主鍵或唯一約束衝突，並且停止複製；發生主鍵或唯一約束衝突後，可透過刪除訂閱端造成唯一約束衝突的記錄，然後使用 alter subscription name enable 讓訂閱繼續；

三、 檢視和表介紹

3.1 pg_publication

介紹

pg_publication 是 PostgreSQL 中的一個系統表，用於儲存邏輯複製釋出的後設資料。邏輯複製允許你從一個 PostgreSQL 資料庫（釋出者）向另一個 PostgreSQL 資料庫（訂閱者）複製資料更改。

表的結構

pg_publication 表包含了關於釋出的各種資訊，主要包括以下欄位：

  • pubname: 釋出的名稱。
  • puballtables: 一個布林值，指示該釋出是否包含所有表。
  • pubinsert, pubupdate, pubdelete, pubtruncate: 每個布林欄位分別指示釋出是否包含 INSERT、UPDATE、DELETE 或 TRUNCATE 操作。

操作示例

# 建立釋出
CREATE PUBLICATION mypublication FOR ALL TABLES;
CREATE PUBLICATION mypublication FOR TABLE mytable;
# 檢視釋出
SELECT * FROM pg_publication;
# 修改釋出
ALTER PUBLICATION mypublication ADD TABLE new_table;
ALTER PUBLICATION mypublication DROP TABLE old_table;
# 刪除釋出
DROP PUBLICATION mypublication;

3.2 pg_publication_tables

介紹

pg_publication_tables 是 PostgreSQL 中的一個系統檢視，用於儲存與釋出（publication）相關的表資訊。這個檢視提供了邏輯複製中包含的表的詳細資訊。

檢視的結構

pg_publication_tables 檢視包含了以下欄位：

  • publication: 釋出的名稱。
  • tablename: 表的名稱。
  • schemaname: 表所在的模式名稱。
  • pubinsert, pubupdate, pubdelete, pubtruncate: 每個布林欄位分別指示釋出是否包含 INSERT、UPDATE、DELETE 或 TRUNCATE 操作。

操作示例

# 要檢視特定釋出中的所有表
SELECT * FROM pg_publication_tables WHERE publication = 'your_publication_name';
# 檢視所有釋出中的表
SELECT * FROM pg_publication_tables;

3.3 pg_subscription

pg_subscription 是 PostgreSQL 中的一個系統表，用於儲存邏輯複製訂閱（subscription）的後設資料。邏輯複製允許您從一個 PostgreSQL 資料庫（釋出者）向另一個 PostgreSQL 資料庫（訂閱者）複製資料更改。

表的結構

pg_subscription 表包含了關於訂閱的各種資訊，主要包括以下欄位：

  • subname: 訂閱的名稱。
  • subowner: 訂閱的擁有者。
  • subenabled: 一個布林值，指示該訂閱是否處於啟用狀態。
  • subconninfo: 一個字串，包含用於連線到釋出者的連線資訊。
  • subslotname: 一個字串，包含用於釋出者端複製槽的名稱。
  • subpublications: 一個字串陣列，包含訂閱的釋出名稱列表。
  • subcopydata: 一個布林值，指示訂閱是否複製初始資料。
  • subslot_type: 一個字串，指示覆制槽的型別（通常是 'logical'）。
  • suboptions: 一個字串陣列，包含額外的訂閱選項。

操作示例

# 建立訂閱
CREATE SUBSCRIPTION mysubscription CONNECTION 'host=my_publisher_host dbname=my_publisher_db user=my_publisher_user password=my_publisher_password' PUBLICATION mypublication;
# 檢視訂閱
SELECT * FROM pg_subscription;
# 啟用/禁用訂閱
ALTER SUBSCRIPTION mysubscription ENABLE;
ALTER SUBSCRIPTION mysubscription DISABLE;
# 刪除訂閱
DROP SUBSCRIPTION mysubscription;

3.4 pg_replication_slots

介紹

pg_replication_slots 是 PostgreSQL 中的一個系統表，用於儲存複製槽（replication slot）的資訊。複製槽是持久化的複製起點，它們儲存了複製流的狀態，使得訂閱者能夠從釋出者那裡接收更新的資料。

表的結構

pg_replication_slots 表包含了關於複製槽的各種資訊，主要包括以下欄位：

  • slot_name: 複製槽的名稱。
  • slot_type: 複製槽的型別，通常為 'logical' 或 'physical'。
  • plugin: 所使用的外掛名稱。
  • tmp_slot_name: 如果適用，臨時複製槽的名稱。
  • active: 一個布林值，指示覆制槽是否處於活動狀態。
  • active_pid: 如果複製槽處於活動狀態，這是複製程序的 PID。
  • xmin: 事務 ID 的最小值，用於確定何時可以清理舊的 WAL 檔案。
  • catalog_xmin: 目錄事務 ID 的最小值。
  • restart_lsn: 複製槽的重啟位置，即複製槽的最新位置。
  • confirmed_flush_lsn: 已確認的重新整理位置，即已確認被訂閱者接收到的位置。

操作示例

# 建立複製槽
SELECT * FROM pg_create_logical_replication_slot('myslot', 'test_decoding');
# 檢視複製槽
SELECT * FROM pg_replication_slots;
# 刪除複製槽
SELECT * FROM pg_drop_replication_slot('myslot');

相關文章