postgresql 逻辑复制配置实战

一、概述

Postgresql流复制是物理复制技术，跟Oracle ADG一样，要求主备数据库在数据块级完全一样，这样就大大保证了数据同步的低延迟和数据一致性。但是在某些场景下 ，物理复制技术并不适用，
比如基于库级或者表级的复制、多对一汇总的数据仓库、要求备机可写等。
Postgresql逻辑复制技术基于WAL日志解析实现，可实现表级同步，并且可以跨版本同步数据。

二、逻辑复制相关概念

PG逻辑复制基于发布和订阅模型，一个发布可以有多个订阅者。
发布publication可以创建在任意单机上，也可以在物理复制的主库上，发布端要求数据库参数WAL_LEVEL为logical。一个发布可以被多个数据库订阅。一个发布只能包含一个数据库中的表，但是这些表可以分布在不同的schema下。
订阅处于逻辑复制的下游端，一个订阅只能对应一个发布。订阅端的数据库名可以跟发布端不同，但是订阅发布的表名和schema必须相同。
当订阅创建成功后，就会自动在发布端创建多个逻辑复制槽，其中一个用于后续的增量数据同步，永久保留，另外还会创建一个或者多个的临时复制槽，用于复制表的全量快照数据。
是当发布端是物理复制架构时，如果发生主从切换，发布是不会跟随数据库failover的。但是可以通过手工拷贝文件或者利用插件实现。

2.1 逻辑复制的限制
（1）数据库的表结构不会被复制，所以需要在订阅端先创建对应的schema和表
（2）DDL不会不复制，如果要进行DDL变更，需要先在订阅端执行DDL，然后在发布端执行。
（3）Sequence不会不复制，当发布端使用了sequence数据，其写入了表的值会同步到订阅端，但是目标库的sequence并不会发生变化。
（4）不支持大对象。
（5）复制只能是基表到基表，不支持视图、物化视图、外部表等。支持DDL，如果表是分区表，需要基于分区进行复制。

2.2 逻辑复制相关配置参数

wal_level：必须设置为"logical"，让WAL日志文件中记录逻辑解码所需的信息，低于这个级别，逻辑复制不能工作。
max_wal_senders (integer):指定来自备用服务器或者流式基本备份客户端的最大并发连接数。默认值为10，为0时表示禁止复制。应将此参数设置为略高于预期客户端的最大数量。
max_replication_slots (integer):指定服务器可以支持的最大复制插槽数。默认值为"10"
wal_sender_timeout (integer):不活动的复制连接的时间超过这个参数指定的毫秒数，就会被终止掉。这对于发送服务器检测备用崩溃或者网络中断很有用。0值将禁用超时机制，默认值为60秒。
track_commit_timestamp (boolean):记录事务的提交时间，默认值为off。

2.3 逻辑复制构建
需要进行逻辑复制的表，要求最好是有主键或者唯一索引。为了能够复制UPDATE和DELETE操作，被发布的表必须配置有一个"复制标识"，这样在订阅者那一端才能标识对于更新或删除合适的行。
默认情况下，复制标识就是主键，如果既没有主键也没有唯一索引，就需要配置复制标识为full

复制标识配置：

Alter table table_name
REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

Default表示使用主键，默认标识，可以不设置；

Using index index_name表示使用唯一索引；

Full 表示表中无主键和唯一索引，full标识影响复制效率；

Noting 标识的表不能进行delete和update复制。

2.4 逻辑复制相关视图和监控

发布端：
pg_publication
pg_publication_tables;
pg_stat_replications
pg_replication_slots

订阅端：
pg_subscription
pg_stat_subscription
pg_subscription_rel

三、搭建实战
发布端：192.168.1.6
订阅端：192.168.1.7

3.1

发布端配置参数：
vi pg_hba.conf

listen_addresses = '*'
wal_level = logical
max_replication_slots = 8  ###设置数据库复制槽的数量，值必须大于订阅的数量
max_wal_senders = 10       ###设置值必须大于max_replication_slots + 物理备库数

vi pg_hba.conf
host    rep             all             192.168.1.0/24           md5   

发布节点创建逻辑复制用户和授权：
postgres=# create user rep replication login connection limit 8 password 'rep';
CREATE ROLE
postgres=# create database pubdb;
CREATE DATABASE
pubdb=# GRANT ALL PRIVILEGES ON DATABASE  pubdb to rep;
GRANT

pubdb=# grant connect on database pubdb to rep;
GRANT
pubdb=# grant usage on schema public to rep;
GRANT
pubdb=# grant select on tt to rep;
GRANT
pubdb=# 


