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

本文链接：https://blog.csdn.net/tonghu_note/article/details/124537330

本文介绍了如何使用Citus扩展将PostgreSQL转变为分布式数据库，详细阐述了安装Citus、配置数据库、添加worker节点、创建分布式和参考表的过程，并展示了表的数据分布情况。适合希望学习分布式数据库管理的读者。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本专栏全部文章	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 |