冷热分区表

以下说明冷热分区的场景及使用。

postgres=#  create table t_cold_hot_table(f1 bigint, f2 timestamp ,f3 bigint) partition by range (f2) begin (timestamp without time zone '2017-09-01 0:0:0') step (interval '1 month') partitions (12) distribute by shard(f1,f2) to group default_group ext_group;
CREATE TABLE

postgres=# \d+ t_cold_hot_table
                              Table "pgxz.t_cold_hot_table"
 Column |            Type             | Modifiers | Storage | Stats target | Description 
--------+-----------------------------+-----------+---------+--------------+-------------
 f1     | bigint                      |           | plain   |              | 
 f2     | timestamp without time zone |           | plain   |              | 
 f3     | bigint                      |           | plain   |              | 
Has OIDs: no
Distribute By SHARD(f1,f2)
        Hotnodes:dn02, dn01 Coldnodes:dn03, dn04
Partition By: RANGE(f2)
    # Of Partitions: 12
    Start With: 2017-09-01
Interval Of Partition: 1 MONTH

注意：

创建时间范围冷热分区表需要有两个group，冷数据的ext_group对应的节点dn03和dn04需要标识为冷节点，如下所示：

postgres=#  select pg_set_node_cold_access();
 pg_set_node_cold_access 
-------------------------
 success
(1 row)

postgres=# \q
[pgxz@VM_0_29_centos install]$ psql -p 23004
psql (PGXC , based on PG 9.4beta1)
Type "help" for help.

postgres=#  select pg_set_node_cold_access();
 pg_set_node_cold_access 
-------------------------
 success
(1 row)

postgres=# 

注意：

使用冷热分区表需要在postgresql.conf中配置冷热分区时间参数，如下所示：

manual_hot_date = '2017-12-01