5分钟入门PG分布式集群Citus
一、实战环境
Citus是Postgres的一个extension扩展，将Postgres转换成一个分布式数据库，在集群的多个节点上分发数据和查询，具有像分片、分布式SQL引擎、复制表和分布式表等特性。
节点 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
二、安装Citus
所有节点（cn, worker1, worker2）都做如下操作
1、安装Citus
curl https://install.citusdata.com/community/deb.sh | bash
apt-get -y install postgresql-14-citus-10.2
pg_conftool 14 main set shared_preload_libraries citus
pg_conftool 14 main set listen_addresses '*'
2、配置PG
文件 /etc/postgresql/14/main/pg_hba.conf 中添加
host all postgres 192.168.40.0/24 trust
host all appuser 192.168.40.0/24 trust
其中postgres是系统用户用于添加节点、创建分布表、参考表等操作，appuser是应用程序连接cn节点使用数据库的帐号
重启PG使配置生效
systemctl restart postgresql
3、新建样例数据库并授权
在这里我们新建了一个数据库d1, 又新建了一个用户appuser, 又在d1数据库里新建了扩展citus, 并授予appuser用户对d1数据库的all权限
create database d1;
CREATE user appuser;
\c d1
CREATE EXTENSION citus;
grant all on database d1 to appuser;
grant all on all tables in schema public to appuser;
grant all on all sequences in schema public to appuser;
grant all on all functions in schema public to appuser;
alter default privileges for user postgres in schema public grant all on tables to appuser;
alter default privileges for user postgres in schema public grant all on sequences to appuser;
三、添加worker节点
仅在cn节点做如下操作
1、添加2个worker节点
\c d1
SELECT * from citus_add_node('192.168.40.143', 5432);
SELECT * from citus_add_node('192.168.40.147', 5432);
SELECT rebalance_table_shards();
检查worker节点信息是否正确添加
d1=# SELECT * FROM citus_get_active_worker_nodes();
node_name | node_port
----------------+-----------
192.168.40.147 | 5432
192.168.40.143 | 5432
(2 rows)
四、创建distributed分布表
分布表的特点是数据分布在每个worker节点上
1、方式1, 先建表，再将其设置为分布表，再向表中插入数据
在cn节点的d1上建表t1
\c d1
CREATE TABLE t1 (
id bigint primary key,
name text
);
以id列为分片键，将表设置为分布表, 默认一共创建32个分片，平均分配到每台worker上
SELECT create_distributed_table('t1', 'id');
此时到cn、worker1和worker2上查看一下t1表的分布情况
cn节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+------------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t1 | table | postgres | permanent | heap | 8192 bytes |
(2 rows)
worker1节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+------------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t1_102008 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102010 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102012 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102014 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102016 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102018 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102020 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102022 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102024 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102026 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102028 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102030 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102032 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102034 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102036 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102038 | table | postgres | permanent | heap | 8192 bytes |
(17 rows)
d1=#
worker2节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+------------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t1_102009 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102011 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102013 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102015 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102017 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102019 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102021 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102023 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102025 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102027 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102029 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102031 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102033 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102035 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102037 | table | postgres | permanent | heap | 8192 bytes |
public | t1_102039 | table | postgres | permanent | heap | 8192 bytes |
(17 rows)
可以看到cn节点的表是逻辑表，2个worker节点是是分片后的表，接下来我们在cn节点插入数据
INSERT INTO t1 (id, name) select generate_series(1,100000), 'aaa';
此时再到cn、worker1和worker2上查看一下t1表的分布情况
cn节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+------------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t1 | table | postgres | permanent | heap | 8192 bytes |
(2 rows)
worker1节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+---------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t1_102008 | table | postgres | permanent | heap | 176 kB |
public | t1_102010 | table | postgres | permanent | heap | 176 kB |
public | t1_102012 | table | postgres | permanent | heap | 176 kB |
public | t1_102014 | table | postgres | permanent | heap | 176 kB |
public | t1_102016 | table | postgres | permanent | heap | 176 kB |
public | t1_102018 | table | postgres | permanent | heap | 184 kB |
public | t1_102020 | table | postgres | permanent | heap | 176 kB |
public | t1_102022 | table | postgres | permanent | heap | 176 kB |
public | t1_102024 | table | postgres | permanent | heap | 176 kB |
public | t1_102026 | table | postgres | permanent | heap | 176 kB |
public | t1_102028 | table | postgres | permanent | heap | 176 kB |
public | t1_102030 | table | postgres | permanent | heap | 184 kB |
public | t1_102032 | table | postgres | permanent | heap | 168 kB |
public | t1_102034 | table | postgres | permanent | heap | 184 kB |
public | t1_102036 | table | postgres | permanent | heap | 176 kB |
public | t1_102038 | table | postgres | permanent | heap | 176 kB |
(17 rows)
worker2节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+---------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t1_102009 | table | postgres | permanent | heap | 168 kB |
public | t1_102011 | table | postgres | permanent | heap | 184 kB |
public | t1_102013 | table | postgres | permanent | heap | 176 kB |
public | t1_102015 | table | postgres | permanent | heap | 176 kB |
public | t1_102017 | table | postgres | permanent | heap | 184 kB |
public | t1_102019 | table | postgres | permanent | heap | 176 kB |
public | t1_102021 | table | postgres | permanent | heap | 176 kB |
public | t1_102023 | table | postgres | permanent | heap | 184 kB |
public | t1_102025 | table | postgres | permanent | heap | 176 kB |
public | t1_102027 | table | postgres | permanent | heap | 184 kB |
public | t1_102029 | table | postgres | permanent | heap | 176 kB |
public | t1_102031 | table | postgres | permanent | heap | 176 kB |
public | t1_102033 | table | postgres | permanent | heap | 184 kB |
public | t1_102035 | table | postgres | permanent | heap | 176 kB |
public | t1_102037 | table | postgres | permanent | heap | 176 kB |
public | t1_102039 | table | postgres | permanent | heap | 176 kB |
(17 rows)
可以看到当在cn节点对t1表插入数据后，cn节点上的表大小没有变化，说明cn节点不存储数据。同时查看worker1和worker2节点表t1的大小变大了，说明worker节点是实际存储数据的。
在cn节点发起一个查询，验证可以正常返回结果
d1=# SELECT * FROM t1 WHERE id = 1;
id | name
----+------
1 | aaa
(1 row)
2、方式1, 先建表，再向表中插入数据，再将其设置为分布表
在cn节点的d1上建表t11
\c d1
CREATE TABLE t11 (
id bigint primary key,
name text
);
在cn节点先插入数据
INSERT INTO t11 (id, name) select generate_series(1,100000), 'aaa';
以id列为分片键，将表设置为分布表, 默认一共创建32个分片，平均分配到每台worker上
d1=# SELECT create_distributed_table('t11', 'id');
NOTICE: Copying data from local table...
NOTICE: copying the data has completed
DETAIL: The local data in the table is no longer visible, but is still on disk.
HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table(
public.t11
public.t11
)
create_distributed_table
--------------------------
(1 row)
按上面的提示，我们删除本地数据，而不影响workder节点上的数据
SELECT truncate_local_data_after_distributing_table(
public.t11
public.t11
);
此时到cn、worker1和worker2上查看一下t11表的分布情况
cn节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+------------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t1 | table | postgres | permanent | heap | 8192 bytes |
public | t11 | table | postgres | permanent | heap | 8192 bytes |
(3 rows)
worker1节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+---------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t11_102040 | table | postgres | permanent | heap | 176 kB |
public | t11_102042 | table | postgres | permanent | heap | 176 kB |
public | t11_102044 | table | postgres | permanent | heap | 176 kB |
public | t11_102046 | table | postgres | permanent | heap | 176 kB |
public | t11_102048 | table | postgres | permanent | heap | 176 kB |
public | t11_102050 | table | postgres | permanent | heap | 152 kB |
public | t11_102052 | table | postgres | permanent | heap | 176 kB |
public | t11_102054 | table | postgres | permanent | heap | 176 kB |
public | t11_102056 | table | postgres | permanent | heap | 176 kB |
public | t11_102058 | table | postgres | permanent | heap | 176 kB |
public | t11_102060 | table | postgres | permanent | heap | 176 kB |
public | t11_102062 | table | postgres | permanent | heap | 184 kB |
public | t11_102064 | table | postgres | permanent | heap | 176 kB |
public | t11_102066 | table | postgres | permanent | heap | 184 kB |
public | t11_102068 | table | postgres | permanent | heap | 176 kB |
public | t11_102070 | table | postgres | permanent | heap | 176 kB |
public | t1_102008 | table | postgres | permanent | heap | 176 kB |
public | t1_102010 | table | postgres | permanent | heap | 176 kB |
public | t1_102012 | table | postgres | permanent | heap | 176 kB |
public | t1_102014 | table | postgres | permanent | heap | 176 kB |
public | t1_102016 | table | postgres | permanent | heap | 176 kB |
public | t1_102018 | table | postgres | permanent | heap | 184 kB |
public | t1_102020 | table | postgres | permanent | heap | 176 kB |
public | t1_102022 | table | postgres | permanent | heap | 176 kB |
public | t1_102024 | table | postgres | permanent | heap | 176 kB |
public | t1_102026 | table | postgres | permanent | heap | 176 kB |
public | t1_102028 | table | postgres | permanent | heap | 176 kB |
public | t1_102030 | table | postgres | permanent | heap | 184 kB |
public | t1_102032 | table | postgres | permanent | heap | 176 kB |
public | t1_102034 | table | postgres | permanent | heap | 184 kB |
public | t1_102036 | table | postgres | permanent | heap | 176 kB |
public | t1_102038 | table | postgres | permanent | heap | 176 kB |
(33 rows)
worker2节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+---------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t11_102041 | table | postgres | permanent | heap | 176 kB |
public | t11_102043 | table | postgres | permanent | heap | 184 kB |
public | t11_102045 | table | postgres | permanent | heap | 176 kB |
public | t11_102047 | table | postgres | permanent | heap | 176 kB |
public | t11_102049 | table | postgres | permanent | heap | 184 kB |
public | t11_102051 | table | postgres | permanent | heap | 176 kB |
public | t11_102053 | table | postgres | permanent | heap | 176 kB |
public | t11_102055 | table | postgres | permanent | heap | 184 kB |
public | t11_102057 | table | postgres | permanent | heap | 176 kB |
public | t11_102059 | table | postgres | permanent | heap | 184 kB |
public | t11_102061 | table | postgres | permanent | heap | 176 kB |
public | t11_102063 | table | postgres | permanent | heap | 176 kB |
public | t11_102065 | table | postgres | permanent | heap | 184 kB |
public | t11_102067 | table | postgres | permanent | heap | 176 kB |
public | t11_102069 | table | postgres | permanent | heap | 176 kB |
public | t11_102071 | table | postgres | permanent | heap | 176 kB |
public | t1_102009 | table | postgres | permanent | heap | 176 kB |
public | t1_102011 | table | postgres | permanent | heap | 184 kB |
public | t1_102013 | table | postgres | permanent | heap | 176 kB |
public | t1_102015 | table | postgres | permanent | heap | 176 kB |
public | t1_102017 | table | postgres | permanent | heap | 184 kB |
public | t1_102019 | table | postgres | permanent | heap | 176 kB |
public | t1_102021 | table | postgres | permanent | heap | 176 kB |
public | t1_102023 | table | postgres | permanent | heap | 184 kB |
public | t1_102025 | table | postgres | permanent | heap | 176 kB |
public | t1_102027 | table | postgres | permanent | heap | 184 kB |
public | t1_102029 | table | postgres | permanent | heap | 176 kB |
public | t1_102031 | table | postgres | permanent | heap | 176 kB |
public | t1_102033 | table | postgres | permanent | heap | 184 kB |
public | t1_102035 | table | postgres | permanent | heap | 176 kB |
public | t1_102037 | table | postgres | permanent | heap | 176 kB |
public | t1_102039 | table | postgres | permanent | heap | 176 kB |
public | t2_102072 | table | postgres | permanent | heap | 4360 kB |
(34 rows)
经观察，说明cn节点不存储数据，说明worker节点是实际存储数据的。
五、创建reference参考表
参考表的特点是数据分布在每个worker节点上且每个节点上都一份而经分片的全量数据，正是因为每个节点都有一份全量数据，所以可以减小对参考表进行关联时查询的耗时。
1、方式1, 先建表，再将其设置为参考表，再向表中插入数据
在cn节点的d1上建表t2
\c d1
CREATE TABLE t2 (
id bigint primary key,
name text
);
将t2表配置为参考表
SELECT create_reference_table('t2');
注意参考表没有分片键，所以此外只接受一个表名做为参数
插入数据
INSERT INTO t2 (id, name) select generate_series(1,100000), 'aaa';
此时到cn、worker1和worker2上查看一下t2表的分布情况
cn节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+------------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t1 | table | postgres | permanent | heap | 8192 bytes |
public | t11 | table | postgres | permanent | heap | 8192 bytes |
public | t2 | table | postgres | permanent | heap | 8192 bytes |
(4 rows)
worker1节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+---------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t11_102040 | table | postgres | permanent | heap | 176 kB |
public | t11_102042 | table | postgres | permanent | heap | 176 kB |
public | t11_102044 | table | postgres | permanent | heap | 176 kB |
public | t11_102046 | table | postgres | permanent | heap | 176 kB |
public | t11_102048 | table | postgres | permanent | heap | 176 kB |
public | t11_102050 | table | postgres | permanent | heap | 184 kB |
public | t11_102052 | table | postgres | permanent | heap | 176 kB |
public | t11_102054 | table | postgres | permanent | heap | 176 kB |
public | t11_102056 | table | postgres | permanent | heap | 176 kB |
public | t11_102058 | table | postgres | permanent | heap | 176 kB |
public | t11_102060 | table | postgres | permanent | heap | 176 kB |
public | t11_102062 | table | postgres | permanent | heap | 184 kB |
public | t11_102064 | table | postgres | permanent | heap | 176 kB |
public | t11_102066 | table | postgres | permanent | heap | 184 kB |
public | t11_102068 | table | postgres | permanent | heap | 176 kB |
public | t11_102070 | table | postgres | permanent | heap | 176 kB |
public | t1_102008 | table | postgres | permanent | heap | 176 kB |
public | t1_102010 | table | postgres | permanent | heap | 176 kB |
public | t1_102012 | table | postgres | permanent | heap | 176 kB |
public | t1_102014 | table | postgres | permanent | heap | 176 kB |
public | t1_102016 | table | postgres | permanent | heap | 176 kB |
public | t1_102018 | table | postgres | permanent | heap | 184 kB |
public | t1_102020 | table | postgres | permanent | heap | 176 kB |
public | t1_102022 | table | postgres | permanent | heap | 176 kB |
public | t1_102024 | table | postgres | permanent | heap | 176 kB |
public | t1_102026 | table | postgres | permanent | heap | 176 kB |
public | t1_102028 | table | postgres | permanent | heap | 176 kB |
public | t1_102030 | table | postgres | permanent | heap | 184 kB |
public | t1_102032 | table | postgres | permanent | heap | 176 kB |
public | t1_102034 | table | postgres | permanent | heap | 184 kB |
public | t1_102036 | table | postgres | permanent | heap | 176 kB |
public | t1_102038 | table | postgres | permanent | heap | 176 kB |
public | t2_102072 | table | postgres | permanent | heap | 4360 kB |
(34 rows)
worker2节点
d1=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+---------+-------------
public | citus_tables | view | postgres | permanent | | 0 bytes |
public | t11_102041 | table | postgres | permanent | heap | 176 kB |
public | t11_102043 | table | postgres | permanent | heap | 184 kB |
public | t11_102045 | table | postgres | permanent | heap | 176 kB |
public | t11_102047 | table | postgres | permanent | heap | 176 kB |
public | t11_102049 | table | postgres | permanent | heap | 184 kB |
public | t11_102051 | table | postgres | permanent | heap | 176 kB |
public | t11_102053 | table | postgres | permanent | heap | 176 kB |
public | t11_102055 | table | postgres | permanent | heap | 184 kB |
public | t11_102057 | table | postgres | permanent | heap | 176 kB |
public | t11_102059 | table | postgres | permanent | heap | 184 kB |
public | t11_102061 | table | postgres | permanent | heap | 176 kB |
public | t11_102063 | table | postgres | permanent | heap | 176 kB |
public | t11_102065 | table | postgres | permanent | heap | 184 kB |
public | t11_102067 | table | postgres | permanent | heap | 176 kB |
public | t11_102069 | table | postgres | permanent | heap | 176 kB |
public | t11_102071 | table | postgres | permanent | heap | 176 kB |
public | t1_102009 | table | postgres | permanent | heap | 176 kB |
public | t1_102011 | table | postgres | permanent | heap | 184 kB |
public | t1_102013 | table | postgres | permanent | heap | 176 kB |
public | t1_102015 | table | postgres | permanent | heap | 176 kB |
public | t1_102017 | table | postgres | permanent | heap | 184 kB |
public | t1_102019 | table | postgres | permanent | heap | 176 kB |
public | t1_102021 | table | postgres | permanent | heap | 176 kB |
public | t1_102023 | table | postgres | permanent | heap | 184 kB |
public | t1_102025 | table | postgres | permanent | heap | 176 kB |
public | t1_102027 | table | postgres | permanent | heap | 184 kB |
public | t1_102029 | table | postgres | permanent | heap | 176 kB |
public | t1_102031 | table | postgres | permanent | heap | 176 kB |
public | t1_102033 | table | postgres | permanent | heap | 184 kB |
public | t1_102035 | table | postgres | permanent | heap | 176 kB |
public | t1_102037 | table | postgres | permanent | heap | 176 kB |
public | t1_102039 | table | postgres | permanent | heap | 176 kB |
public | t2_102072 | table | postgres | permanent | heap | 4360 kB |
(34 rows)
2、方式1, 先建表，再向表中插入数据，再将其设置为参考表
在cn节点的d1上建表t22
\c d1
CREATE TABLE t22 (
id bigint primary key,
name text
);
插入数据
INSERT INTO t22 (id, name) select generate_series(1,100000), 'aaa';
将表t22配置为参考表
d1=# SELECT create_reference_table('t22');
NOTICE: Copying data from local table...
NOTICE: copying the data has completed
DETAIL: The local data in the table is no longer visible, but is still on disk.
HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table(
public.t22
public.t22
)
create_reference_table
------------------------
(1 row)
按上面的提示，我们删除本地数据，而不影响workder节点上的数据
SELECT truncate_local_data_after_distributing_table(
public.t22
public.t22
);
此时到cn、worker1和worker2上查看一下t22表的分布情况
\c d1
\d+
六、查看表分布情况
在cn节点操作
1、查看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)
通过上面可以清晰的看出来系统中现在有2个分布表分别是t1和t11，有2上参考表分别是t2和t22
2、查询表的分片情况
d1=# SELECT * from pg_dist_shard;
logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue
--------------+---------+--------------+---------------+---------------
t1 | 102008 | t | -2147483648 | -2013265921
t1 | 102009 | t | -2013265920 | -1879048193
t1 | 102010 | t | -1879048192 | -1744830465
t1 | 102011 | t | -1744830464 | -1610612737
t1 | 102012 | t | -1610612736 | -1476395009
t1 | 102013 | t | -1476395008 | -1342177281
t1 | 102014 | t | -1342177280 | -1207959553
t1 | 102015 | t | -1207959552 | -1073741825
t1 | 102016 | t | -1073741824 | -939524097
t1 | 102017 | t | -939524096 | -805306369
t1 | 102018 | t | -805306368 | -671088641
t1 | 102019 | t | -671088640 | -536870913
t1 | 102020 | t | -536870912 | -402653185
t1 | 102021 | t | -402653184 | -268435457
t1 | 102022 | t | -268435456 | -134217729
t1 | 102023 | t | -134217728 | -1
t1 | 102024 | t | 0 | 134217727
t1 | 102025 | t | 134217728 | 268435455
t1 | 102026 | t | 268435456 | 402653183
t1 | 102027 | t | 402653184 | 536870911
t1 | 102028 | t | 536870912 | 671088639
t1 | 102029 | t | 671088640 | 805306367
t1 | 102030 | t | 805306368 | 939524095
t1 | 102031 | t | 939524096 | 1073741823
t1 | 102032 | t | 1073741824 | 1207959551
t1 | 102033 | t | 1207959552 | 1342177279
t1 | 102034 | t | 1342177280 | 1476395007
t1 | 102035 | t | 1476395008 | 1610612735
t1 | 102036 | t | 1610612736 | 1744830463
t1 | 102037 | t | 1744830464 | 1879048191
t1 | 102038 | t | 1879048192 | 2013265919
t1 | 102039 | t | 2013265920 | 2147483647
t11 | 102040 | t | -2147483648 | -2013265921
t11 | 102041 | t | -2013265920 | -1879048193
t11 | 102042 | t | -1879048192 | -1744830465
t11 | 102043 | t | -1744830464 | -1610612737
t11 | 102044 | t | -1610612736 | -1476395009
t11 | 102045 | t | -1476395008 | -1342177281
t11 | 102046 | t | -1342177280 | -1207959553
t11 | 102047 | t | -1207959552 | -1073741825
t11 | 102048 | t | -1073741824 | -939524097
t11 | 102049 | t | -939524096 | -805306369
t11 | 102050 | t | -805306368 | -671088641
t11 | 102051 | t | -671088640 | -536870913
t11 | 102052 | t | -536870912 | -402653185
t11 | 102053 | t | -402653184 | -268435457
t11 | 102054 | t | -268435456 | -134217729
t11 | 102055 | t | -134217728 | -1
t11 | 102056 | t | 0 | 134217727
t11 | 102057 | t | 134217728 | 268435455
t11 | 102058 | t | 268435456 | 402653183
t11 | 102059 | t | 402653184 | 536870911
t11 | 102060 | t | 536870912 | 671088639
t11 | 102061 | t | 671088640 | 805306367
t11 | 102062 | t | 805306368 | 939524095
t11 | 102063 | t | 939524096 | 1073741823
t11 | 102064 | t | 1073741824 | 1207959551
t11 | 102065 | t | 1207959552 | 1342177279
t11 | 102066 | t | 1342177280 | 1476395007
t11 | 102067 | t | 1476395008 | 1610612735
t11 | 102068 | t | 1610612736 | 1744830463
t11 | 102069 | t | 1744830464 | 1879048191
t11 | 102070 | t | 1879048192 | 2013265919
t11 | 102071 | t | 2013265920 | 2147483647
t2 | 102072 | t | |
t22 | 102073 | t | |
(66 rows)
通过上面的内容，假设我们要查找t11表的id=50的记录所在的分片的哪个shard上，可以先查出它所在的shardid
d1=# SELECT get_shard_id_for_distribution_column('t11', 50);
get_shard_id_for_distribution_column
--------------------------------------
102209
(1 row)
但这个shard在哪台服务器的哪个表里呢，可以通过下面的语句查询出来
SELECT
shardid,
node.nodename,
node.nodeport
FROM pg_dist_placement placement
JOIN pg_dist_node node
ON placement.groupid = node.groupid
AND node.noderole = 'primary'::noderole
WHERE shardid = 102209;
shardid | nodename | nodeport
---------+----------------+----------
102209 | 192.168.40.143 | 5432
(1 row)
可以看到t11表的id=50的值所在的分片，是192.168.40.143这个worker节点的t11_102209这张分布表 ，我们实际到192.168.40.143查一下看有没有这条数据
d1=# select * from t11_102209 where id=50;
id | name | id2
----+------+-----
50 | aaa | 55
(1 row)
七、作业
大家连接cn进行增删改查的测试
psql -h 192.168.40.142 -p 5432 -U appuser d1
————————————————
版权声明：本文为CSDN博主「童虎学习笔记」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/tonghu_note/article/details/124537330
相关文章