什么是Citus
Citus是一个PostgreSQL扩展，可将Postgres转换为分布式数据库，因此您可以在任何规模上实现高性能。
使用Citus，您可以使用新的超能力扩展PostgreSQL数据库：
1、分布式表在PostgreSQL节点集群中进行分片，以组合它们的CPU、内存、存储和I/O容量。
2、引用表被复制到所有节点，以实现分布式表中的连接和外键以及最大的读取性能。
3、分布式查询引擎在整个集群中对分布式表上的SELECT、DML和其他操作进行路由和并行化。
4、列式存储可压缩数据、加快扫描速度并支持快速投影，适用于常规表和分布式表。
5、来自任何节点的查询使您能够利用集群的全部容量进行分布式查询
您可以使用这些Citus超级功能让您的Postgres数据库在单个Citus节点上进行横向扩展。或者，您可以构建一个能够处理高事务吞吐量的大型集群，尤其是在多租户应用程序中，运行快速分析查询，并处理大量时间序列或物联网数据以进行实时分析。当您的数据大小和容量增长时，您可以轻松地将更多工作节点添加到集群并重新平衡分片。
环境描述
操作系统版本：RedHat 7.6
PostgreSQL版本：PostgreSQL 14.2
Citus版本：citus-10.2.3
Citus节点信息如下：
节点 |
端口 |
CN节点 |
5432 |
Worker01节点 |
5433 |
Worker02节点 |
5434 |
安装PG软件
（略）
安装Citus
1、准备安装包
下载地址：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
2、 安装依赖包
yum install -y libcurl-devel lz4 zstd epel-release libzstd-devel
3、 解压软件
[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]$4、安装软件
安装1 --安装过程因缺少包文件，安装了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
。。。
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.
安装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
安装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]$5. 环境变量配置
[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_PATH6. 初始化数据库
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 ~]$ echo 'port = 5434' >>/opt/pgdata5434/postgresql.conf
[postgres@lxs02 ~]$ echo 'host all all 192.168.60.0/24 trust' >> /opt/pgdata5434/pg_hba.conf
[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 ~]$
7. 安装扩展
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)
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
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=#8. 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)
删除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=#
9. 查看分片
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)
10. 表测试
创建普通表
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)
创建分区表
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)
创建亲和表
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=#
11. 分片策略
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=#
12. 分片存储
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)
13. 添加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=#
14. 重平衡分片分布
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=#
15. 修改副本数量
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=#
16. 新建分布表
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)
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)17. 数据插入
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=#
(9 rows)
18. 关联查询
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)
19. 分片字段不允许更新
postgres=# update enmotech_ref set id=1000 where id=1;
UPDATE 1
postgres=# update enmotech_dist set id=1000 where id=1;
ERROR: modifying the partition value of rows is not allowed
