PostgreSQL 13新特性之一: 逻辑复制支持分区表__PostgreSQL 13 Beta版本已Released，尽管没有太多激动人心的新特性，总体在功能上有所增强，本文介绍逻辑复制对分区表的支持。 PostgreSQL 10 版本开始已支持逻辑复制，在12版本之前逻辑复制仅支持普通表，不支持分区表，如果需要对分区表进行逻辑复制，需单独对所有分区进行逻辑复制。: 世界上功能最强大的开源数据库...

作者简介

谭峰网名francs，三墩IT人，PostgreSQL中文社区委员，《PostgreSQL实战》作者之一，《PostgreSQL 9 Administration Cookbook》译者之一，致力于PostgreSQL技术分享，博客 https://postgres.fun

背景

PostgreSQL 13 Beta版本已Released，尽管没有太多激动人心的新特性，总体在功能上有所增强，本文介绍逻辑复制对分区表的支持。

PostgreSQL 10 版本开始已支持逻辑复制，在12版本之前逻辑复制仅支持普通表，不支持分区表，如果需要对分区表进行逻辑复制，需单独对所有分区进行逻辑复制。

PostgreSQL 13 版本的逻辑复制新增对分区表的支持，如下:

1、可以显式地对分区表进行发布，自动发布所有分区。

2、从分区表中添加或删除分区将自动从发布者中添加或删除。

本文将对第1点进行演示。

发行说明

Allow partitioned tables to be logically replicated via publications (Amit Langote)

Previously, partitions had to be replicated individually. Now partitioned tables can be published explicitly causing all partitions to be automatically published. Addition/removal of partitions from partitioned tables are automatically added/removed from publications. The CREATE PUBLICATION option publish_via_partition_root controls whether changes to partitions are published as their own or their ancestors.

Allow logical replication into partitioned tables on subscribers (Amit Langote)

Previously, subscribers could only receive rows into non-partitioned tables.

以上是PostgreSQL 13 Beta1 手册中的说明。

环境规划

计划部署一个源库和目标库，之后在源库和目标库上部署逻辑复制，环境规划，如下:

CENTER_PostgreSQL_Community

环境准备

在源库、目标库安装 PostgreSQL 13beta1软件并初始化数据库，本文略。

部署mydb数据库

在源库和目标库上均部署 mydb 数据库，如下:

--建用户
CREATE ROLE pguser LOGIN  ENCRYPTED PASSWORD 'pguser' nosuperuser noinherit nocreatedb nocreaterole ;

--创建表空间(如果有 Standby ，也需要创建目录)
mkdir -p /pgdata/pg13/pg_tbs/tbs_mydb

--创建数据库
CREATE DATABASE mydb
WITH  OWNER = postgres 
TEMPLATE = template0 
ENCODING = 'UTF8'
TABLESPACE = tbs_mydb;

--赋权
grant all on database mydb to pguser  with grant option;
grant all on tablespace tbs_mydb to pguser;

\c mydb pguser
create schema pguser;

创建分区表

在源库和目标库创建分区表，如下:

--创建父表
CREATE TABLE tbl_log ( 
id serial,
  user_id int4,
  create_time timestamp(0) without time zone
) PARTITION BY RANGE(create_time);

--创建子表
CREATE TABLE tbl_log_his PARTITION OF tbl_log FOR VALUES FROM (minvalue) TO ('2020-01-01');
CREATE TABLE tbl_log_202001 PARTITION OF tbl_log FOR VALUES FROM ('2020-01-01') TO ('2020-02-01');
CREATE TABLE tbl_log_202002 PARTITION OF tbl_log FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');
CREATE TABLE tbl_log_202003 PARTITION OF tbl_log FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');
CREATE TABLE tbl_log_202004 PARTITION OF tbl_log FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');
CREATE TABLE tbl_log_202005 PARTITION OF tbl_log FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE tbl_log_202006 PARTITION OF tbl_log FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE TABLE tbl_log_202007 PARTITION OF tbl_log FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');

--创建索引
CREATE INDEX idx_tbl_log_ctime ON tbl_log USING BTREE (create_time);

部署逻辑复制

源库执行以下操作，如下:

--创建复制用户
CREATE USER repuser
 REPLICATION
 LOGIN
 CONNECTION LIMIT 10
 ENCRYPTED PASSWORD 'rep123us345er';

--创建发布者
mydb=> CREATE PUBLICATION pub1 FOR TABLE tbl_log;
CREATE PUBLICATION

