PostgreSQL运维—分布式Citus单机多实例部署

原创李先生 2022-07-20

2660

分布式Citus单机多实例部署

什么是Citus

Citus是一个PostgreSQL扩展，可将Postgres转换为分布式数据库，因此您可以在任何规模上实现高性能。

使用Citus，您可以使用新的超能力扩展PostgreSQL数据库：

分布式表在PostgreSQL节点集群中进行分片，以组合它们的CPU、内存、存储和I/O容量。
引用表被复制到所有节点，以实现分布式表中的连接和外键以及最大的读取性能。
分布式查询引擎在整个集群中对分布式表上的SELECT、DML和其他操作进行路由和并行化。
列式存储可压缩数据、加快扫描速度并支持快速投影，适用于常规表和分布式表。
来自任何节点的查询使您能够利用集群的全部容量进行分布式查询

您可以使用这些Citus超级功能让您的Postgres数据库在单个Citus节点上进行横向扩展。或者，您可以构建一个能够处理高事务吞吐量的大型集群，尤其是在多租户应用程序中，运行快速分析查询，并处理大量时间序列或物联网数据以进行实时分析。当您的数据大小和容量增长时，您可以轻松地将更多工作节点添加到集群并重新平衡分片。

环境描述

节点	端口
CN节点	5432
Worker01节点	5433
Worker02节点	5434

安装PG软件

（略）

安装Citus

准备安装包

下载地址：

https://www.modb.pro/download/707405

https://github.com/citusdata/citus

[postgres@lxs02 ~]$ ll
total 5404
-rw-r--r--. 1 postgres postgres 5531853 Jan 11 16:06 citus-10.2.3.tar.gz
[postgres@lxs02 ~]$
复制

安装依赖包

yum install -y libcurl-devel lz4 zstd epel-release libzstd-devel
复制

解压软件

[postgres@lxs02 ~]$ tar -zxf citus-10.2.3.tar.gz 
[postgres@lxs02 ~]$ cd citus-10.2.3/
[postgres@lxs02 citus-10.2.3]$ ll
total 528
-rw-rw-r--. 1 postgres postgres     47 Nov 29 16:50 aclocal.m4
-rwxrwxr-x. 1 postgres postgres    213 Nov 29 16:50 autogen.sh
-rw-rw-r--. 1 postgres postgres   2241 Nov 29 16:50 cgmanifest.json
-rw-rw-r--. 1 postgres postgres  66893 Nov 29 16:50 CHANGELOG.md
drwxrwxr-x. 2 postgres postgres   4096 Nov 29 16:50 ci
-rwxrwxr-x. 1 postgres postgres  96184 Nov 29 16:50 citus-architecture.png
-rwxrwxr-x. 1 postgres postgres  21985 Nov 29 16:50 citus-readme-banner.png
-rwxrwxr-x. 1 postgres postgres  17874 Nov 29 16:50 citus-scale-out.png
drwxrwxr-x. 2 postgres postgres     44 Nov 29 16:50 config
-rw-rw-r--. 1 postgres postgres  19886 May 14 03:36 config.log
-rwxrwxr-x. 1 postgres postgres 178319 Nov 29 16:50 configure
-rw-rw-r--. 1 postgres postgres  12406 Nov 29 16:50 configure.in
-rw-rw-r--. 1 postgres postgres   5830 Nov 29 16:50 CONTRIBUTING.md
-rw-rw-r--. 1 postgres postgres  34520 Nov 29 16:50 LICENSE
-rw-rw-r--. 1 postgres postgres   1698 Nov 29 16:50 Makefile
-rw-rw-r--. 1 postgres postgres   4007 Nov 29 16:50 Makefile.global.in
-rw-rw-r--. 1 postgres postgres   4417 Nov 29 16:50 NOTICE
-rw-rw-r--. 1 postgres postgres   1363 Nov 29 16:50 prep_buildtree
-rw-rw-r--. 1 postgres postgres  27710 Nov 29 16:50 README.md
drwxrwxr-x. 5 postgres postgres     48 Nov 29 16:50 src
drwxrwxr-x. 3 postgres postgres     44 Nov 29 16:50 vendor
[postgres@lxs02 citus-10.2.3]$ 
复制

安装软件

安装.1

