Postgresql在线分区插件之pg_rewrite使用

在使用数据库的过程中，有一些表开始无法估量大小，后面表变大后，需要做分区表，那么是一件比较烦人的事情，现在好了，有了cybertec公司这款插件，轻松实现。说到这个功能，Oracle从很早就有了，叫在线重定义，而且锁粒度也很小，DML不影响。这个功能还是比较实用的，现在PG终于也有了类似的功能。

前置条件：

1. 支持13或者13以后的版本
2. 设置PG_CONFIG环境变量指向PostgreSQL安装的PG_CONFIG命令
3. make && make install
4. 配置postgresql.conf相关参数:
   wal_level = logical
   max_replication_slots = 10
   shared_preload_libraries = ‘pg_rewrite’
5. 启动数据库
6. 通过superuser创建插件
   CREATE EXTENSION pg_rewrite;

安装：

#git克隆
git clone https://github.com/cybertec-postgresql/pg_rewrite.git
Cloning into 'pg_rewrite'...
remote: Enumerating objects: 29, done.
remote: Counting objects: 100% (29/29), done.
remote: Compressing objects: 100% (24/24), done.
remote: Total 29 (delta 7), reused 21 (delta 3), pack-reused 0
Unpacking objects: 100% (29/29), done.

#进入目录，编译安装即可
cd pg_rewrite/
make && make install
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/pgsql13/include/server -I/opt/pgsql13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pg_rewrite.o pg_rewrite.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/pgsql13/include/server -I/opt/pgsql13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o concurrent.o concurrent.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_rewrite.so pg_rewrite.o concurrent.o  -L/opt/pgsql13/lib    -Wl,--as-needed -Wl,-rpath,'/opt/pgsql13/lib',--enable-new-dtags  
/usr/bin/mkdir -p '/opt/pgsql13/lib'
/usr/bin/mkdir -p '/opt/pgsql13/share/extension'
/usr/bin/mkdir -p '/opt/pgsql13/share/extension'
/usr/bin/install -c -m 755  pg_rewrite.so '/opt/pgsql13/lib/pg_rewrite.so'
/usr/bin/install -c -m 644 .//pg_rewrite.control '/opt/pgsql13/share/extension/'
/usr/bin/install -c -m 644 .//pg_rewrite--1.0.sql  '/opt/pgsql13/share/extension/'

使用实例：

#安装插件
create extension pg_rewrite;
#建普通表
CREATE TABLE measurement (
    id              serial,
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int,
    PRIMARY KEY(id, logdate)
);

#新建表结构一样的分区表
CREATE TABLE measurement_aux (
    id              serial,
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int,
    PRIMARY KEY(id, logdate)
) PARTITION BY RANGE (logdate);

#分区表加分区
CREATE TABLE measurement_202109 PARTITION OF measurement_aux
    FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
CREATE TABLE measurement_202110 PARTITION OF measurement_aux
    FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE measurement_202111 PARTITION OF measurement_aux
    FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');
CREATE TABLE measurement_202112 PARTITION OF measurement_aux
    FOR VALUES FROM ('2021-12-01') TO ('2022-01-01');

#插入几条测试数据
insert into measurement(city_id,logdate,peaktemp,unitsales) values (2,now(),6,6);
insert into measurement(city_id,logdate,peaktemp,unitsales) values (4,'2021-11-11',6,6);
insert into measurement(city_id,logdate,peaktemp,unitsales) values (5,'2021-10-11',6,6);
insert into measurement(city_id,logdate,peaktemp,unitsales) values (6,'2021-09-11',6,6);

postgres=#  select * from measurement;
 id | city_id |  logdate   | peaktemp | unitsales 
----+---------+------------+----------+-----------
  1 |       2 | 2021-12-30 |        6 |         6
  2 |       4 | 2021-11-11 |        6 |         6
  3 |       5 | 2021-10-11 |        6 |         6
  4 |       6 | 2021-09-11 |        6 |         6
(4 rows)

#转换之前查看一下表名
postgres=# \dt
                     List of relations
 Schema |        Name        |       Type        |  Owner   
