一、环境配置
2.1 参数配置
postgres=# show manual_hot_date ;
manual_hot_date
-----------------
2022-01-01
(1 row
postgres=# show cold_hot_sepration_mode;
cold_hot_sepration_mode
-------------------------
year
(1 row)
postgres=# show enable_cold_seperation;
enable_cold_seperation
------------------------
on(1 row复制
postgres=# select * from pgxc_group;
group_name | default_group | group_members
---------------+---------------+---------------
default_group | 1 | 16385 16386
(2 rows)复制
postgres=# select * from pgxc_node where node_type='D';
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id | node_cluster_name
-----------+-----------+-----------+---------------+----------------+------------------+-------------+---------------------
dn001 | D | 40004 | 192.168.2.136 | t | t | 2142761564 | opentenbase_cluster
dn002 | D | 40004 | 192.168.2.137 | f | f | -17499968 | opentenbase_cluster
dn003 | D | 40004 | 192.168.2.138 | f | f | -1956435056 | opentenbase_cluster
(3 rows)复制
--CN上执行，不要到DN上
[opentenbase@db1 ~]$ psql -h 192.168.2.136 -p 30004 -d postgres -U opentenbase
create node group cold_group with(dn003);
create extension sharding group to group cold_group;
clean sharding;
--连接到DN3为冷组
[opentenbase@db1 ~]$ psql -h 192.168.2.138 -p 40004 -d postgres -U opentenbase
postgres=# select pg_set_node_cold_access();
pg_set_node_cold_access
-------------------------
success复制
--CN上操作
[opentenbase@db1 ~]$ psql -h 192.168.2.136 -p 30004 -d postgres -U opentenbase
DROP TABLE cold_move_test;
CREATE TABLE cold_move_test(
id int not null,
identifynumber text NOT NULL,
inserttimeforhis timestamp without time zone NOT NULL,
primary key(id,identifynumber,inserttimeforhis))
partition by range (inserttimeforhis)
begin (timestamp without time zone '2020-01-01')
step (interval '1 year')
partitions (24)
DISTRIBUTE BY SHARD (identifynumber,inserttimeforhis) to GROUP default_group cold_group;
##查看对应的分区
pg中的分区表的命名规则如下
postgres=# select relname from pg_class where relname like '%cold_move%part%';
relname
-----------------------------
cold_move_test_part_0
cold_move_test_part_1
cold_move_test_part_2
cold_move_test_part_3
cold_move_test_part_4
cold_move_test_part_5
cold_move_test_part_6
cold_move_test_part_7
cold_move_test_part_8
cold_move_test_part_9
cold_move_test_part_10
cold_move_test_part_11
cold_move_test_part_12
cold_move_test_part_13
cold_move_test_part_14
cold_move_test_part_15
cold_move_test_part_16
cold_move_test_part_17
cold_move_test_part_18
cold_move_test_part_19
cold_move_test_part_20
cold_move_test_part_21
cold_move_test_part_22
cold_move_test_part_23复制
1）--插入100条2020年的数据
for i in `seq 100`
do
psql -h 192.168.2.136 -p 30004 -d postgres -U opentenbase -c "insert into cold_move_test values(${i},'testtest${i}','2020-09-03 16:21:34.201133');" >/dev/null
done
2）--插入100条2021年数据
for i in `seq 201 300`
do
psql -h 192.168.2.136 -p 30004 -d postgres -U opentenbase -c "insert into cold_move_test values(${i},'testtest${i}','2021-12-03 16:21:34.201133');" >/dev/null
done
2）--插入100条2022年数据
for i in `seq 301 400`
do
psql -h 192.168.2.136 -p 30004 -d postgres -U opentenbase -c "insert into cold_move_test values(${i},'testtest${i}','2022-12-03 16:21:34.201133');" >/dev/null
done复制
postgres=# EXPLAIN SELECT COUNT(1) FROM cold_move_test where inserttimeforhis>'2022-01-01';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Aggregate (cost=103.44..103.45 rows=1 width=8)
-> Remote Subquery Scan on all (dn001,dn002) (cost=100.00..103.36 rows=30 width=0)
-> Append (cost=0.00..0.00 rows=0 width=0)
-> Seq Scan on cold_move_test (partition sequence: 2, name: cold_move_test_part_2) (cost=0.00..0.15 rows=1 wi
dth=0)
Filter: (inserttimeforhis > '2022-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on cold_move_test (partition sequence: 3, name: cold_move_test_part_3) (cost=0.00..0.15 rows=1 wi
dth=0)
Filter: (inserttimeforhis > '2022-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on cold_move_test (partition sequence: 4, name: cold_move_test_part_4) (cost=0.00..0.15 rows=1 wi
dth=0)
Filter: (inserttimeforhis > '2022-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on cold_move_test (partition sequence: 5, name: cold_move_test_part_5) (cost=0.00..0.15 rows=1 wi
dth=0)
Filter: (inserttimeforhis > '2022-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on cold_move_test (partition sequence: 6, name: cold_move_test_part_6) (cost=0.00..0.15 rows=1 wi
dth=0)
Filter: (inserttimeforhis > '2022-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on cold_move_test (partition sequence: 7, name: cold_move_test_part_7) (cost=0.00..0.15 rows=1 wi
dth=0)
Filter: (inserttimeforhis > '2022-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on cold_move_test (partition sequence: 8, name: cold_move_test_part_8) (cost=0.00..0.15 rows=1 wi
dth=0)
Filter: (inserttimeforhis > '2022-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on cold_move_test (partition sequence: 9, name: cold_move_test_part_9) (cost=0.00..0.15 rows=1 wi
dth=0)
Filter: (inserttimeforhis > '2022-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on cold_move_test (partition sequence: 10, name: cold_move_test_part_10) (cost=0.00..0.15 rows=1复制
postgres=# EXPLAIN SELECT COUNT(1) FROM cold_move_test where inserttimeforhis<'2022-01-01';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
---------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn003
-> Aggregate (cost=5.00..5.01 rows=1 width=8)
-> Append (cost=0.00..0.00 rows=0 width=0)
-> Seq Scan on cold_move_test (partition sequence: 0, name: cold_move_test_part_0) (cost=0.00..2.25 rows=100
width=0)
Filter: (inserttimeforhis < '2022-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on cold_move_test (partition sequence: 1, name: cold_move_test_part_1) (cost=0.00..2.25 rows=100
width=0)
Filter: (inserttimeforhis < '2022-01-01 00:00:00'::timestamp without time zone)
(8 rows)复制
AtomGit https://atomgit.com/opentenbase GitHub https://github.com/OpenTenBase
文章转载自OpenAtom OpenTenBase，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
