postgresql從庫搭建–邏輯複製
- 2019 年 10 月 10 日
- 筆記
1 物理複製及邏輯複製對比
前文做了PostgreSQL物理複製的部署,其有如下主要優點
- 物理層面完全一致，是主要的複製方式，其類似於Oracle的DG
- 延遲低，事務執行過程中產生REDO record，實時的在備庫apply，事務結束時，備庫立馬能見到數據
- 物理複製的一致性、可靠性高，不必擔心數據邏輯層面不一致
但是其又在實際使用的場景中存在一些無法滿足的需求，例如：
- 無法滿足指定庫或部分表的複製需求
- 將多個資料庫實例的數據匯聚到同一個目標庫或將一個庫的數據分發到多個不同的庫
- 不同的版本之間的複製
- 不同庫名之間的表同步
對於以上場景,物理複製時無法滿足的，因此邏輯複製應運而生了。
邏輯複製的複製架構圖如下：
圖片來源於《PostgreSQL實戰》
邏輯複製是基於邏輯解析，其核心原理是邏輯主庫將Publication中表的WAL日誌解析成一定格式並發送給邏輯備庫，邏輯備庫Subscription接收到解析後的WAL日誌後進行重做，從而實現表數據同步。
2. 邏輯複製的部署
PS： 以下的邏輯從庫可以在新的機器上部署，如在原先的從庫上修改，需停止原實例，並將recovery.conf文件刪除或重命名（如修改為recovery.conf.done）
2.1 修改主庫的postgresql.conf
可以基於原先的物理複製的配置文件進行修改,配置邏輯複製主要需調整如下參數
wal_level = logical max_wal_senders = 10 max_replication_slots = 8
參數簡要說明如下
- wal_level：設置成logical才支援邏輯複製
- max_wal_senders：由於每個訂閱節點和流複製備庫在主庫上都會佔用主庫上一個WAL發送進程，因此此參數設置值需大於max_replication_slots參數值加上物理備庫數量
- max_replication_slots：設置值需大於訂閱節點的數量
2.2 修改邏輯從庫的postgresql.conf
邏輯從庫的postgresql.conf也可以在物理複製的基礎上修改，與主庫不同的是主要修改如下參數
wal_level = logical max_replication_slots = 8 max_logical_replication_workers = 8
參數簡要說明
- wal_level：設置成logical才支援邏輯複製,邏輯從庫可以視情況設置
- max_replication_slots：設置資料庫複製槽數量，應大於訂閱節點的數量
- max_logical_replication_workers：設置邏輯複製進程數，應大於訂閱節點的數量，並且給表同步預留一些進程數量，此參數默認值為4
2.3 創建邏輯複製帳號
postgres=# CREATE USER logical_repl REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'logical_repl';
CREATE ROLE
邏輯複製用戶需要REPLICATION許可權即可，可以不需要SUPERUSER許可權，之後需要在發布節點上將需要同步的表賦權給logical_repl用戶，使logical_repl帳號具有對這些表的讀許可權。
2.4 在邏輯主庫上創建庫及表
/** 創建用於邏輯複製的主庫 */
[postgres@PG32 data]$ createdb sourcedb
[postgres@PG32 data]$ psql -d sourcedb
psql (11.4)
Type "help" for help.
sourcedb=# create table logical_tb1(id int primary key,name varchar(20));
2.5 邏輯從庫上創建庫及表
/** 在邏輯從庫上創建不同的庫 */
[postgres@PG33 data]$ createdb desdb
[postgres@PG33 data]$ psql -d desdb
psql (11.4)
Type "help" for help.
desdb=# create table logical_tb1(id int primary key,name varchar(20));
CREATE TABLE
desdb=#
註：邏輯複製的表結構需要手動在從庫創建
2.6 在邏輯主庫上創建發布
/** 在發布主庫上創建發布pub1,注意實在sourcedb庫下執行 */ sourcedb=# CREATE PUBLICATION pub1 FOR TABLE logical_tb1;
CREATE PUBLICATION
如果需發布多張表 則表名間用逗號（,）分割，如果需發布所有庫，則將FOR TABLE 調整為FOR ALL TABLES。
此時可以查看到如下資訊
sourcedb=# SELECT * FROM pg_publication; pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate ---------+----------+--------------+-----------+-----------+-----------+------------- pub1 | 10 | f | t | t | t | t (1 row)
欄位說明如下：
- pubname： 指發布的名稱
- pubowner： 指發布的屬主，可以和pg_user視圖的usesysid欄位關聯查詢得到屬主具體資訊
- puballtables：是否發布資料庫中的所有表，t表示發布資料庫中所有已存在的表和以後新建的表
- pubinsert： t表示僅發布表上的INSERT操作
- pubupdate： t表示僅發布表上的UPDATE操作
- pubdelete： t表示僅發布表上的DELETE操作
2.7 在邏輯從庫上創建訂閱
desdb=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.56.32 port=5432 user=logical_repl dbname=sourcedb password=logical_repl' PUBLICATION pub1; NOTICE: created replication slot "sub1" on publisher CREATE SUBSCRIPTION
創建成功後，可以在邏輯主庫上查詢到如下資訊：
sourcedb=# 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 | sourcedb | t | 0/6022D30 (1 row)
在邏輯從庫上可以查詢到如下資訊：
desdb=# SELECT * FROM pg_subscription; subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications ---------+---------+----------+------------+---------------------------------------------------------------------------------------+-------------+---------------+----------------- 24995 | sub1 | 10 | t | host=192.168.56.32 port=5432 user=logical_repl dbname=sourcedb password=logical_repl | sub1 | off | {pub1} (1 row)