5分钟Citus之SQL命令参考

2022-05-09 00:00:00 命令节点查看分布亲和性

一、实战环境
节点 cn_node1(协调节点，不存储数据) postgresql 14.2 10.211.55.9
节点 worker1_node2(工作节点，存储数据) postgresql 14.2 10.211.55.4
节点 worker2_node3(工作节点，存储数据) postgresql 14.2 10.211.55.6
citus-10.2 / debian 9
二、SQL命令参考
1、分布表转为普通表
在cn节点，查看系统中现有的Citus表

d1=# select * from citus_tables;
table_name | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method
------------+------------------+---------------------+---------------+------------+-------------+-------------+---------------
t1 | distributed | id | 2 | 8512 kB | 32 | postgres | heap
t11 | distributed | id | 2 | 8512 kB | 32 | postgres | heap
t2 | reference | <none> | 1 | 13 MB | 1 | postgres | heap
t22 | reference | <none> | 1 | 13 MB | 1 | postgres | heap
(4 rows)

在cn节点，我们将以上的分布表t1在线改为普通表

d1=# SELECT undistribute_table('t1');
NOTICE: creating a new table for public.t1
NOTICE: moving the data of public.t1
NOTICE: dropping the old public.t1
NOTICE: renaming the new table to public.t1
undistribute_table
--------------------

(1 row)

在cn节点，再查看系统中现有的Citus表，可以看到表t1已经转为了普通表

d1=# select * from citus_tables;
table_name | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method
------------+------------------+---------------------+---------------+------------+-------------+-------------+---------------
t11 | distributed | id | 2 | 8512 kB | 32 | postgres | heap
t2 | reference | <none> | 1 | 13 MB | 1 | postgres | heap
t22 | reference | <none> | 1 | 13 MB | 1 | postgres | heap
(3 rows)

在worker节点，查看表情况

worker1节点

d1=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | citus_tables | view | postgres
public | t11_102040 | table | postgres
public | t11_102042 | table | postgres
public | t11_102044 | table | postgres
public | t11_102046 | table | postgres
public | t11_102048 | table | postgres
public | t11_102050 | table | postgres
public | t11_102052 | table | postgres
public | t11_102054 | table | postgres
public | t11_102056 | table | postgres
public | t11_102058 | table | postgres
public | t11_102060 | table | postgres
public | t11_102062 | table | postgres
public | t11_102064 | table | postgres
public | t11_102066 | table | postgres
public | t11_102068 | table | postgres
public | t11_102070 | table | postgres
public | t22_102073 | table | postgres
public | t2_102072 | table | postgres
(19 rows)

worker2节点

d1=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | citus_tables | view | postgres
public | t11_102041 | table | postgres
public | t11_102043 | table | postgres
public | t11_102045 | table | postgres
public | t11_102047 | table | postgres
public | t11_102049 | table | postgres
public | t11_102051 | table | postgres
public | t11_102053 | table | postgres
public | t11_102055 | table | postgres
public | t11_102057 | table | postgres
public | t11_102059 | table | postgres
public | t11_102061 | table | postgres
public | t11_102063 | table | postgres
public | t11_102065 | table | postgres
public | t11_102067 | table | postgres
public | t11_102069 | table | postgres
public | t11_102071 | table | postgres
public | t22_102073 | table | postgres
public | t2_102072 | table | postgres
(19 rows)

可以看到在worker1和worker2节点，t1表已经消失了，说明已经转成了普通表。

2、指定分布表之间的亲和性
新建分布表

SELECT create_distributed_table('stores', 'store_id');

指定分布表之间的亲和性

SELECT create_distributed_table('orders', 'store_id', colocate_with => 'stores');
SELECT create_distributed_table('products', 'store_id', colocate_with => 'stores');

通过指定colocate_with => 'stores'可以指定分布表之间的关联亲和性，对于关联查询非常有帮助

3、在所有worker节点执行命令
查询所有worker节点的端口

d1=> SELECT run_command_on_workers($cmd$ show port; $cmd$);
run_command_on_workers
------------------------------
(192.168.40.143,5432,t,5432)
(192.168.40.147,5432,t,5432)
(2 rows)

d1=>

4、添加新的worker节点
新增一个worker节点192.168.40.132，先在这个节点上完成《童虎学习笔记》5分钟入门PG分布式集群Citus的"安装Citus"的内容完成基础配置

在cn节点上添加这个worker节点

d1=# select * from citus_add_node('192.168.40.132', 5432);
NOTICE: Replicating reference table "t2" to the node 192.168.40.132:5432
NOTICE: Replicating reference table "t22" to the node 192.168.40.132:5432
citus_add_node
----------------
5
(1 row)

这个操作会将参考表都同步到这个新增的worker节点，但分布表不会同步

可以通过如下命令查看现有节点情况

SELECT * FROM citus_get_active_worker_nodes();

5、添加非激活worker节点
也可以用下面的命令添加一个非激活状态的worker节点，这个节点连参考表都不会同步

