PostgreSQL Citus集群验证

安装docker和docker-compose略。 网上都能找到。

官方文档

http://docs.citusdata.com/en/v9.5/installation/single_machine_docker.html

docker-compose.yaml

version: '3'

services:
  master:
    container_name: "${COMPOSE_PROJECT_NAME:-citus}_master"
    image: 'citusdata/citus:9.5.1'
    ports: ["${MASTER_EXTERNAL_PORT:-5433}:5432"]
    labels: ['com.citusdata.role=Master']
    environment: &AUTH
      POSTGRES_USER: "${POSTGRES_USER:-postgres}"
      POSTGRES_PASSWORD: "${POSTGRES_PASSWORD:-postgres}"
      PGUSER: "${POSTGRES_USER:-postgres}"
      PGPASSWORD: "${POSTGRES_PASSWORD:-postgres}"
      POSTGRES_HOST_AUTH_METHOD: "${POSTGRES_HOST_AUTH_METHOD:-trust}"
  worker:
    image: 'citusdata/citus:9.5.1'
    labels: ['com.citusdata.role=Worker']
    depends_on: [ manager ]
    environment: *AUTH
    command: "/wait-for-manager.sh"
    volumes:
      - healthcheck-volume:/healthcheck
  manager:
    container_name: "${COMPOSE_PROJECT_NAME:-citus}_manager"
    image: 'citusdata/membership-manager:0.3.0'
    volumes:
      - "${DOCKER_SOCK:-/var/run/docker.sock}:/var/run/docker.sock"
      - healthcheck-volume:/healthcheck
    depends_on: [ master ]
    environment: *AUTH
volumes:
  healthcheck-volume:

执行 docker-compose up -d 启动服务。

[root@postgresql citus]# docker ps
CONTAINER ID   IMAGE                                      COMMAND                  CREATED             STATUS                   PORTS                     NAMES
56601cdbb38f   citusdata/citus:9.5.1                      "docker-entrypoint.s…"   8 seconds ago       Up 6 seconds (healthy)   5432/tcp                  citus_worker_1
317a2d603a20   citusdata/membership-manager:0.3.0         "python -u ./manager…"   8 seconds ago       Up 7 seconds (healthy)                             citus_manager
ac118f987944   citusdata/citus:9.5.1                      "docker-entrypoint.s…"   8 seconds ago       Up 7 seconds (healthy)   0.0.0.0:5433->5432/tcp    citus_master

citus_master # citus主节点对外暴露提供服务
citus_manager # 容器化citus集群添加删除节点管理
citus_worker_1 # pg数据库节点后端存储

查看状态

查看工作节点

# 进入主节点
[root@postgresql citus]# docker exec -it citus_master psql -U postgres
psql (13.0 (Debian 13.0-1.pgdg100+1))
Type "help" for help.

postgres=# SELECT * FROM master_get_active_worker_nodes();
   node_name    | node_port 
----------------+-----------
 citus_worker_1 |      5432
(1 row)

这里看到子节点已经自动添加。

创建测试数据

1、创建表

create table test_citus(id int, name varchar(16));

# 查看效果
postgres=# \d
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | test_citus | table | postgres
(1 row)

2、设置表分片

SELECT master_create_distributed_table('test_citus', 'id', 'hash');

3、设置表分片规则

设定分片个数(4)及每个分片副本数(1)

SELECT master_create_worker_shards('test_citus', 4, 1);

4、查看分片分布

postgres=# SELECT * from pg_dist_shard_placement order by shardid, placementid;
 shardid | shardstate | shardlength |    nodename    | nodeport | placementid 
---------+------------+-------------+----------------+----------+-------------
  102008 |          1 |           0 | citus_worker_1 |     5432 |           1
  102009 |          1 |           0 | citus_worker_1 |     5432 |           2
  102010 |          1 |           0 | citus_worker_1 |     5432 |           3
  102011 |          1 |           0 | citus_worker_1 |     5432 |           4
(4 rows)

4、创建模拟数据

postgres=# INSERT INTO test_citus (ID,NAME) VALUES (1, 'citus1'), (2, 'citus2'), (3, 'citus3'), (4, 'citus4'), (5, 'citus5'), (6, 'citus6'), (7, 'citus7'), (8, 'citus8'), (9, 'citus9');
INSERT 0 9

5、查询数据

postgres=# select * from test_citus ;
 id |  name  
----+--------
  1 | citus1
  5 | citus5
  8 | citus8
  3 | citus3
  4 | citus4
  7 | citus7
  6 | citus6
  2 | citus2
  9 | citus9
(9 rows)

6、查询节点数据

# 进入节点容器
[root@goalgo-postgresql citus]# docker exec -it citus_worker_1 psql -U postgres
psql (13.0 (Debian 13.0-1.pgdg100+1))
Type "help" for help.

