PostgreSQL 14分布式Citus单机多实例部署

什么是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 ~]$ lltotal 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]$ lltotal 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.mddrwxrwxr-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.pngdrwxrwxr-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.mddrwxrwxr-x. 5 postgres postgres 48 Nov 29 16:50 srcdrwxrwxr-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_configchecking for a sed that does not truncate output... /bin/sedchecking for gawk... gawk。。。checking curl/curl.h usability... yeschecking curl/curl.h presence... yeschecking for curl/curl.h... yeschecking for LZ4_compress_default in -llz4... noconfigure: error: lz4 library not foundIf you have lz4 installed, see config.log for details on thefailure. 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.rpmLoaded plugins: langpacks, product-id, search-disabled-repos, subscription-managerThis 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_64Marking lz4-1.8.3-1.el7.x86_64.rpm as an update to lz4-1.7.5-2.el7.x86_64Resolving 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 ResolutionDependencies Resolved===========================================================Package                                         Arch                                               Version                                                    Repository                                                           Size===========================================================Updating:lz4                                             x86_64                                             1.8.3-1.el7                                                /lz4-1.8.3-1.el7.x86_64                                             201 kTransaction Summary===========================================================Upgrade 1 PackageTotal size: 201 kDownloading packages:Running transaction checkRunning transaction testTransaction test succeededRunning transactionUpdating   : lz4-1.8.3-1.el7.x86_64                                                                                                                                                                                                1/2Cleanup    : lz4-1.7.5-2.el7.x86_64                                                                                                                                                                                                2/2Verifying  : lz4-1.8.3-1.el7.x86_64                                                                                                                                                                                                1/2Verifying  : lz4-1.7.5-2.el7.x86_64                                                                                                                                                                                                2/2Updated: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.rpmLoaded plugins: langpacks, product-id, search-disabled-repos, subscription-managerThis 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_64Marking lz4-devel-1.8.3-1.el7.x86_64.rpm to be installedResolving Dependencies--> Running transaction check---> Package lz4-devel.x86_64 0:1.8.3-1.el7 will be installed--> Finished Dependency ResolutionDependencies 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 kTransaction Summary===========================================================Install 1 PackageTotal size: 77 kInstalled size: 77 kDownloading packages:Running transaction checkRunning transaction testTransaction test succeededRunning transactionInstalling : lz4-devel-1.8.3-1.el7.x86_64                                                                                                                                                                                          1/1Verifying  : lz4-devel-1.8.3-1.el7.x86_64                                                                                                                                                                                          1/1Installed:lz4-devel.x86_64 0:1.8.3-1.el7                                                                                                                                                                                                         Complete![root@lxs02 ~]#[root@lxs02 ~]# rpm -qa|grep lz4lz4-1.8.3-1.el7.x86_64lz4-devel-1.8.3-1.el7.x86_64

安装2

[postgres@lxs02 citus-10.2.3]$ ./configure PG_CONFIG=/opt/pgsql14.2/bin/pg_configchecking for a sed that does not truncate output... /bin/sedchecking for gawk... gawk。。。checking lz4.h presence... yeschecking for lz4.h... yeschecking for ZSTD_decompress in -lzstd... noconfigure: error: zstd library not foundIf you have zstd installed, see config.log for details on thefailure. 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.zipArchive: zstd-dev.zip8bf32de8507c7fa9ebc9207ac50401a194fb7564creating: zstd-dev/。。。finishing deferred symbolic links:zstd-dev/tests/cli-tests/bin/unzstd -> zstdzstd-dev/tests/cli-tests/bin/zstdcat -> zstd[root@lxs02 ~]# cd zstd-dev[root@lxs02 zstd-dev]# make & make install[1] 30827make[1]: Entering directory `/root/zstd-dev/lib'。。。zstd installation completedmake[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_configchecking for a sed that does not truncate output... /bin/sedchecking for gawk... gawk。。。config.status: creating src/include/citus_config.hconfig.status: creating src/include/citus_version.h[postgres@lxs02 citus-10.2.3]$make&make install[postgres@lxs02 citus-10.2.3]$ make && make installMakefile: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 functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHexport PGHOME=/opt/pgsql14.2export PGUSER=postgresexport PATH=$HOME/bin:$PGHOME/bin:$PATHexport LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

