日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

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

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

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

但是其又在實(shí)際使用的場(chǎng)景中存在一些無(wú)法滿足的需求，例如：

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

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

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

圖片來(lái)源于《PostgreSQL實(shí)戰(zhàn)》

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

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

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

（1）修改主庫(kù)的postgresql.conf

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

wal_level = logical
max_wal_senders = 10
max_replication_slots = 8

參數(shù)簡(jiǎn)要說(shuō)明如下

• wal_level：設(shè)置成logical才支持邏輯復(fù)制.
• max_wal_senders：由于每個(gè)訂閱節(jié)點(diǎn)和流復(fù)制備庫(kù)在主庫(kù)上都會(huì)占用主庫(kù)上一個(gè)WAL發(fā)送進(jìn)程，因此此參數(shù)設(shè)置值需大于max_replication_slots參數(shù)值加上物理備庫(kù)數(shù)量。
• max_replication_slots：設(shè)置值需大于訂閱節(jié)點(diǎn)的數(shù)量。

（2）修改邏輯從庫(kù)的postgresql.conf

邏輯從庫(kù)的postgresql.conf也可以在物理復(fù)制的基礎(chǔ)上修改，與主庫(kù)不同的是主要修改如下參數(shù)

wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

參數(shù)簡(jiǎn)要說(shuō)明：

• wal_level：設(shè)置成logical才支持邏輯復(fù)制,邏輯從庫(kù)可以視情況設(shè)置。
• max_replication_slots：設(shè)置數(shù)據(jù)庫(kù)復(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）在邏輯主庫(kù)上創(chuàng)建庫(kù)及表

/** 創(chuàng)建用于邏輯復(fù)制的主庫(kù) */ 
[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）邏輯從庫(kù)上創(chuàng)建庫(kù)及表

/** 在邏輯從庫(kù)上創(chuàng)建不同的庫(kù) */
[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)在從庫(kù)創(chuàng)建

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

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

如果需發(fā)布多張表 則表名間用逗號(hào)（,）分割，如果需發(fā)布所有庫(kù)，則將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)

字段說(shuō)明如下：

• pubname：指發(fā)布的名稱。
• pubowner：指發(fā)布的屬主，可以和pg_user視圖的usesysid字段關(guān)聯(lián)查詢得到屬主具體信息。
• puballtables：是否發(fā)布數(shù)據(jù)庫(kù)中的所有表，t表示發(fā)布數(shù)據(jù)庫(kù)中所有已存在的表和以后新建的表。
• pubinsert：t表示僅發(fā)布表上的INSERT操作。
• pubupdate：t表示僅發(fā)布表上的UPDATE操作。
• pubdelete：t表示僅發(fā)布表上的DELETE操作。

（7）在邏輯從庫(kù)上創(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)建成功后，可以在邏輯主庫(kù)上查詢到如下信息：

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)

在邏輯從庫(kù)上可以查詢到如下信息：

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í)邏輯從庫(kù)的日志中有如下錯(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）測(cè)試數(shù)據(jù)同步

在邏輯主庫(kù)插入數(shù)據(jù)。

/** 在主庫(kù)插入數(shù)據(jù) */ 
sourcedb=# insert into logical_tb1(id,name) values(1,'a'),(2,'bca');
INSERT 0 2

在邏輯從庫(kù)查看結(jié)果。

/** 查看數(shù)據(jù)是否同步完成 */ 
desdb=# select * from logical_tb1;
 id | name
----+------
  1 | a
  2 | bca
(2 rows)

由此可見數(shù)據(jù)已同步完成。

（10）添加復(fù)制所需的表

在邏輯主庫(kù)和邏輯從庫(kù)均添加一張新表，并添加到發(fā)布列表中。

/** 主庫(kù)上創(chuàng)建表結(jié)構(gòu) */ 
sourcedb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE 
sourcedb=#
/** 從庫(kù)上創(chuàng)建表結(jié)構(gòu) */
desdb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE 
/** 在主庫(kù)上給邏輯復(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
/** 在主庫(kù)查看發(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í)在主庫(kù)寫入數(shù)據(jù)，查看從庫(kù)情況如下：

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

因?yàn)檫€需要在從庫(kù)刷新一下訂閱。

/** 此時(shí)在從庫(kù)刷新訂閱 */ 
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)用場(chǎng)景

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

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

文章名稱：PostgreSQL主從復(fù)制-邏輯復(fù)制
文章路徑：http://www.5511xx.com/article/dhhcodj.html