--------+--------------------+-------------------+----------

 public | measurement        | table             | postgres
 public | measurement_202109 | table             | postgres
 public | measurement_202110 | table             | postgres
 public | measurement_202111 | table             | postgres
 public | measurement_202112 | table             | postgres
 public | measurement_aux    | partitioned table | postgres

(9 rows)

#转换为分区表
postgres=# SELECT partition_table('measurement', 'measurement_aux', 'measurement_old');
 partition_table 
-----------------
 
(1 row)

#原来的表重命名为measurement_old
postgres=# \dt
                     List of relations
 Schema |        Name        |       Type        |  Owner   
--------+--------------------+-------------------+----------
 
 public | measurement        | partitioned table | postgres
 public | measurement_202109 | table             | postgres
 public | measurement_202110 | table             | postgres
 public | measurement_202111 | table             | postgres
 public | measurement_202112 | table             | postgres
 public | measurement_old    | table             | postgres

(9 rows)

#查看表数据
postgres=# select * from measurement;
 id | city_id |  logdate   | peaktemp | unitsales 
----+---------+------------+----------+-----------
  4 |       6 | 2021-09-11 |        6 |         6
  3 |       5 | 2021-10-11 |        6 |         6
  2 |       4 | 2021-11-11 |        6 |         6
  1 |       2 | 2021-12-30 |        6 |         6
(4 rows)

postgres=# select * from measurement_old;
 id | city_id |  logdate   | peaktemp | unitsales 
----+---------+------------+----------+-----------
  1 |       2 | 2021-12-30 |        6 |         6
  2 |       4 | 2021-11-11 |        6 |         6
  3 |       5 | 2021-10-11 |        6 |         6
  4 |       6 | 2021-09-11 |        6 |         6
(4 rows)

#查看转换后分区数据，已经自动落入相应分区
postgres=# select * from measurement_202109;
 id | city_id |  logdate   | peaktemp | unitsales 
----+---------+------------+----------+-----------
  4 |       6 | 2021-09-11 |        6 |         6
(1 row)

postgres=# select * from measurement_202110;
 id | city_id |  logdate   | peaktemp | unitsales 
----+---------+------------+----------+-----------
  3 |       5 | 2021-10-11 |        6 |         6
(1 row)

postgres=# select * from measurement_202111;
 id | city_id |  logdate   | peaktemp | unitsales 
----+---------+------------+----------+-----------
  2 |       4 | 2021-11-11 |        6 |         6
(1 row)

postgres=# select * from measurement_202112;
 id | city_id |  logdate   | peaktemp | unitsales 
----+---------+------------+----------+-----------
  1 |       2 | 2021-12-30 |        6 |         6
(1 row)

配置相关参数：

rewrite.check_constraints
在复制数据之前，检查源表和目标表约束，如果不一致，则抛出错误，如果关闭该检查，目标分区表没有约束，在源表的违反约束的数据会出现在目标分区表中，这样就会出现数据不一致的情况，所以尽量不要关闭该参数，尽量使得目标表和源表的约束一致。

rewrite.max_xlock_time
虽然正在被处理的表在大多数时间都可以被其他事务读和写操作，但是需要一个排他锁（AccessExclusiveLock）来完成处理(即处理剩余的并发更改和重命名表)。如果该插件函数过多地阻止对表的访问。则可以考虑设置该参数max_xlock_time”，例如：
SET rewrite.max_xlock_time TO 100;  --单位为毫秒

注意：

1. 该插件不支持外部表，也不支持有外键的目标表
2. 如果主键使用序列填充，有默认值的情况，序列的使用值不会跟随到新的分区表，这个其实和逻辑复制时一样的，不会复制序列，所以我们要查询出源表的最大值，然后重置目标分区表使用的序列值。例如：

 select setval('measurement_aux_id_seq'::regclass,new_value);

参考：
https://github.com/cybertec-postgresql/pg_rewrite
https://www.cybertec-postgresql.com/en/pg_rewrite-postgresql-table-partitioning/