postgres=# \c pubdb rep
You are now connected to database "pubdb" as user "rep".
pubdb=> create table tt(id int4 primary key,name text);
CREATE TABLE
pubdb=> insert into tt values (1,'a');
INSERT 0 1
pubdb=> create publication pub1 for table tt;
CREATE PUBLICATION

如果需要发布多张表，用逗号分隔，为所有表发布：for all tables

--查看创建的发布
pubdb=> select * from pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 16393 | pub1    |    16384 | f            | t         | t         | t         | t           | f
(1 row)

--主库查看发布的列表
pubdb=> select * from pg_publication_tables;
 pubname | schemaname | tablename 
---------+------------+-----------
 pub1    | public     | tt

订阅端配置参数：
vi pg_hba.conf
listen_addresses = '*'
wal_level = logical
max_replication_slots = 8            ###设置数据库复制槽的数量，值必须大于订阅的数量
max_logical_replication_workers = 8  ###设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量。max_logical_replication_workers会消耗后台进程数，并且从 max_worker_processes 参数设置的后台进程数消费，因此max_worker_processes要设置大一些

订阅节点创建接收表的命令：
postgres=# create user rep replication login connection limit 8 password 'rep';
CREATE ROLE

postgres=# create database subdb;
CREATE DATABASE
postgres=# \c subdb rep;
You are now connected to database "subdb" as user "rep".
subdb=> create table tt(id int4 primary key,name text);
CREATE TABLE

订阅节点创建订阅的命令如下：
subdb=> \c subdb postgres
You are now connected to database "subdb" as user "postgres".
subdb=# 
subdb=# create subscription sub1 connection 'host=192.168.1.6 port=5432 dbname=pubdb user=rep password=rep' publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

--查看订阅
subdb=# select * from pg_subscription;
  oid  | subdbid | subname | subowner | subenabled | subbinary | substream |                          subconninfo                          | subslotname | subsynccommit | subpublications 
-------+---------+---------+----------+------------+-----------+-----------+---------------------------------------------------------------+-------------+---------------+-----------------
 16394 |   16386 | sub1    |       10 | t          | f         | f         | host=192.168.1.6 port=5432 dbname=pubdb user=rep password=rep | sub1        | off           | {pub1}
(1 row)

subdb=# grant connect on database subdb to rep;
GRANT
subdb=# grant usage on schema public to rep;
GRANT
subdb=# grant select on tt to rep;
GRANT

--创建成功后，可以在发布节点查询如下信息：
postgres=# 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   | pubdb    | t      | 0/A03D540
(1 row)


四、测试同步：
发布节点插入数据：
postgres=# \c pubdb
You are now connected to database "pubdb" as user "postgres".
pubdb=# insert into tt values (2,'tt');
INSERT 0 1
pubdb=# select * from tt;
 id | name 
----+------
  1 | a
  2 | tt
(2 rows)


订阅节点查看：
subdb=# select * from tt;
 id | name 
----+------
  1 | a
  2 | tt
(2 rows)

发布节点删除数据：
pubdb=# delete from tt where id=1;
DELETE 1
pubdb=# select * from tt;
 id | name 
----+------
  2 | tt
(1 row)


订阅节点查看：
subdb=# select * from tt;
 id | name 
----+------
  2 | tt
(1 row)

五、添加需要复制的表
在逻辑主库和从库均添加一张新表，并添加到发布列表中。

发布节点创建表：
postgres=# \c pubdb rep
You are now connected to database "pubdb" as user "rep".
pubdb=> create table tb(id int primary key ,addr varchar(100));
CREATE TABLE

主库上给逻辑复制账号授权：
pubdb=> grant select on tb to rep;
GRANT

添加新表至发布列表：
pubdb=> alter publication pub1 add table tb;
ALTER PUBLICATION

--主库查看发布的列表
pubdb=> select * from pg_publication_tables;
 pubname | schemaname | tablename 
---------+------------+-----------
 pub1    | public     | tt
 pub1    | public     | tb



从库节点创建表：
postgres=# \c subdb rep
You are now connected to database "subdb" as user "rep".
subdb=> create table tb(id int primary key ,addr varchar(100));
CREATE TABLE

--主库插入记录
pubdb=> insert into tb values (1,'beijing');
INSERT 0 1
pubdb=> select * from tb;
 id |  addr   
----+---------
  1 | beijing
(1 row)

--从库查看
subdb=> select * from tb;
 id | addr 
----+------
(0 rows)

需要进行刷新一下订阅，才能查看到数据：
subdb=# alter subscription sub1 refresh publication;
ALTER SUBSCRIPTION
subdb=# select * from tb;
 id |  addr   
----+---------
  1 | beijing
(1 row)