PostgreSQL 逻辑复制搭建

前言

本篇文章介绍 PostgreSQL 的搭建过程，原理以及维护相关内容将在下一篇文章中介绍。

1. 环境准备

1.1 环境介绍

操作系统：CentOS Linux release 7.8.2003 (Core)

1.2 发布端参数配置

postgresql.conf 中的设置：

# 数据库监听
listen_addresses = '*'

# WAL 的等级，若要开启逻辑复制，必须设置 logical 让数据库记录逻辑解码所需的信息，所以低于该级别逻辑复制不能工作。
wal_level = logical

# 数据库复制槽的数量
max_replication_slots = 8

# 由于每个订阅都会占用主库一个 WAL 发送进程，因此参数设置值必须大于 max_replication_slots + 物理备库数量
max_wal_senders = 10
1
2
3
4
5
6
7
8
9
10
11

pg_hba.conf 中的设置：

host    all             replc           172.16.104.56/22        md5
1

上面这行配置的含义是，允许用户 replc 从 172.16.104.56/22 的网络上发起到本数据库的流复制连接，使用 MD5 的密码认证。

1.3 订阅端参数配置

postgresql.conf 中的设置：

# 数据库监听
listen_addresses = '*'

# WAL 的等级，若要开启逻辑复制，必须设置 logical 让数据库记录逻辑解码所需的信息，所以低于该级别逻辑复制不能工作。
wal_level = logical

# 数据库复制槽的数量，
max_replication_slots = 8

# 设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量。
max_logical_replication_workers = 10
1
2
3
4
5
6
7
8
9
10
11

2. 逻辑复制搭建

2.1 创建逻辑复制用户

在发布端创建逻辑复制专用用户：

create user replc replication login connection limit 8 password 'rep';
1

用于逻辑复制的用户，必须是 replication 角色，或是 superuser 角色。

2.2 发布节点造测试表

创建一个库专用于逻辑复制搭建测试：

postgres=# create database pubdb;
CREATE DATABASE

postgres=# \c pubdb replc;
You are now connected to database "pubdb" as user "replc".

pubdb=> create table tt(id int4 primary key, name text);
CREATE TABLE

pubdb=> insert into tt values (1, 'a');
INSERT 0 1
1
2
3
4
5
6
7
8
9
10
11

2.3 发布节点授权

刚才创建的逻辑复制用户，仅拥有 replication 角色权限还不够，需要授予表的只读权限：

# 要创建发布，必须要拥有该库的 create 权限
grant connect, create  on database pubdb to replc;

grant usage on schema public to replc;

grant select on tt to replc;
1
2
3
4
5
6

现在 replc 用户已经拥有 tt 测试表的只读权限。

2.4 创建 PUBLICATION

发布端创建发布任务：

create PUBLICATION pub1 FOR TABLE tt;
1

如果需要多张表，则表名间使用逗号分隔，如果需要发布所有表，将 FOR TABLE 调整为 FOR ALL TABLES 即可。查看发布任务可以使用下方 SQL：

select * from pg_publication;
1

输出的结果：

  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------
 24876 | pub1    |    24866 | f            | t         | t         | t         | t
1
2
3

• pubname：发布任务名称。
• pubowner：指发布属组，可以和 pg_user 视图的 usesysid 相关联。
• puballtables：是否发布数据库中所有表，t 表示发布数据库中所有已存在的表和以后新建的表。
• pubinsert：如果为 t 表示发布表中的 INSERT 操作。
• pubupdate：如果为 t 表示发布表中的 UPDATE 操作。
• pubdelete：如果为 t 表示发布表中的 DELETE 操作。
• pubtruncate：如果为 t 表示发布表中的 TRUNCATE 操作。

2.5 订阅节点

# 创建订阅节点的库名
create database subdb;

# 创建用户
create user replc replication login connection limit 8 password 'rep';

# 切换至 replc
\c subdb replc

# 创建表
create table tt(id int4 primary key, name text);

# 创建订阅任务，注意该步骤必须使用 superuser 角色账号
create subscription sub1 connection 'host=172.16.104.7 port=5432 dbname=pubdb user=replc password=rep' PUBLICATION pub1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

执行结果：

subdb=# create subscription sub1 connection 'host=172.16.104.7 port=5432 dbname=pubdb user=replc password=rep' PUBLICATION pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
1
2
3

此时在订阅节点可以查到订阅任务信息：

select * from pg_subscription;
1

  oid  | subdbid | subname | subowner | subenabled |                           subconninfo                            | subslotname | subsynccommit | subpublications 
-------+---------+---------+----------+------------+------------------------------------------------------------------+-------------+---------------+-----------------
 24887 |   24878 | sub1    |       10 | t          | host=172.16.104.7 port=5432 dbname=pubdb user=replc password=rep | sub1        | off           | {pub1}
1
2
3

创建成功后，就可以在发布端查到任务运行情况：

select * from pg_replication_slots;
1

 slot_name |  plugin  | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn 
-----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
 sub1      | pgoutput | logical   |  24867 | pubdb    | f         | t      |     108314 |      |          796 | 6/7000270   | 6/70002A8
1
2
3

2.6 添加复制表

经过上面的操作，我们已经成功配置 tt 表的发布与订阅，假设此时又有新需求，再添加一张表，如何操作呢？

首先，在发布节点创建测试表：

# 创建测试表
create table t2(id int4 primary key, name text);

# 写入测试数据
insert into t2 values (1, 'a'),(2, 'b'),(3, 'c');
1
2
3
4
5

将该表授权给 replc 账号：

grant select on t2 to replc;
1

将该表添加到发布列表：

ALTER PUBLICATION pub1 ADD TABLE t2;
1

查看发布任务表信息：

select * from pg_publication_tables;
1

输出：

 pubname | schemaname | tablename 
---------+------------+-----------
 pub1    | public     | tt
 pub1    | public     | t2
1
2
3
4

现在开始配置订阅端：

# 创建表结构
create table t2(id int4 primary key, name text);
1
2

订阅端执行刷新任务操作：

ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
1

订阅端查看数据：

subdb=# select * from t2;
 id | name 
----+------
  1 | a
  2 | b
  3 | c
1
2
3
4
5
6

至此，表 t2 已成功添加到逻辑复制任务中。

2.7 删除复制任务

DROP SUBSCRIPTION sub1;
1