日本综合一区二区|亚洲中文天堂综合|日韩欧美自拍一区|男女精品天堂一区|欧美自拍第6页亚洲成人精品一区|亚洲黄色天堂一区二区成人|超碰91偷拍第一页|日韩av夜夜嗨中文字幕|久久蜜综合视频官网|精美人妻一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時間：8:30-17:00
你可能遇到了下面的問題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
PostgreSQL主從復(fù)制-邏輯復(fù)制

1、物理復(fù)制及邏輯復(fù)制對比

前文做了PostgreSQL物理復(fù)制的部署,其有如下主要優(yōu)點

  • 物理層面完全一致，是主要的復(fù)制方式，其類似于Oracle的DG。
  • 延遲低，事務(wù)執(zhí)行過程中產(chǎn)生REDO record，實時的在備庫apply，事務(wù)結(jié)束時，備庫立馬能見到數(shù)據(jù)。
  • 物理復(fù)制的一致性、可靠性高，不必擔(dān)心數(shù)據(jù)邏輯層面不一致。

但是其又在實際使用的場景中存在一些無法滿足的需求，例如：

  • 無法滿足指定庫或部分表的復(fù)制需求。
  • 將多個數(shù)據(jù)庫實例的數(shù)據(jù)匯聚到同一個目標庫或?qū)⒁粋€庫的數(shù)據(jù)分發(fā)到多個不同的庫。
  • 不同的版本之間的復(fù)制。
  • 不同庫名之間的不同步。

對于以上場景,物理復(fù)制是無法滿足的，因此邏輯復(fù)制應(yīng)運而生了。

邏輯復(fù)制的復(fù)制架構(gòu)圖如下：

圖片來源于《PostgreSQL實戰(zhàn)》

邏輯復(fù)制是基于邏輯解析，其核心原理是邏輯主庫將Publication中表的WAL日志解析成一定格式并發(fā)送給邏輯備庫，邏輯備庫Subscription接收到解析后的WAL日志后進行重做，從而實現(xiàn)表數(shù)據(jù)同步。

2、邏輯復(fù)制的部署

PS：以下的邏輯從庫可以在新的機器上部署，如在原先的從庫上修改，需停止原實例，并將recovery.conf文件刪除或重命名（如修改為recovery.conf.done）

（1）修改主庫的postgresql.conf

可以基于原先的物理復(fù)制的配置文件進行修改,配置邏輯復(fù)制主要需調(diào)整如下參數(shù)

wal_level = logical
max_wal_senders = 10
max_replication_slots = 8

參數(shù)簡要說明如下

  • wal_level：設(shè)置成logical才支持邏輯復(fù)制.
  • max_wal_senders：由于每個訂閱節(jié)點和流復(fù)制備庫在主庫上都會占用主庫上一個WAL發(fā)送進程，因此此參數(shù)設(shè)置值需大于max_replication_slots參數(shù)值加上物理備庫數(shù)量。
  • max_replication_slots：設(shè)置值需大于訂閱節(jié)點的數(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é)點的數(shù)量。
  • max_logical_replication_workers：設(shè)置邏輯復(fù)制進程數(shù)，應(yīng)大于訂閱節(jié)點的數(shù)量，并且給表同步預(yù)留一些進程數(shù)量，此參數(shù)默認值為4。

（3）創(chuàng)建邏輯復(fù)制賬號

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é)點上將需要同步的表賦權(quán)給logical_repl用戶，使logical_repl賬號具有對這些表的讀權(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)需要手動在從庫創(chuàng)建

（6）在邏輯主庫上創(chuàng)建發(fā)布

/** 在發(fā)布主庫上創(chuàng)建發(fā)布pub1,注意實在sourcedb庫下執(zhí)行 */
 sourcedb=# CREATE PUBLICATION pub1 FOR TABLE logical_tb1; 
CREATE PUBLICATION

如果需發(fā)布多張表 則表名間用逗號（,）分割，如果需發(fā)布所有庫，則將FOR TABLE 調(diào)整為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：指發(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ù)制賬號授權(quán)

因為步驟2.3中只創(chuàng)建了復(fù)制賬號并未對需要復(fù)制的表進行授權(quán)，因此，此時邏輯從庫的日志中有如下錯誤

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)在需要對復(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ù)制賬號授權(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)

此時已加入一張表進入發(fā)布列表中。

此時在主庫寫入數(shù)據(jù)，查看從庫情況如下：

/** 主庫插入一條記錄 */ 
sourcedb=# insert into logical_tb2(id,addr) values(1,'beijing');
INSERT 0 1 
/** 此時在邏輯從庫查看，結(jié)果卻沒有數(shù)據(jù) */
desdb=# select  * from logical_tb2;
 id | addr
----+------
(0 rows)

因為還需要在從庫刷新一下訂閱。

/** 此時在從庫刷新訂閱 */ 
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ù)制特點和應(yīng)用場景

PostgreSQL的邏輯復(fù)制與物理復(fù)制的差異比較突出，在使用中可以根據(jù)其特點選擇使用哪種復(fù)制方式。

  • 邏輯訂閱，適合于發(fā)布端與訂閱端都有讀寫的情況。
  • 邏輯訂閱，更適合于小事務(wù)，或者低密度寫（輕度寫）的同步。如果有大事務(wù)、高密度寫，邏輯訂閱的延遲相比物理復(fù)制更高。
  • 邏輯訂閱，適合于雙向，多向同步。
  • 物理復(fù)制，適合于單向同步。
  • 物理復(fù)制，適合于任意事務(wù)，任意密度寫（重度寫）的同步。
  • 物理復(fù)制，適合于HA、容災(zāi)、讀寫分離。
  • 物理復(fù)制，適合于備庫沒有寫，只有讀的場景。

本文標題：PostgreSQL主從復(fù)制-邏輯復(fù)制
鏈接URL：http://www.dlmjj.cn/article/dhhcodj.html