6. 初始化数据库

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-bit2022-05-14 04:10:36.061 CST [34945] LOG: listening on IPv6 address "::1", port 54322022-05-14 04:10:36.061 CST [34945] LOG: listening on IPv4 address "127.0.0.1", port 54322022-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 CST2022-05-14 04:10:36.117 CST [34945] LOG: database system is ready to accept connectionsdoneserver 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-bit2022-05-14 04:10:39.388 CST [34955] LOG: listening on IPv6 address "::1", port 54332022-05-14 04:10:39.388 CST [34955] LOG: listening on IPv4 address "127.0.0.1", port 54332022-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 CST2022-05-14 04:10:39.408 CST [34955] LOG: database system is ready to accept connectionsdoneserver 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-bit2022-05-14 04:10:41.397 CST [34973] LOG: listening on IPv6 address "::1", port 54342022-05-14 04:10:41.397 CST [34973] LOG: listening on IPv4 address "127.0.0.1", port 54342022-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 CST2022-05-14 04:10:41.404 CST [34973] LOG: database system is ready to accept connectionsdoneserver started[postgres@lxs02 ~]$

7. 安装扩展

CN节点

[postgres@lxs02 ~]$ echo "shared_preload_libraries = 'citus'" >> /opt/pgdata5432/postgresql.conf[postgres@lxs02 ~]$ pg_ctl restart -D /opt/pgdata5432waiting for server to shut down.... doneserver stoppedwaiting for server to start....2022-05-14 04:36:44.242 CST [36456] LOG: number of prepared transactions has not been configured, overriding2022-05-14 04:36:44.242 CST [36456] DETAIL: max_prepared_transactions is now set to 2002022-05-14 04:36:44.253 CST [36456] LOG: redirecting log output to logging collector process2022-05-14 04:36:44.253 CST [36456] HINT: Future log output will appear in directory "pg_log".doneserver started[postgres@lxs02 ~]$ psql -p5432psql (14.2)Type "help" for help.postgres=# create extension citus;CREATE EXTENSIONpostgres=# \dx citus2022-05-14 04:24:13.027 CST [35615] LOG: starting maintenance daemon on database 13892 user 102022-05-14 04:24:13.027 CST [35615] CONTEXT: Citus maintenance daemon for database 13892 user 10List of installed extensionsName  | 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/pgdata5433waiting for server to shut down.... doneserver stoppedwaiting for server to start....2022-05-14 04:37:12.801 CST [36491] LOG: number of prepared transactions has not been configured, overriding2022-05-14 04:37:12.801 CST [36491] DETAIL: max_prepared_transactions is now set to 2002022-05-14 04:37:12.812 CST [36491] LOG: redirecting log output to logging collector process2022-05-14 04:37:12.812 CST [36491] HINT: Future log output will appear in directory "pg_log".doneserver started[postgres@lxs02 ~]$ psql -p5433psql (14.2)Type "help" for help.postgres=# create extension citus;CREATE EXTENSIONWork02节点[postgres@lxs02 ~]$ echo "shared_preload_libraries = 'citus'" >> /opt/pgdata5434/postgresql.conf[postgres@lxs02 ~]$ pg_ctl restart -D /opt/pgdata5434waiting for server to shut down.... doneserver stoppedwaiting for server to start....2022-05-14 04:37:14.814 CST [36502] LOG: number of prepared transactions has not been configured, overriding2022-05-14 04:37:14.814 CST [36502] DETAIL: max_prepared_transactions is now set to 2002022-05-14 04:37:14.824 CST [36502] LOG: redirecting log output to logging collector process2022-05-14 04:37:14.824 CST [36502] HINT: Future log output will appear in directory "pg_log".doneserver started[postgres@lxs02 ~]$ psql -p5434psql (14.2)Type "help" for help.postgres=# create extension citus;CREATE EXTENSIONpostgres=#

8. cn节点添加/删除worker

添加worker节点