--给repuser用户赋权
mydb=> GRANT CONNECT ON DATABASE mydb TO repuser;
GRANT
mydb=> GRANT USAGE ON SCHEMA pguser TO repuser;
GRANT
mydb=> GRANT SELECT ON ALL TABLES IN SCHEMA pguser TO repuser;
GRANT

需给源库上的repuser用户赋相关权限，如果不给repuser用户赋权，创建订阅后目标库无法初始化同步源库数据。

目标库创建订阅，如下:

mydb=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.2.11 port=1922 dbname=mydb user=repuser' PUBLICATION pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

注意配置好源库的pg_hba.conf和.pgpass文件，否则创建订阅会报相关的连接不上错误。

数据验证

源库批量插入数据，如下:

INSERT INTO tbl_log(user_id,create_time)
SELECT round(100000000*random()),generate_series('2019-10-01'::date, '2020-06-20'::date, '1 day');

源库查看数据，如下:

[pg13@ydtf01 ~]$ psql mydb pguser -p 1922
psql (13beta1)
Type "help" for help.

mydb=> SELECT count(*) FROM tbl_log;
count
-------
264
(1 row)

mydb=> SELECT count(*) FROM tbl_log_202001;
count
-------
31
(1 row)

mydb=> SELECT count(*) FROM tbl_log_his;
count
-------
92
(1 row)

目标库验证数据，如下:

[pg13@ydtf03 ~]$ psql mydb pguser -p 1924
psql (13beta1)
Type "help" for help.

mydb=> SELECT count(*) FROM tbl_log;
count
-------
264
(1 row)

mydb=> SELECT count(*) FROM tbl_log_202001;
count
-------
31
(1 row)

mydb=> SELECT count(*) FROM tbl_log_his;
count
-------
92
(1 row)

可见分区表的数据已从源库同步到目标库。

总结

本文简单演示了PostgreSQL 13 Beta1 逻辑复制对分区表的支持。

如果给分区表添加或删除分区，将自动从发布中添加或删除分区，添加完分区后，需要执行以下命令对订阅进行刷新，新分区的数据才会同步。

ALTER subscription sub_name REFRESH PUBLICATION

本文不做演示，有兴趣的朋友可自行测试。

阅读原文

https://postgres.fun/20200622102400.html

CENTER_PostgreSQL_Community

### **作者简介**
#### 谭峰 网名francs，三墩IT人，PostgreSQL中文社区委员，《PostgreSQL实战》作者之一，《PostgreSQL 9 Administration Cookbook》译者之一，致力于PostgreSQL技术分享，博客 https://postgres.fun

### **背景**
#### PostgreSQL 13 Beta版本已Released，尽管没有太多激动人心的新特性，总体在功能上有所增强，本文介绍逻辑复制对分区表的支持。
 
#### PostgreSQL 10 版本开始已支持逻辑复制，在12版本之前逻辑复制仅支持普通表，不支持分区表，如果需要对分区表进行逻辑复制，需单独对所有分区进行逻辑复制。
 
#### PostgreSQL 13 版本的逻辑复制新增对分区表的支持，如下:
 
#### 1、可以显式地对分区表进行发布，自动发布所有分区。
#### 2、从分区表中添加或删除分区将自动从发布者中添加或删除。
 
#### 本文将对第1点进行演示。
### **发行说明**
#### Allow partitioned tables to be logically replicated via publications (Amit Langote)
#### Previously, partitions had to be replicated individually. Now partitioned tables can be published explicitly causing all partitions to be automatically published. Addition/removal of partitions from partitioned tables are automatically added/removed from publications. The CREATE PUBLICATION option publish_via_partition_root controls whether changes to partitions are published as their own or their ancestors.
 
#### Allow logical replication into partitioned tables on subscribers (Amit Langote)
#### Previously, subscribers could only receive rows into non-partitioned tables.
#### 以上是PostgreSQL 13 Beta1 手册中的说明。
 
### **环境规划**
#### 计划部署一个源库和目标库，之后在源库和目标库上部署逻辑复制，环境规划，如下:
![CENTER_PostgreSQL_Community]( /images/news/2020/20200701_1618_图片1--2020.6_.30_.png)

### **环境准备**
#### 在源库、目标库安装 PostgreSQL 13beta1软件并初始化数据库，本文略。