[postgres@lxs02 citus-10.2.3]$ ./configure PG_CONFIG=/opt/pgsql14.2/bin/pg_config 
checking for a sed that does not truncate output... /bin/sed
checking for gawk... gawk
。。。
checking curl/curl.h usability... yes
checking curl/curl.h presence... yes
checking for curl/curl.h... yes
checking for LZ4_compress_default in -llz4... no
configure: error: lz4 library not found
If you have lz4 installed, see config.log for details on the
failure.  It is possible the compiler isn't looking in the proper directory.
Use --without-lz4 to disable lz4 support.
[postgres@lxs02 citus-10.2.3]$ 
复制

安装LZ4

[root@lxs02 ~]# ls -rlt lz4*
-rw-r--r--. 1 root root 26784 May 12 00:04 lz4-devel-1.8.3-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 86572 May 12 00:13 lz4-1.8.3-1.el7.x86_64.rpm
[root@lxs02 ~]# 
[root@lxs02 ~]# yum -y install lz4-1.8.3-1.el7.x86_64.rpm 
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Examining lz4-1.8.3-1.el7.x86_64.rpm: lz4-1.8.3-1.el7.x86_64
Marking lz4-1.8.3-1.el7.x86_64.rpm as an update to lz4-1.7.5-2.el7.x86_64
Resolving Dependencies
--> Running transaction check
---> Package lz4.x86_64 0:1.7.5-2.el7 will be updated
---> Package lz4.x86_64 0:1.8.3-1.el7 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

=========================================================================================================================================================================================================================================
 Package                                         Arch                                               Version                                                    Repository                                                           Size
=========================================================================================================================================================================================================================================
Updating:
 lz4                                             x86_64                                             1.8.3-1.el7                                                /lz4-1.8.3-1.el7.x86_64                                             201 k

Transaction Summary
=========================================================================================================================================================================================================================================
Upgrade  1 Package

Total size: 201 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Updating   : lz4-1.8.3-1.el7.x86_64                                                                                                                                                                                                1/2 
  Cleanup    : lz4-1.7.5-2.el7.x86_64                                                                                                                                                                                                2/2 
  Verifying  : lz4-1.8.3-1.el7.x86_64                                                                                                                                                                                                1/2 
  Verifying  : lz4-1.7.5-2.el7.x86_64                                                                                                                                                                                                2/2 

Updated:
  lz4.x86_64 0:1.8.3-1.el7                                                                                                                                                                                                               

Complete!
[root@lxs02 ~]# 
[root@lxs02 ~]# yum -y install lz4-devel-1.8.3-1.el7.x86_64.rpm 
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Examining lz4-devel-1.8.3-1.el7.x86_64.rpm: lz4-devel-1.8.3-1.el7.x86_64
Marking lz4-devel-1.8.3-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package lz4-devel.x86_64 0:1.8.3-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=========================================================================================================================================================================================================================================
 Package                                            Arch                                            Version                                                 Repository                                                              Size
=========================================================================================================================================================================================================================================
Installing:
 lz4-devel                                          x86_64                                          1.8.3-1.el7                                             /lz4-devel-1.8.3-1.el7.x86_64                                           77 k

Transaction Summary
=========================================================================================================================================================================================================================================
Install  1 Package

Total size: 77 k
Installed size: 77 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : lz4-devel-1.8.3-1.el7.x86_64                                                                                                                                                                                          1/1 
  Verifying  : lz4-devel-1.8.3-1.el7.x86_64                                                                                                                                                                                          1/1 

Installed:
  lz4-devel.x86_64 0:1.8.3-1.el7                                                                                                                                                                                                         

Complete!
[root@lxs02 ~]# 
[root@lxs02 ~]# rpm -qa|grep lz4
lz4-1.8.3-1.el7.x86_64
lz4-devel-1.8.3-1.el7.x86_64
[root@lxs02 ~]# 
复制

安装.2

[postgres@lxs02 citus-10.2.3]$  ./configure PG_CONFIG=/opt/pgsql14.2/bin/pg_config 
checking for a sed that does not truncate output... /bin/sed
checking for gawk... gawk
。。。
checking lz4.h presence... yes
checking for lz4.h... yes
checking for ZSTD_decompress in -lzstd... no
configure: error: zstd library not found
If you have zstd installed, see config.log for details on the
failure.  It is possible the compiler isn't looking in the proper directory.
Use --without-zstd to disable zstd support.
[postgres@lxs02 citus-10.2.3]$ 
复制

