《童虎学习笔记》5分钟入门PG分布式集群Citus

钮勇

2023-12-01

本专栏全部文章	https://blog.csdn.net/tonghu_note/category_11713514.html
总目录	https://blog.csdn.net/tonghu_note/article/details/124333034

来我的dou音 aa10246666，看配套视频

一、实战环境

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$$)
create_distributed_table
--------------------------

(1 row)

按上面的提示，我们删除本地数据，而不影响workder节点上的数据

SELECT truncate_local_data_after_distributing_table($$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$$)
create_reference_table
------------------------

(1 row)

按上面的提示，我们删除本地数据，而不影响workder节点上的数据

SELECT truncate_local_data_after_distributing_table($$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