1、物理復(fù)制及邏輯復(fù)制對(duì)比
前文做了PostgreSQL物理復(fù)制的部署,其有如下主要優(yōu)點(diǎn)
- 物理層面完全一致，是主要的復(fù)制方式，其類似于Oracle的DG。
- 延遲低，事務(wù)執(zhí)行過程中產(chǎn)生REDO record，實(shí)時(shí)的在備庫apply，事務(wù)結(jié)束時(shí)，備庫立馬能見到數(shù)據(jù)。
- 物理復(fù)制的一致性、可靠性高，不必?fù)?dān)心數(shù)據(jù)邏輯層面不一致。
但是其又在實(shí)際使用的場景中存在一些無法滿足的需求，例如：
- 無法滿足指定庫或部分表的復(fù)制需求。
- 將多個(gè)數(shù)據(jù)庫實(shí)例的數(shù)據(jù)匯聚到同一個(gè)目標(biāo)庫或?qū)⒁粋€(gè)庫的數(shù)據(jù)分發(fā)到多個(gè)不同的庫。
- 不同的版本之間的復(fù)制。
- 不同庫名之間的不同步。
對(duì)于以上場景,物理復(fù)制是無法滿足的，因此邏輯復(fù)制應(yīng)運(yùn)而生了。
邏輯復(fù)制的復(fù)制架構(gòu)圖如下：
圖片來源于《PostgreSQL實(shí)戰(zhàn)》
邏輯復(fù)制是基于邏輯解析，其核心原理是邏輯主庫將Publication中表的WAL日志解析成一定格式并發(fā)送給邏輯備庫，邏輯備庫Subscription接收到解析后的WAL日志后進(jìn)行重做，從而實(shí)現(xiàn)表數(shù)據(jù)同步。
2、邏輯復(fù)制的部署
PS：以下的邏輯從庫可以在新的機(jī)器上部署，如在原先的從庫上修改，需停止原實(shí)例，并將recovery.conf文件刪除或重命名（如修改為recovery.conf.done）
（1）修改主庫的postgresql.conf
可以基于原先的物理復(fù)制的配置文件進(jìn)行修改,配置邏輯復(fù)制主要需調(diào)整如下參數(shù)
wal_level = logical
max_wal_senders = 10
max_replication_slots = 8
參數(shù)簡要說明如下
- wal_level：設(shè)置成logical才支持邏輯復(fù)制.
- max_wal_senders：由于每個(gè)訂閱節(jié)點(diǎn)和流復(fù)制備庫在主庫上都會(huì)占用主庫上一個(gè)WAL發(fā)送進(jìn)程，因此此參數(shù)設(shè)置值需大于max_replication_slots參數(shù)值加上物理備庫數(shù)量。
- max_replication_slots：設(shè)置值需大于訂閱節(jié)點(diǎn)的數(shù)量。
（2）修改邏輯從庫的postgresql.conf
邏輯從庫的postgresql.conf也可以在物理復(fù)制的基礎(chǔ)上修改，與主庫不同的是主要修改如下參數(shù)
wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8
參數(shù)簡要說明：
- wal_level：設(shè)置成logical才支持邏輯復(fù)制,邏輯從庫可以視情況設(shè)置。
- max_replication_slots：設(shè)置數(shù)據(jù)庫復(fù)制槽數(shù)量，應(yīng)大于訂閱節(jié)點(diǎn)的數(shù)量。
- max_logical_replication_workers：設(shè)置邏輯復(fù)制進(jìn)程數(shù)，應(yīng)大于訂閱節(jié)點(diǎn)的數(shù)量，并且給表同步預(yù)留一些進(jìn)程數(shù)量，此參數(shù)默認(rèn)值為4。
（3）創(chuàng)建邏輯復(fù)制賬號(hào)
postgres=# CREATE USER logical_repl REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'logical_repl';
CREATE ROLE
邏輯復(fù)制用戶需要REPLICATION權(quán)限即可，可以不需要SUPERUSER權(quán)限，之后需要在發(fā)布節(jié)點(diǎn)上將需要同步的表賦權(quán)給logical_repl用戶，使logical_repl賬號(hào)具有對(duì)這些表的讀權(quán)限。
（4）在邏輯主庫上創(chuàng)建庫及表
/** 創(chuàng)建用于邏輯復(fù)制的主庫 */
[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));
（5）邏輯從庫上創(chuàng)建庫及表
/** 在邏輯從庫上創(chuàng)建不同的庫 */
[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=#
注：邏輯復(fù)制的表結(jié)構(gòu)需要手動(dòng)在從庫創(chuàng)建
（6）在邏輯主庫上創(chuàng)建發(fā)布
/** 在發(fā)布主庫上創(chuàng)建發(fā)布pub1,注意實(shí)在sourcedb庫下執(zhí)行 */
sourcedb=# CREATE PUBLICATION pub1 FOR TABLE logical_tb1;
CREATE PUBLICATION
如果需發(fā)布多張表 則表名間用逗號(hào)（,）分割，如果需發(fā)布所有庫，則將FOR TABLE 調(diào)整為FOR ALL TABLES。
此時(shí)可以查看到如下信息。
sourcedb=# SELECT * FROM pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
---------+----------+--------------+-----------+-----------+-----------+-------------
pub1 | 10 | f | t | t | t | t
(1 row)
字段說明如下：
- pubname：指發(fā)布的名稱。
- pubowner：指發(fā)布的屬主，可以和pg_user視圖的usesysid字段關(guān)聯(lián)查詢得到屬主具體信息。
- puballtables：是否發(fā)布數(shù)據(jù)庫中的所有表，t表示發(fā)布數(shù)據(jù)庫中所有已存在的表和以后新建的表。
- pubinsert：t表示僅發(fā)布表上的INSERT操作。
- pubupdate：t表示僅發(fā)布表上的UPDATE操作。
- pubdelete：t表示僅發(fā)布表上的DELETE操作。
（7）在邏輯從庫上創(chuàng)建訂閱
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
創(chuàng)建成功后，可以在邏輯主庫上查詢到如下信息：
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)
（8）給復(fù)制賬號(hào)授權(quán)
因?yàn)椴襟E2.3中只創(chuàng)建了復(fù)制賬號(hào)并未對(duì)需要復(fù)制的表進(jìn)行授權(quán)，因此，此時(shí)邏輯從庫的日志中有如下錯(cuò)誤
2019-10-10 15:57:21.847 CST [27443] ERROR: could not start initial contents copy for table "public.logical_tb1": ERROR: permission denied for table logical_tb1
2019-10-10 15:57:21.848 CST [24722] LOG: background worker "logical replication worker" (PID 27443) exited with exit code 1
因此 ，現(xiàn)在需要對(duì)復(fù)制用戶授權(quán)。
sourcedb=# GRANT USAGE ON SCHEMA public TO logical_repl;
GRANT
sourcedb=# GRANT SELECT ON logical_tb1 TO logical_repl;
GRANT
授權(quán)后,顯示正常。
2019-10-10 16:00:25.959 CST [28204] LOG: logical replication table synchronization worker for subscription "sub1", table "logical_tb1" has started
2019-10-10 16:00:25.967 CST [28204] LOG: logical replication table synchronization worker for subscription "sub1", table "logical_tb1" has finished
（9）測試數(shù)據(jù)同步
在邏輯主庫插入數(shù)據(jù)。
/** 在主庫插入數(shù)據(jù) */
sourcedb=# insert into logical_tb1(id,name) values(1,'a'),(2,'bca');
INSERT 0 2
在邏輯從庫查看結(jié)果。
/** 查看數(shù)據(jù)是否同步完成 */
desdb=# select * from logical_tb1;
id | name
----+------
1 | a
2 | bca
(2 rows)
由此可見數(shù)據(jù)已同步完成。
（10）添加復(fù)制所需的表
在邏輯主庫和邏輯從庫均添加一張新表，并添加到發(fā)布列表中。
/** 主庫上創(chuàng)建表結(jié)構(gòu) */
sourcedb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE
sourcedb=#
/** 從庫上創(chuàng)建表結(jié)構(gòu) */
desdb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE
/** 在主庫上給邏輯復(fù)制賬號(hào)授權(quán) */
sourcedb=# GRANT SELECT ON logical_tb2 TO logical_repl;
GRANT
/** 添加新表至發(fā)布列表 */
sourcedb=# ALTER PUBLICATION pub1 ADD TABLE logical_tb2;
ALTER PUBLICATION
/** 在主庫查看發(fā)布列表中的表名 */
sourcedb=# SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename
---------+------------+-------------
pub1 | public | logical_tb1
pub1 | public | logical_tb2
(2 rows)
此時(shí)已加入一張表進(jìn)入發(fā)布列表中。
此時(shí)在主庫寫入數(shù)據(jù)，查看從庫情況如下：
/** 主庫插入一條記錄 */
sourcedb=# insert into logical_tb2(id,addr) values(1,'beijing');
INSERT 0 1
/** 此時(shí)在邏輯從庫查看，結(jié)果卻沒有數(shù)據(jù) */
desdb=# select * from logical_tb2;
id | addr
----+------
(0 rows)
因?yàn)檫€需要在從庫刷新一下訂閱。
/** 此時(shí)在從庫刷新訂閱 */
desdb=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
/** 刷新完成后再查數(shù)據(jù)已經(jīng)有數(shù)據(jù)了 */
ALTER SUBSCRIPTION
desdb=# select * from logical_tb2;
id | addr
----+---------
1 | beijing
(1 row)
至此，PostgreSQL的邏輯復(fù)制也部署完畢。
3、物理復(fù)制與邏輯復(fù)制特點(diǎn)和應(yīng)用場景
PostgreSQL的邏輯復(fù)制與物理復(fù)制的差異比較突出，在使用中可以根據(jù)其特點(diǎn)選擇使用哪種復(fù)制方式。
- 邏輯訂閱，適合于發(fā)布端與訂閱端都有讀寫的情況。
- 邏輯訂閱，更適合于小事務(wù)，或者低密度寫（輕度寫）的同步。如果有大事務(wù)、高密度寫，邏輯訂閱的延遲相比物理復(fù)制更高。
- 邏輯訂閱，適合于雙向，多向同步。
- 物理復(fù)制，適合于單向同步。
- 物理復(fù)制，適合于任意事務(wù)，任意密度寫（重度寫）的同步。
- 物理復(fù)制，適合于HA、容災(zāi)、讀寫分離。
- 物理復(fù)制，適合于備庫沒有寫，只有讀的場景。