d1=# select * from citus_add_inactive_node('192.168.40.132', 5432);

接下来激活这个节点，它会自动同步参考表

d1=# select citus_activate_node('192.168.40.132', 5432);
citus_activate_node
---------------------
6
(1 row)

6、将节点变为非激活状态
d1=# select citus_disable_node('192.168.40.132', 5432);
citus_disable_node
--------------------

(1 row)

7、删除worker节点
d1=# select * from citus_remove_node('192.168.40.132', 5432);
citus_remove_node
-------------------

(1 row)

8、查看Citus表占用的所有分片大小
d1=# SELECT pg_size_pretty(citus_relation_size('t1'));
pg_size_pretty
----------------
4416 kB
(1 row)

d1=# SELECT pg_size_pretty(citus_relation_size('t2'));
pg_size_pretty
----------------
13 MB
(1 row)

可以看到t1表是分布表大小是4M，t2表是参考表，每个shard都有一个全量复本，所以大小比t1大。其中有3个函数可以计算表大小，函数之间的区别见下表

citus_relation_size 含main fork, 但不含visibility map和free space map
citus_table_size citus_relation_size + visibility map和free space map，但不含索引
citus_total_relation_size citus_table_size + 索引
9、移动shard
192.168.40.143上的shard 102209，我希望将它移到192.168.40.147这台服务器上

192.168.40.143

d1=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | citus_tables | view | postgres
public | t11_102203 | table | postgres
public | t11_102206 | table | postgres
public | t11_102209 | table | postgres
public | t11_102212 | table | postgres
public | t11_102215 | table | postgres
public | t11_102218 | table | postgres
public | t11_102221 | table | postgres
public | t11_102224 | table | postgres
public | t11_102227 | table | postgres
public | t11_102230 | table | postgres
public | t11_102233 | table | postgres
public | t1_102171 | table | postgres
public | t1_102174 | table | postgres
public | t1_102177 | table | postgres
public | t1_102180 | table | postgres
public | t1_102183 | table | postgres
public | t1_102186 | table | postgres
public | t1_102189 | table | postgres
public | t1_102192 | table | postgres
public | t1_102195 | table | postgres
public | t1_102198 | table | postgres
public | t1_102201 | table | postgres
public | t22_102073 | table | postgres
public | t2_102072 | table | postgres
(25 rows)

命令如下

SELECT citus_move_shard_placement(102209, '192.168.40.143', 5432, '192.168.40.147', 5432);

查看2台服务器shard分布情况

192.168.40.143

d1=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | citus_tables | view | postgres
public | t11_102203 | table | postgres
public | t11_102206 | table | postgres
public | t11_102212 | table | postgres
public | t11_102215 | table | postgres
public | t11_102218 | table | postgres
public | t11_102221 | table | postgres
public | t11_102224 | table | postgres
public | t11_102227 | table | postgres
public | t11_102230 | table | postgres
public | t11_102233 | table | postgres
public | t1_102171 | table | postgres
public | t1_102174 | table | postgres
public | t1_102180 | table | postgres
public | t1_102183 | table | postgres
public | t1_102186 | table | postgres
public | t1_102189 | table | postgres
public | t1_102192 | table | postgres
public | t1_102195 | table | postgres
public | t1_102198 | table | postgres
public | t1_102201 | table | postgres
public | t22_102073 | table | postgres
public | t2_102072 | table | postgres
(23 rows)

192.168.40.147

d1=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | citus_tables | view | postgres
public | t11_102204 | table | postgres
public | t11_102207 | table | postgres
public | t11_102209 | table | postgres
public | t11_102210 | table | postgres
public | t11_102213 | table | postgres
public | t11_102216 | table | postgres
public | t11_102219 | table | postgres
public | t11_102222 | table | postgres
public | t11_102225 | table | postgres
public | t11_102228 | table | postgres
public | t11_102231 | table | postgres
public | t1_102172 | table | postgres
public | t1_102175 | table | postgres
public | t1_102177 | table | postgres
public | t1_102178 | table | postgres
public | t1_102181 | table | postgres
public | t1_102184 | table | postgres
public | t1_102187 | table | postgres
public | t1_102190 | table | postgres
public | t1_102193 | table | postgres
public | t1_102196 | table | postgres
public | t1_102199 | table | postgres
public | t22_102073 | table | postgres
public | t2_102072 | table | postgres
(25 rows)

可见102209已经移到了192.168.40.147服务器上

10、查看各节点的连接数情况
d1=# SELECT * from citus_remote_connection_stats();
hostname | port | database_name | connection_count_to_node
----------------+------+---------------+--------------------------
192.168.40.143 | 5432 | d1 | 2
192.168.40.147 | 5432 | d1 | 2
192.168.40.132 | 5432 | d1 | 2
(3 rows)
————————————————
版权声明：本文为CSDN博主「童虎学习笔记」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/tonghu_note/article/details/124548113

相关文章