安装ZSTD

[root@lxs02 ~]# ls -rlt zstd-dev.zip 
-rw-r--r--. 1 root root 2258420 May 12 00:25 zstd-dev.zip
[root@lxs02 ~]# 
[root@lxs02 ~]# unzip zstd-dev.zip 
Archive:  zstd-dev.zip
8bf32de8507c7fa9ebc9207ac50401a194fb7564
   creating: zstd-dev/
。。。
finishing deferred symbolic links:
  zstd-dev/tests/cli-tests/bin/unzstd -> zstd
  zstd-dev/tests/cli-tests/bin/zstdcat -> zstd
[root@lxs02 ~]# cd zstd-dev
[root@lxs02 zstd-dev]# make & make install
[1] 30827
make[1]: Entering directory `/root/zstd-dev/lib'
。。。
zstd installation completed
make[1]: Leaving directory `/root/zstd-dev/programs'
[1]+  Done                    make
[root@lxs02 zstd-dev]# 
[root@lxs02 zstd-dev]# find / -name 'libzstd.so.1'
/root/zstd-dev/lib/libzstd.so.1
/usr/local/lib/libzstd.so.1
[root@lxs02 zstd-dev]# cp /usr/local/lib/libzstd.so.1 /usr/lib
[root@lxs02 zstd-dev]# ldconfig
复制

安装.3

[postgres@lxs02 citus-10.2.3]$  ./configure PG_CONFIG=/opt/pgsql14.2/bin/pg_config 
checking for a sed that does not truncate output... /bin/sed
checking for gawk... gawk
。。。
config.status: creating src/include/citus_config.h
config.status: creating src/include/citus_version.h
[postgres@lxs02 citus-10.2.3]$ 
复制

make&make install

