【分布式数据库Citus PgSQL集群】集群部署

$ cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)

$ psql -V
psql (PostgreSQL) 12.3

端口：5432

$ netstat -ntlp|grep post
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 10826/postgres

CN节点：192.168.10.71

Worker节点：192.168.10.72、192.168.10.73

 安装citus插件

tar xzf citus-10.0.2.tar.gz
cd citus-10.0.2/
./configure
make
make install

安装过程：

+ View Code?

1	./configure

+ View Code?

1 2 3 4 5

checking for a sed that does not truncate output... /bin/sed checking for gawk... gawk checking for flex... no checking for pg_config... no configure: error: Could not find pg_config. Set PG_CONFIG or PATH.

　提示需要配置环境变量：

export PGHOME=/usr/local/pgsql
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

./configure checking for a sed that does not truncate output... /bin/sed checking for gawk... gawk checking for flex... no checking for pg_config... /usr/local/pgsql/bin/pg_config configure: building against PostgreSQL 12 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking how to run the C preprocessor... gcc -E checking for grep that handles long lines and -e... /bin/grep checking for egrep... /bin/grep -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking size of void *... 8 checking whether gcc supports -std=gnu99... yes checking whether gcc supports -Wall... yes checking whether gcc supports -Wextra... yes checking whether gcc supports -Wno-unused-parameter... yes checking whether gcc supports -Wno-sign-compare... yes checking whether gcc supports -Wno-missing-field-initializers... yes checking whether gcc supports -Wno-clobbered... yes checking whether gcc supports -Wno-gnu-variable-sized-type-not-at-end... no checking whether gcc supports -Wno-declaration-after-statement... yes checking whether gcc supports -Wendif-labels... yes checking whether gcc supports -Wmissing-format-attribute... yes checking whether gcc supports -Wmissing-declarations... yes checking whether gcc supports -Wmissing-prototypes... yes checking whether gcc supports -Wshadow... yes checking whether gcc supports -Werror=vla... yes checking whether gcc supports -Werror=implicit-int... yes checking whether gcc supports -Werror=implicit-function-declaration... yes checking whether gcc supports -Werror=return-type... yes checking whether gcc supports -fstack-clash-protection... yes checking for curl_global_init in -lcurl... no configure: error: libcurl not found If you have libcurl already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-libcurl to disable anonymous statistics collection.

　

$ rpm -qa|grep libcurl
libcurl-7.29.0-57.el7.x86_64

libcurl已安装，执行

+ View Code?

1	./configure --without-libcurl

 

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

$ ./configure --without-libcurl checking for a sed that does not truncate output... /bin/sed checking for gawk... gawk checking for flex... no checking for pg_config... /usr/local/pgsql/bin/pg_config configure: building against PostgreSQL 12 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking how to run the C preprocessor... gcc -E checking for grep that handles long lines and -e... /bin/grep checking for egrep... /bin/grep -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking size of void *... 8 checking whether gcc supports -std=gnu99... yes checking whether gcc supports -Wall... yes checking whether gcc supports -Wextra... yes checking whether gcc supports -Wno-unused-parameter... yes checking whether gcc supports -Wno-sign-compare... yes checking whether gcc supports -Wno-missing-field-initializers... yes checking whether gcc supports -Wno-clobbered... yes checking whether gcc supports -Wno-gnu-variable-sized-type-not-at-end... no checking whether gcc supports -Wno-declaration-after-statement... yes checking whether gcc supports -Wendif-labels... yes checking whether gcc supports -Wmissing-format-attribute... yes checking whether gcc supports -Wmissing-declarations... yes checking whether gcc supports -Wmissing-prototypes... yes checking whether gcc supports -Wshadow... yes checking whether gcc supports -Werror=vla... yes checking whether gcc supports -Werror=implicit-int... yes checking whether gcc supports -Werror=implicit-function-declaration... yes checking whether gcc supports -Werror=return-type... yes checking whether gcc supports -fstack-clash-protection... 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 zlib support.

$ rpm -qa|grep lz4
lz4-1.7.5-3.el7.x86_64

lz4已安装,执行

+ View Code?

1	./configure --without-libcurl --without-lz4

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

