背景
PostgreSQL 13 版本逻辑复制已支持分区表，支持以下两种场景:
- 分区表数据逻辑复制到普通表。
- 分区表数据逻辑复制到异构分区表。
实际上，普通表数据可以逻辑复制到分区表，本文演示下。
环境规划
环境规划，如下:
| 节点 | 数据库版本 | IP | 端口 |
|---|---|---|---|
| 源库 | PostgreSQL 13beta1 | 192.168.2.11 | 1922 |
| 目标库 | PostgreSQL 13beta1 | 192.168.2.13 | 1924 |
环境准备
源库和目标库安装 PostgreSQL 13beta1软件，本文略。
源库上创建表和发布
在源库创创建表，如下:1
2
3
4
5CREATE TABLE tbl_log (
id serial,
user_id int4,
create_time timestamp(0) without time zone
);
插入数据，如下:1
2
3mydb=> INSERT INTO tbl_log(user_id,create_time)
SELECT round(100000000*random()),generate_series('2019-10-01'::date, '2020-06-20'::date, '1 minute');
INSERT 0 378721
源库上创建发布，如下:1
2mydb=> CREATE PUBLICATION pub1 FOR TABLE tbl_log ;
CREATE PUBLICATION
给repuser用户赋权，如下:1
2
3
4
5
6mydb=> 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用户赋权，创建订阅后目标库无法初始化同步源库数据。
目标库上创建表和订阅
目标库上创建分区表，如下:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19--创建父表
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);
创建订阅，如下:1
2mydb=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.2.11 port=1922 dbname=mydb user=repuser' PUBLICATION pub1;
NOTICE: created replication slot "sub1" on publisher
注意配置好源库的pg_hba.conf和.pgpass文件，否则创建订阅会报相关的连接不上错误。
数据验证
源库上数据如下:1
2
3
4
5
6
7
8
9[pg13@ydtf01 log]$ psql mydb pguser -p 1922
psql (13beta1)
Type "help" for help.
mydb=> SELECT COUNT(*) FROM tbl_log;
count
--------
378721
(1 row)
目标库上验证数据，如下:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38[pg13 ~]$ psql mydb pguser -p 1924
psql (13beta1)
Type "help" for help.
mydb=> \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+----------------+-------------------+--------+-------------+---------+-------------
pguser | tbl_log | partitioned table | pguser | permanent | 0 bytes |
pguser | tbl_log_202001 | table | pguser | permanent | 1960 kB |
pguser | tbl_log_202002 | table | pguser | permanent | 1832 kB |
pguser | tbl_log_202003 | table | pguser | permanent | 1960 kB |
pguser | tbl_log_202004 | table | pguser | permanent | 1896 kB |
pguser | tbl_log_202005 | table | pguser | permanent | 1960 kB |
pguser | tbl_log_202006 | table | pguser | permanent | 1208 kB |
pguser | tbl_log_202007 | table | pguser | permanent | 0 bytes |
pguser | tbl_log_his | table | pguser | permanent | 5760 kB |
(9 rows)
mydb=> SELECT COUNT(*) FROM tbl_log;
count
--------
378721
(1 row)
^
mydb=> SELECT * FROM tbl_log_202001 LIMIT 2;
id | user_id | create_time
--------+----------+---------------------
132481 | 76249118 | 2020-01-01 00:00:00
132482 | 28947895 | 2020-01-01 00:01:00
(2 rows)
mydb=> SELECT * FROM tbl_log_his LIMIT 2;
id | user_id | create_time
----+----------+---------------------
1 | 61896797 | 2019-10-01 00:00:00
2 | 79680989 | 2019-10-01 00:01:00
(2 rows)
可见数据已从普通表逻辑复制到了分区表。
