PostgreSQL基于Citus实现的分布式集群

文章目录

• 前言
• • 📣 1.Citus介绍
  • 📣 2.架构设计
  • 📣 3.安装citus
  • 📣 4.集群配置

前言

因客户的需求，本文详细阐述了PostgreSQL基于Citus实现的分布式集群的全过程。

---

📣 1.Citus介绍

📣 2.架构设计

📣 3.安装citus

✨ 3.1 yum安装

✨ 3.2 创建拓展

##以下操作，所有节点均操作

su - postgres
psql
create database clusterdb;
\c clusterdb
alter system set shared_preload_libraries='citus';
clusterdb=# select pg_reload_conf();
clusterdb=# select * from pg_available_extensions where name='citus';
 name  | default_version | installed_version |          comment           
-------+-----------------+-------------------+----------------------------
 citus | 12.1-1          |                   | Citus distributed database
(1 row)
clusterdb=# \q

systemctl status postgresql-14
systemctl restart postgresql-14

root@pgcn:~$ su - postgres
root@pgcn:~$ psql

postgres=# \c clusterdb
You are now connected to database "clusterdb" as user "postgres".

clusterdb=# show shared_preload_libraries;
 shared_preload_libraries 
--------------------------
 citus
(1 row)

clusterdb=# create extension citus;
CREATE EXTENSION

clusterdb=# \dx
                     List of installed extensions
      Name      | Version |   Schema   |         Description          
----------------+---------+------------+------------------------------
 citus          | 12.1-1  | pg_catalog | Citus distributed database
 citus_columnar | 11.3-1  | pg_catalog | Citus Columnar extension
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

clusterdb=# \dx+ citus

📣 4.集群配置

✨ 4.1修改配置文件

✨ 4.2 新增节点

✨ 4.3 集群信息

-- 查看工作节点
clusterdb=# SELECT * FROM master_get_active_worker_nodes();
  node_name   | node_port 
--------------+-----------
 172.18.12.83 |      5432
 172.18.12.81 |      5432
 172.18.12.84 |      5432
 172.18.12.82 |      5432
(4 rows)

clusterdb=# select * from pg_dist_node;
 nodeid | groupid |   nodename   | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards 
--------+---------+--------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
     43 |      43 | 172.18.12.82 |     5432 | default  | t           | t        | primary  | default     | t              | t
     44 |      44 | 172.18.12.83 |     5432 | default  | t           | t        | primary  | default     | t              | t
     46 |      46 | 172.18.12.84 |     5432 | default  | t           | t        | primary  | default     | t              | t
     42 |      42 | 172.18.12.81 |     5432 | default  | t           | t        | primary  | default     | t              | t