postgresql逻辑复制的搭建

postgresql有流式物理复制和逻辑主从复制，前者复制延时低，但是备库只读。而后者备库可读写，但是有较大的局限性，只能部分复制，数据容易出现不一致等问题。

一、逻辑复制优缺点比较

缺点：逻辑复制比较容易出错，效率也比较低，容易造成主从数据不一致的问题。而且只做部分复制，如表级别复制，而不是整个集群的块级别一致复制

优势：备库可以读写，还可以指定的某个库或者某张表进行复制。同时也可以将的不同库的不同步同步复制到同一个数据库

二、逻辑复制的搭建

1.参数要求，wal_level值要为logical,修改该参数需要重启服务

## 修改发布节点参数
vi postgresql.conf

wal_level = logical				# minimal, replica, or logical
max_replication_slots = 10		# max number of replication slots

## 修改订阅节点参数
max_replication_slots = 10      # max number of replication slots
max_logical_replication_workers = 4    # taken from max_worker_processes

2.在发布节点中创建逻辑复制用户

-- 创建测试数据库
postgres=# create database test;
CREATE DATABASE

postgres=# \c test
You are now connected to database "test" as user "postgres".

-- 创建用户
test=# create user logical_user replication login connection limit 10 encrypted password 'logical_user';
CREATE ROLE

-- 创建测试表和插入数据
test=# create table aa(id int);
CREATE TABLE

test=# insert into aa values(1),(2),(3);
INSERT 0 3

-- 创建发布
test=# create publication pub_test for table aa;
CREATE PUBLICATION

-- 查看发布信息
test=# select * from pg_publication;
 pubname  | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
----------+----------+--------------+-----------+-----------+-----------+-------------
 pub_test |       10 | f            | t         | t         | t         | t
(1 row)

-- 用户授权（不进行授权会出现初始化错误，permission denied）
test=# grant usage on schema public to logical_user;
GRANT

test=# grant select on aa to logical_user;
GRANT

3.在订阅节点上进行关联

-- 创建test数据库
postgres=# create database test;
CREATE DATABASE

test=# create table aa(id int);
CREATE TABLE

-- 创建订阅
test=# create subscription sub_test connection 'host=172.16.0.105 port=5432 dbname=test user=logical_user password=logical_user' publication pub_test;
NOTICE:  created replication slot "sub_test" on publisher
CREATE SUBSCRIPTION

-- 查看订阅信息
test=# select * from pg_subscription;
-[ RECORD 1 ]---+--------------------------------------------------------------------------------
subdbid         | 49326
subname         | sub_test
subowner        | 10
subenabled      | t
subconninfo     | host=172.16.0.105 port=5432 dbname=test user=logical_user password=logical_user
subslotname     | sub_test
subsynccommit   | off
subpublications | {pub_test}

4.查看逻辑复制情况

-- 查看pg_replication_slots表信息
test=# select * from pg_replication_slots;
-[ RECORD 1 ]-------+----------
slot_name           | sub_test
plugin              | pgoutput
slot_type           | logical
datoid              | 41215
database            | test
temporary           | f
active              | t
active_pid          | 15770
xmin                |
catalog_xmin        | 660
restart_lsn         | 0/C059B78
confirmed_flush_lsn | 0/C059BB0

-- 查看日志信息
LOG:  logical replication table synchronization worker for subscription "sub_test", table "aa" has started
LOG:  logical replication table synchronization worker for subscription "sub_test", table "aa" has finished

5.发布和订阅其他操作

-- 添加发布表
alter publication pub_test add table bb;

-- 刷新订阅信息
alter subscription sub_test refresh publication;

-- 停止逻辑复制
alter subscription pub_test disable;

-- 启动逻辑复制
alter subscription pub_test enable;

参考文档的来源：https://blog.youkuaiyun.com/weixin_49756466/article/details/121666795