PostgreSQL部署逻辑复制

1.环境准备

角色	主机名	IP	端口	数据库名	用户名	版本
发布端	postgresql	192.168.80.239	5432	pubdb	replic	postgresql 15
订阅端	postgresql2	192.168.80.240	5432	subdb	replic	postgresql 15

2.发布端配置参数

## vi postgressql.conf（重启生效）
listen_addresses = '*'
wal_level=logical
max_replication_slots=8
max_wal_senders=10

## alter system set
alter system set wal_level=logical;

## 参数说明
wal_level设置为logical，才支持逻辑复制，低于这个级别逻辑复制不能工作。
max_replication_slots设置值必须大于订阅的数量。
max_wal_senders设置值必须大于max_replication_slots参数值加上物理备库数，因为每个订阅在主库上都会占用主库一个wal发送进程。

3.发布端配置pg_hba.conf

vi pg_hba.conf
host    replication     test       0/0         md5

4.订阅端配置参数

## vi postgresql.conf（重启生效）
listen_addresses = '*'
wal_level=logical
max_replication_slots=8
max_logical_replication_workers=8

## alter system set
alter system set wal_level=logical;

## 参数说明
max_replication_slots设置数据库复制槽数量。
max_logical_replication_workers设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量。

注意：max_logical_replication_workers会消耗后台进程数，并且从max_worker_processes参数设置的后台进程数中消费，因此max_worker_processes需要设置的大一些。

5.发布端创建逻辑复制用户，并具备replication复制权限（可选）

如不创建，可以使用默认的管理员用户postgres。

postgres=# create user replic replication login connection limit 8 password 'replic';
CREATE ROLE

limit 8：为新用户设置最大数目连接数。默认无限制。

6.发布端创建发布

## 创建复制数据库
postgres=# create database pubdb;
CREATE DATABASE

## 授予复制用户权限
postgres=# \c pubdb postgres
You are now connected to database "pubdb" as user "postgres".
pubdb=# grant all on schema public to replic;
GRANT

## 创建复制表
pubdb=> create table c1 (id int4 primary key,name text);
CREATE TABLE
pubdb=> insert into c1 values (1,'a');
INSERT 0 1
pubdb=> select * from c1;
 id | name 
----+------
  1 | a
(1 row)

## 创建发布
pubdb=> \c pubdb postgres
You are now connected to database "pubdb" as user "postgres".
pubdb=# create publication pub1 for table c1;
CREATE PUBLICATION

注意：如果发布多张表使用逗号隔开，如果发布所有表则将 for table 修改为 for all tables。

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

参数说明：
pubname：发布名称。
pubowner：发布的属主，可以和pg_user视图的usesysid字段关联查询属主的具体信息。
puballtables：是否发布数据库中的所有表，t 表示发布数据库中所有已存在的表和以后新建的表。
pubinsert：t 表示仅发布表上的insert操作。
pubupdate：t 表示仅发布表上的update操作。
pubdelete：t 表示仅发布表上的delete操作。
pubtruncate：t 表示仅发布表上的truncate操作。

7.发布端给复制用户授权

pubdb=# grant connect on database pubdb to replic;
GRANT
pubdb=# grant usage on schema public to replic;
GRANT
pubdb=# grant select on c1 to replic;
GRANT

8.订阅端创建表

postgres=# create database subdb;
CREATE DATABASE
postgres=# create user replic replication login connection limit 8 password 'replic';
CREATE ROLE
subdb=> \c subdb postgres
You are now connected to database "subdb" as user "postgres".
subdb=# grant all on schema public to replic;
GRANT
subdb=> create table c1 (id int4 primary key,name text);
CREATE TABLE

9.订阅端创建订阅

subdb=> \c subdb postgres
You are now connected to database "subdb" as user "postgres".
subdb=# create subscription sub1 connection 'host=192.168.80.239 port=5432 dbname=pubdb user=replic password=replic' publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

## 查看创建的订阅
subdb=# \x
Expanded display is on.
subdb=# select * from pg_subscription;
-[ RECORD 1 ]----+-----------------------------------------------------------------------
oid              | 41374
subdbid          | 41361
subskiplsn       | 0/0
subname          | sub1
subowner         | 10
subenabled       | t
subbinary        | f
substream        | f
subtwophasestate | d
subdisableonerr  | f
subconninfo      | host=192.168.80.239 port=5432 dbname=pubdb user=replic password=replic
subslotname      | sub1
subsynccommit    | off
subpublications  | {pub1}

10.订阅端给复制用户授权

subdb=# grant connect on database subdb to replic;
GRANT
subdb=# grant usage on schema public to replic;
GRANT
subdb=# grant select on c1 to replic;
GRANT

11.配置完成，发布端查看信息

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/3F45C840
(1 row)

12.测试逻辑复制

## 发布端向表中插入数据
pubdb=> insert into c1 values (2,'tt');
INSERT 0 1
pubdb=> select * from c1;
 id | name 
----+------
  1 | a
  2 | tt
(2 rows)

pubdb=> delete from c1 where id=1;
DELETE 1
pubdb=> select * from c1;
 id | name 
----+------
  2 | tt
(1 row)

## 订阅端查看结果
subdb=# select * from c1;
 id | name 
----+------
  2 | tt
(1 row)

## 添加新表测试，发布端创建表结构
pubdb=> create table c2 (id int primary key,addr varchar(100));
CREATE TABLE

## 订阅端创建表结构
subdb=> create table c2 (id int primary key,addr varchar(100));
CREATE TABLE

## 发布端授权
pubdb=> grant select on c2 to replic;
GRANT

## 将新表c2，添加到发布列表中
pubdb=> \c pubdb postgres 
You are now connected to database "pubdb" as user "postgres".
pubdb=# alter publication pub1 add table c2;
ALTER PUBLICATION

## 发布端查看发布列表
pubdb=# select * from pg_publication_tables;
 pubname | schemaname | tablename | attnames  | rowfilter 
---------+------------+-----------+-----------+-----------
 pub1    | public     | c1        | {id,name} | 
 pub1    | public     | c2        | {id,addr} | 
(2 rows)

## 如果没有看到新表，可在订阅端刷新订阅
subdb=> \c subdb postgres
You are now connected to database "subdb" as user "postgres".
subdb=# alter subscription sub1 refresh publication;
ALTER SUBSCRIPTION

## 删除复制设置
drop subscription sub1;