[postgres@lxs02 citus-10.2.3]$ make && make install
Makefile:51: warning: overriding recipe for target `check'
/opt/pgsql14.2/lib/pgxs/src/makefiles/pgxs.mk:446: warning: ignoring old recipe for target `check'
make -C src/backend/distributed/ all
。。。
/usr/bin/install -c -m 644 ./src/include/citus_version.h '/opt/pgsql14.2/include/server/'
/usr/bin/install -c -m 644 /home/postgres/citus-10.2.3/./src/include/distributed/*.h '/opt/pgsql14.2/include/server/distributed/'
[postgres@lxs02 citus-10.2.3]$ 
复制

环境变量配置

[postgres@lxs02 ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH


export PGHOME=/opt/pgsql14.2
export PGUSER=postgres
export PATH=$HOME/bin:$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
[postgres@lxs02 ~]$ 
复制

初始化数据库

CN节点

[postgres@lxs02 ~]$ initdb -D /opt/pgdata5432 -k -E UTF-8 -U liyuanpeng
[postgres@lxs02 ~]$ echo "log_destination = 'csvlog'
> logging_collector = on
> log_directory = 'pg_log'
> log_filename = 'postgresql-pg5432-%Y-%m-%d_%H%M%S.log'
> listen_addresses = '*'" >> /opt/pgdata5432/postgresql.conf 
[postgres@lxs02 ~]$ 
[postgres@lxs02 ~]$ echo 'port = 5432' >> /opt/pgdata5432/postgresql.conf 
[postgres@lxs02 ~]$ 
[postgres@lxs02 ~]$ echo 'host    all             all     192.168.60.0/24                 trust' >> /opt/pgdata5432/pg_hba.conf
[postgres@lxs02 ~]$ 
[postgres@lxs02 ~]$ pg_ctl start -D /opt/pgdata5432/
waiting for server to start....2022-05-14 04:10:36.058 CST [34945] LOG:  starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-05-14 04:10:36.061 CST [34945] LOG:  listening on IPv6 address "::1", port 5432
2022-05-14 04:10:36.061 CST [34945] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-05-14 04:10:36.062 CST [34945] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-05-14 04:10:36.114 CST [34946] LOG:  database system was shut down at 2022-05-14 04:07:00 CST
2022-05-14 04:10:36.117 CST [34945] LOG:  database system is ready to accept connections
 done
server started
[postgres@lxs02 opt]$
复制

Work01节点

[postgres@lxs02 ~]$ initdb -D /opt/pgdata5433 -k -E UTF-8 -U liyuanpeng
[postgres@lxs02 ~]$ echo "log_destination = 'csvlog'
> logging_collector = on
> log_directory = 'pg_log'
> log_filename = 'postgresql-pg5432-%Y-%m-%d_%H%M%S.log'
> listen_addresses = '*'" >> /opt/pgdata5432/postgresql.conf 
[postgres@lxs02 ~]$ 
[postgres@lxs02 ~]$ echo 'port = 5433' >> /opt/pgdata5432/postgresql.conf 
[postgres@lxs02 ~]$ 
[postgres@lxs02 ~]$ echo 'host    all             all     192.168.60.0/24                 trust' >> /opt/pgdata5433/pg_hba.conf
[postgres@lxs02 ~]$ 
[postgres@lxs02 ~]$ pg_ctl start -D /opt/pgdata5433/
waiting for server to start....2022-05-14 04:10:39.387 CST [34955] LOG:  starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-05-14 04:10:39.388 CST [34955] LOG:  listening on IPv6 address "::1", port 5433
2022-05-14 04:10:39.388 CST [34955] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2022-05-14 04:10:39.389 CST [34955] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2022-05-14 04:10:39.394 CST [34956] LOG:  database system was shut down at 2022-05-14 04:07:00 CST
2022-05-14 04:10:39.408 CST [34955] LOG:  database system is ready to accept connections
 done
server started
[postgres@lxs02 ~]$
复制

Work02节点

[postgres@lxs02 ~]$ initdb -D /opt/pgdata5434 -k -E UTF-8 -U liyuanpeng
[postgres@lxs02 ~]$ echo "log_destination = 'csvlog'
> logging_collector = on
> log_directory = 'pg_log'
> log_filename = 'postgresql-pg5432-%Y-%m-%d_%H%M%S.log'
> listen_addresses = '*'" >> /opt/pgdata5432/postgresql.conf 
[postgres@lxs02 ~]$ 
[postgres@lxs02 ~]$ echo 'port = 5434' >>/opt/pgdata5434/postgresql.conf 
[postgres@lxs02 ~]$ 
[postgres@lxs02 ~]$ echo 'host    all             all     192.168.60.0/24                 trust' >> /opt/pgdata5434/pg_hba.conf
[postgres@lxs02 ~]$ 
[postgres@lxs02 ~]$ pg_ctl start -D /opt/pgdata5434/
waiting for server to start....2022-05-14 04:10:41.397 CST [34973] LOG:  starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-05-14 04:10:41.397 CST [34973] LOG:  listening on IPv6 address "::1", port 5434
2022-05-14 04:10:41.397 CST [34973] LOG:  listening on IPv4 address "127.0.0.1", port 5434
2022-05-14 04:10:41.400 CST [34973] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5434"
2022-05-14 04:10:41.402 CST [34974] LOG:  database system was shut down at 2022-05-14 04:07:00 CST
2022-05-14 04:10:41.404 CST [34973] LOG:  database system is ready to accept connections
 done
server started
[postgres@lxs02 ~]$ 
复制

安装扩展

CN节点

[postgres@lxs02 ~]$ echo "shared_preload_libraries = 'citus'" >> /opt/pgdata5432/postgresql.conf
[postgres@lxs02 ~]$ pg_ctl restart -D /opt/pgdata5432
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-05-14 04:36:44.242 CST [36456] LOG:  number of prepared transactions has not been configured, overriding
2022-05-14 04:36:44.242 CST [36456] DETAIL:  max_prepared_transactions is now set to 200
2022-05-14 04:36:44.253 CST [36456] LOG:  redirecting log output to logging collector process
2022-05-14 04:36:44.253 CST [36456] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[postgres@lxs02 ~]$ psql -p5432
psql (14.2)
Type "help" for help.

postgres=# create extension citus;
CREATE EXTENSION
postgres=# \dx citus
2022-05-14 04:24:13.027 CST [35615] LOG:  starting maintenance daemon on database 13892 user 10
2022-05-14 04:24:13.027 CST [35615] CONTEXT:  Citus maintenance daemon for database 13892 user 10
               List of installed extensions
 Name  | Version |   Schema   |        Description         
-------+---------+------------+----------------------------
 citus | 10.2-4  | pg_catalog | Citus distributed database
(1 row)

postgres=# 
复制

Work01节点

[postgres@lxs02 ~]$ echo "shared_preload_libraries = 'citus'" >> /opt/pgdata5433/postgresql.conf
[postgres@lxs02 ~]$ pg_ctl restart -D /opt/pgdata5433
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-05-14 04:37:12.801 CST [36491] LOG:  number of prepared transactions has not been configured, overriding
2022-05-14 04:37:12.801 CST [36491] DETAIL:  max_prepared_transactions is now set to 200
2022-05-14 04:37:12.812 CST [36491] LOG:  redirecting log output to logging collector process
2022-05-14 04:37:12.812 CST [36491] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[postgres@lxs02 ~]$ psql -p5433
psql (14.2)
Type "help" for help.

postgres=# create extension citus;
CREATE EXTENSION
postgres=# 
复制

Work02节点

[postgres@lxs02 ~]$ echo "shared_preload_libraries = 'citus'" >> /opt/pgdata5434/postgresql.conf
[postgres@lxs02 ~]$ pg_ctl restart -D /opt/pgdata5434
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-05-14 04:37:14.814 CST [36502] LOG:  number of prepared transactions has not been configured, overriding
2022-05-14 04:37:14.814 CST [36502] DETAIL:  max_prepared_transactions is now set to 200
2022-05-14 04:37:14.824 CST [36502] LOG:  redirecting log output to logging collector process
2022-05-14 04:37:14.824 CST [36502] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[postgres@lxs02 ~]$ psql -p5434
psql (14.2)
Type "help" for help.

postgres=# create extension citus;
CREATE EXTENSION
postgres=# 
复制

cn节点添加/删除worker

添加worker节点

[postgres@lxs02 ~]$ psql -p5432
psql (14.2)
Type "help" for help.

postgres=# select * from master_add_node('192.168.60.202',5433);
 master_add_node 
-----------------
               3
(1 row)

postgres=# select * from master_add_node('192.168.60.202',5434);
 master_add_node 
-----------------
               4
(1 row)

postgres=# select * from master_get_active_worker_nodes();
   node_name    | node_port 
----------------+-----------
 192.168.60.202 |      5433
 192.168.60.202 |      5434
(2 rows)

postgres=# select * from pg_dist_node;
 nodeid | groupid |    nodename    | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards 
--------+---------+----------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
      3 |       3 | 192.168.60.202 |     5433 | default  | f           | t        | primary  | default     | f              | t
      4 |       4 | 192.168.60.202 |     5434 | default  | f           | t        | primary  | default     | f              | t
(2 rows)

postgres=# 
复制

删除worker节点（无数据）

postgres=# select * from master_remove_node('192.168.60.202',5434);
 master_remove_node 
--------------------
 
(1 row)

postgres=# select * from master_get_active_worker_nodes();
   node_name    | node_port 
----------------+-----------
 192.168.60.202 |      5433
(1 row)

postgres=# select * from pg_dist_node;
 nodeid | groupid |    nodename    | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards 
--------+---------+----------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
      3 |       3 | 192.168.60.202 |     5433 | default  | f           | t        | primary  | default     | f              | t
(1 row)

postgres=# 
复制

查看分片

postgres=# show citus.shard_count ;
 citus.shard_count 
-------------------
 32
(1 row)

postgres=# alter system set citus.shard_count=4;
ALTER SYSTEM
postgres=# \q
[postgres@lxs02 ~]$ pg_ctl reload -D /opt/pgdata5432
server signaled
[postgres@lxs02 ~]$ psql -p5432
psql (14.2)
Type "help" for help.

postgres=# show citus.shard_count ;
 citus.shard_count 
-------------------
 4
(1 row)

postgres=# 
复制

表测试

创建普通表

CN

postgres=# create table enmotech(id serial,col1 varchar(8),updatetime timestamptz default now());
CREATE TABLE
postgres=# create table enmotech_dist(id serial,col1 varchar(8),updatetime timestamptz default now());
CREATE TABLE
postgres=# create table enmotech_ref(id serial,col1 varchar(8),updatetime timestamptz default now());
CREATE TABLE
postgres=# create table enmotech_col(id serial,col1 varchar(8),updatetime timestamptz default now());
CREATE TABLE
postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | enmotech      | table | postgres
 public | enmotech_col  | table | postgres
 public | enmotech_dist | table | postgres
 public | enmotech_ref  | table | postgres
(4 rows)

postgres=# 
复制

创建分布表

CN

postgres=# select create_distributed_table('enmotech_dist','id');
 create_distributed_table 
--------------------------
 
(1 row)

postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | enmotech      | table | postgres
 public | enmotech_col  | table | postgres
 public | enmotech_dist | table | postgres
 public | enmotech_ref  | table | postgres
(4 rows)

postgres=# 
复制

Worker01

[postgres@lxs02 ~]$ psql -p5433
psql (14.2)
Type "help" for help.

postgres=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 public | enmotech_dist_102008 | table | postgres
 public | enmotech_dist_102009 | table | postgres
 public | enmotech_dist_102010 | table | postgres
 public | enmotech_dist_102011 | table | postgres
(4 rows)

postgres=# 
复制

创建亲和表

CN

postgres=# select create_distributed_table('enmotech_col','id',colocate_with =>'enmotech_dist');
 create_distributed_table 
--------------------------
 
(1 row)

postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | enmotech      | table | postgres
 public | enmotech_col  | table | postgres
 public | enmotech_dist | table | postgres
 public | enmotech_ref  | table | postgres
(4 rows)

postgres=# 
复制

Worker01

postgres=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 public | enmotech_col_102012  | table | postgres
 public | enmotech_col_102013  | table | postgres
 public | enmotech_col_102014  | table | postgres
 public | enmotech_col_102015  | table | postgres
 public | enmotech_dist_102008 | table | postgres
 public | enmotech_dist_102009 | table | postgres
 public | enmotech_dist_102010 | table | postgres
 public | enmotech_dist_102011 | table | postgres
(8 rows)

postgres=# 
复制

创建引用表

CN

postgres=# select create_reference_table('enmotech_ref');
 create_reference_table 
------------------------
 
(1 row)

postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | enmotech      | table | postgres
 public | enmotech_col  | table | postgres
 public | enmotech_dist | table | postgres
 public | enmotech_ref  | table | postgres
(4 rows)

postgres=# 
复制

worker01

postgres=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 public | enmotech_col_102012  | table | postgres
 public | enmotech_col_102013  | table | postgres
 public | enmotech_col_102014  | table | postgres
 public | enmotech_col_102015  | table | postgres
 public | enmotech_dist_102008 | table | postgres
 public | enmotech_dist_102009 | table | postgres
 public | enmotech_dist_102010 | table | postgres
 public | enmotech_dist_102011 | table | postgres
 public | enmotech_ref_102016  | table | postgres
(9 rows)

postgres=# 
复制

分片策略

CN

postgres=# select * from pg_dist_shard;
 logicalrelid  | shardid | shardstorage | shardminvalue | shardmaxvalue 
---------------+---------+--------------+---------------+---------------
 enmotech_dist |  102008 | t            | -2147483648   | -1073741825
 enmotech_dist |  102009 | t            | -1073741824   | -1
 enmotech_dist |  102010 | t            | 0             | 1073741823
 enmotech_dist |  102011 | t            | 1073741824    | 2147483647
 enmotech_col  |  102012 | t            | -2147483648   | -1073741825
 enmotech_col  |  102013 | t            | -1073741824   | -1
 enmotech_col  |  102014 | t            | 0             | 1073741823
 enmotech_col  |  102015 | t            | 1073741824    | 2147483647
 enmotech_ref  |  102016 | t            |               | 
(9 rows)

postgres=# 
复制

分片存储

CN

postgres=# select * from pg_dist_placement;
 placementid | shardid | shardstate | shardlength | groupid 
-------------+---------+------------+-------------+---------
           1 |  102008 |          1 |           0 |       3
           2 |  102009 |          1 |           0 |       3
           3 |  102010 |          1 |           0 |       3
           4 |  102011 |          1 |           0 |       3
           5 |  102012 |          1 |           0 |       3
           6 |  102013 |          1 |           0 |       3
           7 |  102014 |          1 |           0 |       3
           8 |  102015 |          1 |           0 |       3
           9 |  102016 |          1 |           0 |       3
(9 rows)

postgres=# select * from pg_dist_shard_placement;
 shardid | shardstate | shardlength |    nodename    | nodeport | placementid 
---------+------------+-------------+----------------+----------+-------------
  102008 |          1 |           0 | 192.168.60.202 |     5433 |           1
  102009 |          1 |           0 | 192.168.60.202 |     5433 |           2
  102010 |          1 |           0 | 192.168.60.202 |     5433 |           3
  102011 |          1 |           0 | 192.168.60.202 |     5433 |           4
  102012 |          1 |           0 | 192.168.60.202 |     5433 |           5
  102013 |          1 |           0 | 192.168.60.202 |     5433 |           6
  102014 |          1 |           0 | 192.168.60.202 |     5433 |           7
  102015 |          1 |           0 | 192.168.60.202 |     5433 |           8
  102016 |          1 |           0 | 192.168.60.202 |     5433 |           9
(9 rows)

postgres=# 
复制

添加Worker节点

CN

postgres=# select * from master_add_node('192.168.60.202',5434);
NOTICE:  Replicating reference table "enmotech_ref" to the node 192.168.60.202:5434
 master_add_node 
-----------------
               5
(1 row)

postgres=# select * from pg_dist_node;
 nodeid | groupid |    nodename    | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards 
--------+---------+----------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
      3 |       3 | 192.168.60.202 |     5433 | default  | f           | t        | primary  | default     | f              | t
      5 |       5 | 192.168.60.202 |     5434 | default  | f           | t        | primary  | default     | f              | t
(2 rows)

postgres=# 
复制

NOTICE: Replicating reference table “enmotech_ref” to the node 192.168.60.202:5434

自动copy到了Worker02：5434上了

Worker02

postgres=# \dt
                List of relations
 Schema |        Name         | Type  |  Owner   
--------+---------------------+-------+----------
 public | enmotech_ref_102016 | table | postgres
(1 row)

postgres=# 
复制

重平衡分片分布

CN

postgres=# select rebalance_table_shards();
NOTICE:  Moving shard 102008 from 192.168.60.202:5433 to 192.168.60.202:5434 ...
NOTICE:  Moving shard 102009 from 192.168.60.202:5433 to 192.168.60.202:5434 ...
 rebalance_table_shards 
------------------------
 
(1 row)

postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | enmotech      | table | postgres
 public | enmotech_col  | table | postgres
 public | enmotech_dist | table | postgres
 public | enmotech_ref  | table | postgres
(4 rows)

postgres=# 
复制

Worker01

postgres=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 public | enmotech_col_102014  | table | postgres
 public | enmotech_col_102015  | table | postgres
 public | enmotech_dist_102010 | table | postgres
 public | enmotech_dist_102011 | table | postgres
 public | enmotech_ref_102016  | table | postgres
(5 rows)

postgres=# 
复制

Worker02

postgres=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 public | enmotech_col_102012  | table | postgres
 public | enmotech_col_102013  | table | postgres
 public | enmotech_dist_102008 | table | postgres
 public | enmotech_dist_102009 | table | postgres
 public | enmotech_ref_102016  | table | postgres
(5 rows)

postgres=# 
复制

修改副本数量

CN

postgres=# alter system set citus.shard_replication_factor=2;
ALTER SYSTEM
postgres=# 
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=# show citus.shard_replication_factor;
 citus.shard_replication_factor 
--------------------------------
 2
(1 row)

postgres=# 
复制

新建分布表

CN

postgres=# create table enmotech_dist1(id serial,col1 varchar(8),updatetime timestamptz default now());
CREATE TABLE
postgres=# select create_distributed_table('enmotech_dist1','id');
 create_distributed_table 
--------------------------
 
(1 row)

postgres=# \dt
             List of relations
 Schema |      Name      | Type  |  Owner   
--------+----------------+-------+----------
 public | enmotech       | table | postgres
 public | enmotech_col   | table | postgres
 public | enmotech_dist  | table | postgres
 public | enmotech_dist1 | table | postgres
 public | enmotech_ref   | table | postgres
(5 rows)

postgres=# 
复制

Worker01

postgres=# \dt
                 List of relations
 Schema |         Name          | Type  |  Owner   
--------+-----------------------+-------+----------
 public | enmotech_col_102014   | table | postgres
 public | enmotech_col_102015   | table | postgres
 public | enmotech_dist1_102021 | table | postgres
 public | enmotech_dist1_102022 | table | postgres
 public | enmotech_dist1_102023 | table | postgres
 public | enmotech_dist1_102024 | table | postgres
 public | enmotech_dist_102010  | table | postgres
 public | enmotech_dist_102011  | table | postgres
 public | enmotech_ref_102016   | table | postgres
(9 rows)

postgres=# 
复制

Worker02

postgres=# \dt
                 List of relations
 Schema |         Name          | Type  |  Owner   
--------+-----------------------+-------+----------
 public | enmotech_col_102012   | table | postgres
 public | enmotech_col_102013   | table | postgres
 public | enmotech_dist1_102021 | table | postgres
 public | enmotech_dist1_102022 | table | postgres
 public | enmotech_dist1_102023 | table | postgres
 public | enmotech_dist1_102024 | table | postgres
 public | enmotech_dist_102008  | table | postgres
 public | enmotech_dist_102009  | table | postgres
 public | enmotech_ref_102016   | table | postgres
(9 rows)

postgres=# 
复制

关联查询

数据插入

postgres=# insert into enmotech select generate_series(1,100),'emotech';
INSERT 0 100
postgres=# insert into enmotech_dist select generate_series(1,100),'enmotech';
INSERT 0 100
postgres=# insert into enmotech_ref select generate_series(1,100),'ref';
INSERT 0 100
postgres=# insert into enmotech_col select generate_series(1,100),'col';
INSERT 0 100
postgres=# insert into enmotech_dist1 select generate_series(1,100),'dist1';
INSERT 0 100
postgres=# 
复制

关联查询

postgres=# select count(*) from enmotech e ,enmotech_ref r where e.id=r.id;
 count 
-------
   100
(1 row)

postgres=# select count(*) from enmotech e ,enmotech_dist d where e.id=d.id;
 count 
-------
   100
(1 row)

postgres=# select count(*) from enmotech e ,enmotech_col c where e.id=c.id;
 count 
-------
   100
(1 row)

postgres=#
复制

outer join

postgres=# select count(*) from enmotech_dist1 d1 left outer join enmotech_dist d on d1.id=d.id;
ERROR:  cannot push down this subquery
DETAIL:  Shards of relations in subquery need to have 1-to-1 shard partitioning
postgres=# select count(*) from enmotech_col c left outer join enmotech_dist d on c.id=d.id;
 count 
-------
   100
(1 row)

postgres=# 
复制

分片字段不允许更新

postgres=# update enmotech_ref set id=1000 where id=1;
UPDATE 1
postgres=# 
postgres=# update enmotech_dist set id=1000 where id=1;
ERROR:  modifying the partition value of rows is not allowed
postgres=# 
复制

最后修改时间：2022-07-20 16:45:19

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

3人已赞赏

文章被以下合辑收录

PostgreSQL分享—李先生（共53篇）

日常的PostgreSQL分享，包含运维、升级、安装部署、疑难问题等

后来

1年前

有用

1年前

丘

丘比特

2年前

阔以～

2年前

A隆鑫卡友生活智能充电站

2年前

有用

2年前

李

李春

2年前

有用

2年前

PostgreSQL运维—分布式Citus单机多实例部署

分布式Citus单机多实例部署

什么是Citus

环境描述

安装PG软件

安装Citus

准备安装包

安装依赖包

解压软件

安装软件

安装.1

安装LZ4

安装.2

安装ZSTD

安装.3

make&make install

环境变量配置

初始化数据库

CN节点

Work01节点

Work02节点

安装扩展

CN节点

Work01节点

Work02节点

cn节点添加/删除worker

添加worker节点

删除worker节点（无数据）

查看分片

表测试

创建普通表

CN

创建分布表

CN

Worker01

创建亲和表

CN

Worker01

创建引用表

CN

worker01

分片策略

CN

分片存储

CN

添加Worker节点

CN

Worker02

重平衡分片分布

CN

Worker01

Worker02

修改副本数量

修改副本数量

CN

新建分布表

CN

Worker01

Worker02

关联查询

数据插入

关联查询

outer join

分片字段不允许更新

文章被以下合辑收录

评论

相关阅读