tbase配置冷热数据分离

本次测试使用了2个数据节点。 dn001,dn002

postgres=# select * from pgxc_node;
 node_name | node_type | node_port |   node_host   | nodeis_primary | nodeis_preferred |  node_id   | node_cluster_name
-----------+-----------+-----------+---------------+----------------+------------------+------------+-------------------
 agtm_0    | G         |     11002 | 192.168.5.200 | t              | f                |  475343005 | tbase_cluster
 cn001     | C         |     11387 | 192.168.5.200 | f              | f                | -264077367 | tbase_cluster
 dn001     | D         |     11000 | 192.168.5.200 | f              | f                | 2142761564 | tbase_cluster
 dn002     | D         |     11004 | 192.168.5.201 | f              | f                |  -17499968 | tbase_cluster
(4 rows)

1，建立分组default_group和cold_group两个存储池。

#删除之前创建的default_group
drop sharding in group default_group;
drop node group default_group;

dn001 为默认组。
dn002 为冷数据组。
#创建冷热组
create default node group default_group with(dn001);
create sharding group to group default_group;
clean sharding;
create node group cold_group with(dn002);
create extension sharding group to group cold_group;
clean sharding;

2，冷数据的cold_group对应的节点需要标识为冷节点：（主备都要设置）

[tbase@db02 ~]$ psql -h 192.168.5.201 -p 11004 -d postgres -U tbase
psql (10.6, server 10.0 TBase V2)
Type "help" for help.

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

3，创建分区表

create table cold_move_test
(
f1 int not null,
f3 varchar(20),
f2 timestamp not null
)
partition by range (f2)
begin (timestamp without time zone '2015-01-01 0:0:0')
step (interval '1 year') partitions (10)
distribute by shard(f1,f2)
to group default_group cold_group;

4，修改冷热分离参数（所有节点全部修改）

//manual_hot_date时间修改为2019-01-01

cold_hot_sepration_mode    postmaster    是    year    NULL    Set the cold_hot_sepration_mode, day/month/year.    
enable_cold_seperation    user    否    on    NULL    Enable cold storage seperation when make route strategy.    
manual_hot_date    sighup    否    2019-01-01    NULL    Set the manual_hot_date, this value will mask off hot_date_age.

5，导入测试数据

\copy  cold_move_test from 'cold_move_test.csv';

6，验证数据冷热分离结果

postgres=# select b.node_name,a.cnt from
postgres-# (select xc_node_id x_id,count(*) cnt from cold_move_test group by xc_node_id) a inner join pgxc_node b on a.x_id=b.node_id;
 node_name | cnt
-----------+-----
 dn002     | 100
 dn001     |  50
(2 rows)

测试数据：