[postgres@lxs02 ~]$ psql -p5432psql (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 |      5433192.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              | t4 |       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 SYSTEMpostgres=# \q[postgres@lxs02 ~]$ pg_ctl reload -D /opt/pgdata5432server signaled[postgres@lxs02 ~]$ psql -p5432psql (14.2)Type "help" for help.postgres=# show citus.shard_count ;citus.shard_count-------------------4(1 row)

10. 表测试

创建普通表

postgres=# create table enmotech(id serial,col1 varchar(8),updatetime timestamptz default now());CREATE TABLEpostgres=# create table enmotech_dist(id serial,col1 varchar(8),updatetime timestamptz default now());CREATE TABLEpostgres=# create table enmotech_ref(id serial,col1 varchar(8),updatetime timestamptz default now());CREATE TABLEpostgres=# create table enmotech_col(id serial,col1 varchar(8),updatetime timestamptz default now());CREATE TABLEpostgres=# \dtList of relationsSchema |     Name      | Type  |  Owner   --------+---------------+-------+----------public | enmotech      | table | postgrespublic | enmotech_col  | table | postgrespublic | enmotech_dist | table | postgrespublic | enmotech_ref  | table | postgres(4 rows)

创建分区表

postgres=# select create_distributed_table('enmotech_dist','id');create_distributed_table--------------------------(1 row)postgres=# \dtList of relationsSchema |     Name      | Type  |  Owner   --------+---------------+-------+----------public | enmotech      | table | postgrespublic | enmotech_col  | table | postgrespublic | enmotech_dist | table | postgrespublic | enmotech_ref  | table | postgres(4 rows)postgres=#Worker01[postgres@lxs02 ~]$ psql -p5433psql (14.2)Type "help" for help.postgres=# \dtList of relationsSchema |         Name         | Type  |  Owner   --------+----------------------+-------+----------public | enmotech_dist_102008 | table | postgrespublic | enmotech_dist_102009 | table | postgrespublic | enmotech_dist_102010 | table | postgrespublic | enmotech_dist_102011 | table | postgres(4 rows)

创建亲和表

postgres=# select create_distributed_table('enmotech_col','id',colocate_with =>'enmotech_dist');create_distributed_table--------------------------(1 row)postgres=# \dtList of relationsSchema |     Name      | Type  |  Owner   --------+---------------+-------+----------public | enmotech      | table | postgrespublic | enmotech_col  | table | postgrespublic | enmotech_dist | table | postgrespublic | enmotech_ref  | table | postgres(4 rows)postgres=#Worker01postgres=# \dtList of relationsSchema |         Name         | Type  |  Owner   --------+----------------------+-------+----------public | enmotech_col_102012  | table | postgrespublic | enmotech_col_102013  | table | postgrespublic | enmotech_col_102014  | table | postgrespublic | enmotech_col_102015  | table | postgrespublic | enmotech_dist_102008 | table | postgrespublic | enmotech_dist_102009 | table | postgrespublic | enmotech_dist_102010 | table | postgrespublic | enmotech_dist_102011 | table | postgres(8 rows)postgres=#

创建引用表

postgres=# select create_reference_table('enmotech_ref');create_reference_table------------------------(1 row)postgres=# \dtList of relationsSchema |     Name      | Type  |  Owner   --------+---------------+-------+----------public | enmotech      | table | postgrespublic | enmotech_col  | table | postgrespublic | enmotech_dist | table | postgrespublic | enmotech_ref  | table | postgres(4 rows)postgres=#worker01postgres=# \dtList of relationsSchema |         Name         | Type  |  Owner   --------+----------------------+-------+----------public | enmotech_col_102012  | table | postgrespublic | enmotech_col_102013  | table | postgrespublic | enmotech_col_102014  | table | postgrespublic | enmotech_col_102015  | table | postgrespublic | enmotech_dist_102008 | table | postgrespublic | enmotech_dist_102009 | table | postgrespublic | enmotech_dist_102010 | table | postgrespublic | enmotech_dist_102011 | table | postgrespublic | enmotech_ref_102016  | table | postgres(9 rows)postgres=#

11. 分片策略

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

12. 分片存储

postgres=# select * from pg_dist_placement;placementid | shardid | shardstate | shardlength | groupid-------------+---------+------------+-------------+---------1 |  102008 |          1 |           0 |       32 |  102009 |          1 |           0 |       33 |  102010 |          1 |           0 |       34 |  102011 |          1 |           0 |       35 |  102012 |          1 |           0 |       36 |  102013 |          1 |           0 |       37 |  102014 |          1 |           0 |       38 |  102015 |          1 |           0 |       39 |  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 |           1102009 |          1 |           0 | 192.168.60.202 |     5433 |           2102010 |          1 |           0 | 192.168.60.202 |     5433 |           3102011 |          1 |           0 | 192.168.60.202 |     5433 |           4102012 |          1 |           0 | 192.168.60.202 |     5433 |           5102013 |          1 |           0 | 192.168.60.202 |     5433 |           6102014 |          1 |           0 | 192.168.60.202 |     5433 |           7102015 |          1 |           0 | 192.168.60.202 |     5433 |           8102016 |          1 |           0 | 192.168.60.202 |     5433 |           9(9 rows)

13. 添加Worker节点

postgres=# select * from master_add_node('192.168.60.202',5434);NOTICE: Replicating reference table "enmotech_ref" to the node 192.168.60.202:5434master_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              | t5 |       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=# \dtList of relationsSchema |        Name         | Type  |  Owner   --------+---------------------+-------+----------public | enmotech_ref_102016 | table | postgres(1 row)postgres=#

14. 重平衡分片分布

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=# \dtList of relationsSchema |     Name      | Type  |  Owner   --------+---------------+-------+----------public | enmotech      | table | postgrespublic | enmotech_col  | table | postgrespublic | enmotech_dist | table | postgrespublic | enmotech_ref  | table | postgres(4 rows)postgres=#

Worker01

postgres=# \dtList of relationsSchema |         Name         | Type  |  Owner   --------+----------------------+-------+----------public | enmotech_col_102014  | table | postgrespublic | enmotech_col_102015  | table | postgrespublic | enmotech_dist_102010 | table | postgrespublic | enmotech_dist_102011 | table | postgrespublic | enmotech_ref_102016  | table | postgres(5 rows)postgres=#

Worker02

postgres=# \dtList of relationsSchema |         Name         | Type  |  Owner   --------+----------------------+-------+----------public | enmotech_col_102012  | table | postgrespublic | enmotech_col_102013  | table | postgrespublic | enmotech_dist_102008 | table | postgrespublic | enmotech_dist_102009 | table | postgrespublic | enmotech_ref_102016  | table | postgres(5 rows)postgres=#

15. 修改副本数量

postgres=# alter system set citus.shard_replication_factor=2;ALTER SYSTEMpostgres=#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. 新建分布表

postgres=# create table enmotech_dist1(id serial,col1 varchar(8),updatetime timestamptz default now());CREATE TABLEpostgres=# select create_distributed_table('enmotech_dist1','id');create_distributed_table--------------------------(1 row)postgres=# \dtList of relationsSchema |      Name      | Type  |  Owner   --------+----------------+-------+----------public | enmotech       | table | postgrespublic | enmotech_col   | table | postgrespublic | enmotech_dist  | table | postgrespublic | enmotech_dist1 | table | postgrespublic | enmotech_ref   | table | postgres(5 rows)postgres=#Worker01postgres=# \dtList of relationsSchema |         Name          | Type  |  Owner   --------+-----------------------+-------+----------public | enmotech_col_102014   | table | postgrespublic | enmotech_col_102015   | table | postgrespublic | enmotech_dist1_102021 | table | postgrespublic | enmotech_dist1_102022 | table | postgrespublic | enmotech_dist1_102023 | table | postgrespublic | enmotech_dist1_102024 | table | postgrespublic | enmotech_dist_102010  | table | postgrespublic | enmotech_dist_102011  | table | postgrespublic | enmotech_ref_102016   | table | postgres(9 rows)Worker02postgres=# \dtList of relationsSchema |         Name          | Type  |  Owner   --------+-----------------------+-------+----------public | enmotech_col_102012   | table | postgrespublic | enmotech_col_102013   | table | postgrespublic | enmotech_dist1_102021 | table | postgrespublic | enmotech_dist1_102022 | table | postgrespublic | enmotech_dist1_102023 | table | postgrespublic | enmotech_dist1_102024 | table | postgrespublic | enmotech_dist_102008  | table | postgrespublic | enmotech_dist_102009  | table | postgrespublic | enmotech_ref_102016   | table | postgres(9 rows)

17. 数据插入

postgres=# insert into enmotech select generate_series(1,100),'emotech';INSERT 0 100postgres=# insert into enmotech_dist select generate_series(1,100),'enmotech';INSERT 0 100postgres=# insert into enmotech_ref select generate_series(1,100),'ref';INSERT 0 100postgres=# insert into enmotech_col select generate_series(1,100),'col';INSERT 0 100postgres=# insert into enmotech_dist1 select generate_series(1,100),'dist1';INSERT 0 100postgres=#(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 joinpostgres=# select count(*) from enmotech_dist1 d1 left outer join enmotech_dist d on d1.id=d.id;ERROR: cannot push down this subqueryDETAIL: Shards of relations in subquery need to have 1-to-1 shard partitioningpostgres=# 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 1postgres=# update enmotech_dist set id=1000 where id=1;ERROR: modifying the partition value of rows is not allowed