postgres=# \d
               List of relations
 Schema |       Name        | Type  |  Owner   
--------+-------------------+-------+----------
 public | test_citus_102008 | table | postgres
 public | test_citus_102009 | table | postgres
 public | test_citus_102010 | table | postgres
 public | test_citus_102011 | table | postgres
(4 rows)

postgres=# select * from test_citus_102008 ;
 id |  name  
----+--------
  1 | citus1
  5 | citus5
  8 | citus8
(3 rows)

postgres=# select * from test_citus_102009 ;
 id |  name  
----+--------
  3 | citus3
  4 | citus4
  7 | citus7
(3 rows)

postgres=# select * from test_citus_102010 ;
 id |  name  
----+--------
  6 | citus6
(1 row)

postgres=# select * from test_citus_102011 ;
 id |  name  
----+--------
  2 | citus2
  9 | citus9
(2 rows)

数据被分散到不同的表里面了。

部署搭建参考：
https://blog.csdn.net/liushimiao0104/article/details/81450176
https://www.jianshu.com/p/ab4b86602fa1
https://www.jianshu.com/p/639ebb43bfb4

新建立库使用分片 验证

主节点和子节点都要执行

一步一步每个节点都要执行。
# 创建新DB
create database new;
# 查看DB
\l
# 进入DB
\c new
# 为当前数据库添加扩展库
CREATE EXTENSION citus;
# 为Coordinator添加子节点
SELECT * from master_add_node('citus_worker_1', 5432);
# 查看节点是否添加成功。
SELECT * FROM master_get_active_worker_nodes();

下面是执行后的提示

[root@goalgo-postgresql ~]# docker exec -it citus_worker_1 psql -U postgres
psql (13.0 (Debian 13.0-1.pgdg100+1))
Type "help" for help.

postgres=# create database new;
NOTICE:  Citus partially supports CREATE DATABASE for distributed databases
DETAIL:  Citus does not propagate CREATE DATABASE command to workers
HINT:  You can manually create a database and its extensions on workers.
CREATE DATABASE

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 new       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)


postgres=# \c new
You are now connected to database "new" as user "postgres".
new=# CREATE EXTENSION citus;
CREATE EXTENSION
new=# SELECT * from master_add_node('citus_worker_1', 5432);
 master_add_node 
-----------------
               1
(1 row)

new=# SELECT * FROM master_get_active_worker_nodes();
   node_name    | node_port 
----------------+-----------
 citus_worker_1 |      5432
(1 row)

主节点创建表验证

new=# create table test_table(id int, name varchar(16));
CREATE TABLE
new=# SELECT master_create_distributed_table('test_table', 'id', 'hash');
 master_create_distributed_table 
---------------------------------
 
(1 row)

new=# SELECT master_create_worker_shards('test_table', 2, 1);
 master_create_worker_shards 
-----------------------------
 
(1 row)

new=# SELECT * from pg_dist_shard_placement order by shardid, placementid;
 shardid | shardstate | shardlength |    nodename    | nodeport | placementid 
---------+------------+-------------+----------------+----------+-------------
  102008 |          1 |           0 | citus_worker_1 |     5432 |           1
  102009 |          1 |           0 | citus_worker_1 |     5432 |           2
(2 rows)

new=# INSERT INTO test_table (ID,NAME) VALUES (1, 'citus1'), (2, 'citus2'), (3, 'citus3'), (4, 'citus4'), (5, 'citus5'), (6, 'citus6'), (7, 'citus7'), (8, 'citus8'), (9, 'citus9');
INSERT 0 9
new=# select * from test_table;
 id |  name  
----+--------
  1 | citus1
  3 | citus3
  4 | citus4
  5 | citus5
  7 | citus7
  8 | citus8
  2 | citus2
  6 | citus6
  9 | citus9
(9 rows)

子节点验证数据

[root@postgresql ~]# docker exec -it citus_worker_1 psql -U new
psql: error: could not connect to server: FATAL:  role "new" does not exist
[root@goalgo-postgresql ~]# docker exec -it citus_worker_1 psql -U postgres
psql (13.0 (Debian 13.0-1.pgdg100+1))
Type "help" for help.

postgres=# \c new
You are now connected to database "new" as user "postgres".
new=# \d
               List of relations
 Schema |       Name        | Type  |  Owner   
--------+-------------------+-------+----------
 public | test_table_102008 | table | postgres
 public | test_table_102009 | table | postgres
(2 rows)

new=# select * from test_table_102008;
 id |  name  
----+--------
  1 | citus1
  3 | citus3
  4 | citus4
  5 | citus5
  7 | citus7
  8 | citus8
(6 rows)

new=# select * from test_table_102009;
 id |  name  
----+--------
  2 | citus2
  6 | citus6
  9 | citus9
(3 rows)