### **部署mydb数据库**
#### 在源库和目标库上均部署 mydb 数据库，如下:
```
--建用户
CREATE ROLE pguser LOGIN  ENCRYPTED PASSWORD 'pguser' nosuperuser noinherit nocreatedb nocreaterole ;

--创建表空间(如果有 Standby ，也需要创建目录)
mkdir -p /pgdata/pg13/pg_tbs/tbs_mydb

--创建数据库
CREATE DATABASE mydb
WITH  OWNER = postgres 
TEMPLATE = template0 
ENCODING = 'UTF8'
TABLESPACE = tbs_mydb;

--赋权
grant all on database mydb to pguser  with grant option;
grant all on tablespace tbs_mydb to pguser;

\c mydb pguser
create schema pguser;
```

### **创建分区表**
#### 在源库和目标库创建分区表，如下:
```
--创建父表
CREATE TABLE tbl_log ( 
id serial,
  user_id int4,
  create_time timestamp(0) without time zone
) PARTITION BY RANGE(create_time);

--创建子表
CREATE TABLE tbl_log_his PARTITION OF tbl_log FOR VALUES FROM (minvalue) TO ('2020-01-01');
CREATE TABLE tbl_log_202001 PARTITION OF tbl_log FOR VALUES FROM ('2020-01-01') TO ('2020-02-01');
CREATE TABLE tbl_log_202002 PARTITION OF tbl_log FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');
CREATE TABLE tbl_log_202003 PARTITION OF tbl_log FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');
CREATE TABLE tbl_log_202004 PARTITION OF tbl_log FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');
CREATE TABLE tbl_log_202005 PARTITION OF tbl_log FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE tbl_log_202006 PARTITION OF tbl_log FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE TABLE tbl_log_202007 PARTITION OF tbl_log FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');

--创建索引
CREATE INDEX idx_tbl_log_ctime ON tbl_log USING BTREE (create_time);
```
### **部署逻辑复制**
#### 源库执行以下操作，如下:
```
--创建复制用户
CREATE USER repuser
 REPLICATION
 LOGIN
 CONNECTION LIMIT 10
 ENCRYPTED PASSWORD 'rep123us345er';

--创建发布者
mydb=> CREATE PUBLICATION pub1 FOR TABLE tbl_log;
CREATE PUBLICATION

--给repuser用户赋权
mydb=> GRANT CONNECT ON DATABASE mydb TO repuser;
GRANT
mydb=> GRANT USAGE ON SCHEMA pguser TO repuser;
GRANT
mydb=> GRANT SELECT ON ALL TABLES IN SCHEMA pguser TO repuser;
GRANT
```
#### 需给源库上的repuser用户赋相关权限，如果不给repuser用户赋权，创建订阅后目标库无法初始化同步源库数据。
 
#### 目标库创建订阅，如下:
```
mydb=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.2.11 port=1922 dbname=mydb user=repuser' PUBLICATION pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
```
#### 注意配置好源库的pg_hba.conf和.pgpass文件，否则创建订阅会报相关的连接不上错误。

### **数据验证**
#### 源库批量插入数据，如下:
```
INSERT INTO tbl_log(user_id,create_time)
SELECT round(100000000*random()),generate_series('2019-10-01'::date, '2020-06-20'::date, '1 day');
```
#### 源库查看数据，如下:
```
[pg13@ydtf01 ~]$ psql mydb pguser -p 1922
psql (13beta1)
Type "help" for help.

mydb=> SELECT count(*) FROM tbl_log;
count
-------
264
(1 row)

mydb=> SELECT count(*) FROM tbl_log_202001;
count
-------
31
(1 row)

mydb=> SELECT count(*) FROM tbl_log_his;
count
-------
92
(1 row)
```
#### 目标库验证数据，如下:
```
[pg13@ydtf03 ~]$ psql mydb pguser -p 1924
psql (13beta1)
Type "help" for help.

mydb=> SELECT count(*) FROM tbl_log;
count
-------
264
(1 row)

mydb=> SELECT count(*) FROM tbl_log_202001;
count
-------
31
(1 row)

mydb=> SELECT count(*) FROM tbl_log_his;
count
-------
92
(1 row)
```
#### 可见分区表的数据已从源库同步到目标库。

### **总结**
#### 本文简单演示了PostgreSQL 13 Beta1 逻辑复制对分区表的支持。
#### 如果给分区表添加或删除分区，将自动从发布中添加或删除分区，添加完分区后，需要执行以下命令对订阅进行刷新，新分区的数据才会同步。
#### ALTER subscription sub_name REFRESH PUBLICATION
#### 本文不做演示，有兴趣的朋友可自行测试。

#### 阅读原文
#### <https://postgres.fun/20200622102400.html>

![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)