$ ./configure --without-libcurl --without-lz4 checking for a sed that does not truncate output... /bin/sed checking for gawk... gawk checking for flex... no checking for pg_config... /usr/local/pgsql/bin/pg_config configure: building against PostgreSQL 12 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking how to run the C preprocessor... gcc -E checking for grep that handles long lines and -e... /bin/grep checking for egrep... /bin/grep -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking size of void *... 8 checking whether gcc supports -std=gnu99... yes checking whether gcc supports -Wall... yes checking whether gcc supports -Wextra... yes checking whether gcc supports -Wno-unused-parameter... yes checking whether gcc supports -Wno-sign-compare... yes checking whether gcc supports -Wno-missing-field-initializers... yes checking whether gcc supports -Wno-clobbered... yes checking whether gcc supports -Wno-gnu-variable-sized-type-not-at-end... no checking whether gcc supports -Wno-declaration-after-statement... yes checking whether gcc supports -Wendif-labels... yes checking whether gcc supports -Wmissing-format-attribute... yes checking whether gcc supports -Wmissing-declarations... yes checking whether gcc supports -Wmissing-prototypes... yes checking whether gcc supports -Wshadow... yes checking whether gcc supports -Werror=vla... yes checking whether gcc supports -Werror=implicit-int... yes checking whether gcc supports -Werror=implicit-function-declaration... yes checking whether gcc supports -Werror=return-type... yes checking whether gcc supports -fstack-clash-protection... 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 zlib support.

　执行./configure --without-libcurl --without-lz4 --without-zstd 

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

$ ./configure --without-libcurl --without-lz4  --without-zstd checking for a sed that does not truncate output... /bin/sed checking for gawk... gawk checking for flex... no checking for pg_config... /usr/local/pgsql/bin/pg_config configure: building against PostgreSQL 12 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking how to run the C preprocessor... gcc -E checking for grep that handles long lines and -e... /bin/grep checking for egrep... /bin/grep -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking size of void *... 8 checking whether gcc supports -std=gnu99... yes checking whether gcc supports -Wall... yes checking whether gcc supports -Wextra... yes checking whether gcc supports -Wno-unused-parameter... yes checking whether gcc supports -Wno-sign-compare... yes checking whether gcc supports -Wno-missing-field-initializers... yes checking whether gcc supports -Wno-clobbered... yes checking whether gcc supports -Wno-gnu-variable-sized-type-not-at-end... no checking whether gcc supports -Wno-declaration-after-statement... yes checking whether gcc supports -Wendif-labels... yes checking whether gcc supports -Wmissing-format-attribute... yes checking whether gcc supports -Wmissing-declarations... yes checking whether gcc supports -Wmissing-prototypes... yes checking whether gcc supports -Wshadow... yes checking whether gcc supports -Werror=vla... yes checking whether gcc supports -Werror=implicit-int... yes checking whether gcc supports -Werror=implicit-function-declaration... yes checking whether gcc supports -Werror=return-type... yes checking whether gcc supports -fstack-clash-protection... yes checking for git... no checking for .git... no configure: creating ./config.status config.status: creating Makefile.global config.status: creating src/include/citus_config.h config.status: creating src/include/citus_version.h

　make && make install

编辑参数文件postgresql.conf，cn节点与worker节点

shared_preload_libraries = 'citus'

citus.node_conninfo='sslmode=prefer'

创建扩展

创建测试库citusdb,如果存在多个业务库，都需要创建扩展。例如：

citusdb=# create extension citus;
CREATE EXTENSION

worker节点编辑pg_hba.conf

# IPv4 local connections:
host all all 10.154.0.0/32 trust

在CN节点添加worker

测试与worker连通性

$ psql -h localhost -p 1921

$ psql -h 192.168.10.72 -p 1921

$ psql -h 192.168.10.73 -p 1921
psql (12.3)
Type "help" for help.

postgres=#

添加Worker节点（只有CN节点执行）

citusdb=# select master_add_node('192.168.10.72','5432');
master_add_node
-----------------
1
(1 row)

citusdb=# select master_add_node('192.168.10.73','5432');
master_add_node
-----------------
2
(1 row)

查看工作节点

citusdb=# select * from master_get_active_worker_nodes();
node_name | node_port
---------------+-----------
192.168.10.73 | 5432
192.168.10.72 | 5432
(2 rows)

创建表

 

citusdb=# create table city(id int PRIMARY KEY,name varchar(10));
CREATE TABLE
citusdb=# set citus.shard_count=32;
SET

表分片，分片数，副本数，分片字段数据类型均相同的表自动建立亲和关系 

citusdb=# select create_distributed_table('city','id');
create_distributed_table
--------------------------
(1 row)

citusdb=# insert into city values (2021,'BeiJing');
INSERT 0 1
citusdb=# insert into city values (2022,'TianJin');
INSERT 0 1
citusdb=# insert into city values (2023,'Boston');
INSERT 0 1
citusdb=# insert into city values (2024,'ShangHai');
INSERT 0 1

citusdb=# select * from city;
id | name
------+----------
2022 | TianJin
2023 | Boston
2021 | BeiJing
2024 | ShangHai
(4 rows)

citusdb=# select tablename from pg_tables where schemaname='public';
tablename
------------
city
(1 rows)

Worker节点：

子节点分片之后的数据表

citusdb=# select tablename from pg_tables where schemaname='public';
tablename
-------------------
city_102040
city_102042
city_102044
city_102046
city_102048
city_102050
city_102052
city_102054
city_102056
city_102058
city_102060
city_102062
city_102064
city_102066
city_102068
city_102070
(16 rows)