PostgreSQL- 逻辑复制 logical replication

主

vim postgresql.conf
wal_level = logical
max_wal_senders = 10
max_replication_slots = 8

CREATE USER logical_repl REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'logical_repl'; 

CREATE PUBLICATION pub1 FOR TABLE logical_tb1; 

SELECT * FROM pg_publication;
GRANT USAGE ON SCHEMA public TO logical_repl;
GRANT SELECT ON logical_tb1 TO logical_repl;

从

wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.56.32 port=5432 user=logical_repl dbname=sourcedb password=logical_repl' PUBLICATION pub1;

确认：
主：

sourcedb=# SELECT slot_name,plugin,slot_type,database,active,restart_lsn    FROM pg_replication_slots where slot_name='sub1';

从

SELECT * FROM pg_subscription;

后期添加多表：

sourcedb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE 
sourcedb=#
/** 从库上创建表结构 */
desdb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE 
/** 在主库上给逻辑复制账号授权 */ 
sourcedb=# GRANT SELECT ON logical_tb2 TO logical_repl;
GRANT
/** 添加新表至发布列表 */ 
sourcedb=# ALTER PUBLICATION pub1 ADD TABLE logical_tb2;
ALTER PUBLICATION
/** 在主库查看发布列表中的表名 */
sourcedb=# SELECT * FROM pg_publication_tables;

因为还需要在从库刷新一下订阅
/** 此时在从库刷新订阅 */ 
desdb=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
/** 刷新完成后再查数据已经有数据了 */ 
ALTER SUBSCRIPTION
desdb=# select  * from logical_tb2;
 id |  addr
----+---------
  1 | beijing
(1 row)

插入数据查看日志。

testdb=# insert into test02 values (11,'a',now());
LOG:  statement: insert into test02 values (11,'a',now());
INSERT 0 1
testdb=# insert into test01 values (31,'a');
LOG:  statement: insert into test01 values (31,'a');
INSERT 0 1

通过 pg_recvlogical 解析获取到的日志。

$ pg_recvlogical -U dbadmin -d testdb -p 5432 -S slot1 --start -f -

BEGIN 2057
table public.test02: INSERT: id[integer]:11 col1[text]:'a' ctime[timestamp without time zone]:'2020-08-27 11:45:49.934037'
COMMIT 2057
BEGIN 2058
table public.test01: INSERT: id[integer]:31 col1[text]:'a'
COMMIT 2058