暂无图片
暂无图片
3
暂无图片
暂无图片
2
暂无图片
首页 / PostgreSQL+citus分布式数据库保姆级部署手册

PostgreSQL+citus分布式数据库保姆级部署手册

原创 小白学信创²⁰²² ᯤ⁶ᴳ All China Database Union 2024-09-10
2441

一、citus功能概述

1.1、什么是 Citus

Citus 数据库是 Postgres 的一个开源扩展，它为您提供了 Postgres 的所有优点，无论规模如何 - 从单个节点到大型分布式数据库集群。由于 Citus 是 Postgres 的一个扩展（而不是分支），因此当您使用 Citus 时，您也在使用 Postgres。
您可以利用最新的 Postgres 功能、工具和生态系统。使用 Citus，您可以获得分布式 Postgres 功能，如分片、分布式表、引用表、分布式查询引擎、列式存储 - 并且从 Citus 11.0 开始，可以从任何节点进行查询。Citus 结合了并行性、在内存中保留更多数据和更高的 I/O 带宽，可以显著提高多租户 SaaS 应用程序、面向客户的实时分析仪表板和时间序列工作负载的性能。

image.png

1.2、获取 Citus 的两种方法

1、开源：Citus 是 100% 开源的。您可以下载 Citus 开源版，或者要查看源代码并自行构建，请访问 GitHub 上的 Citus 存储库。
2、托管服务：Citus 数据库作为托管服务在云中提供，并附带 Azure Cosmos DB for PostgreSQL，以前称为 Azure Database for PostgreSQL 中的 Hyperscale (Citus)。

1.3、Citus 为您提供任意规模的 Postgres

您可以开始在单个节点上使用 Citus，从一开始就使用分布式数据模型，这样您就可以“随时进行扩展”。当您的 Postgres 工作负载需要扩展时，可以轻松地将工作节点添加到 Citus 数据库集群，和/或扩展集群中的协调器和工作节点。

1.4、Citus集群构成

Citus集群由一个中心的协调节点(CN)和若干个工作节点(Worker)构成：coordinate：协调节点，存储元数据，不存实际数据。该节点直接对用户开放，等于一个客户端。
worker：工作节点，不存储元数据，存储实际数据，执行协调节点发来的查询请求。

1.5、Citus 对于多租户应用程序的一些优势：

  • 为所有租户提供快速查询
  • 在数据库中（而不是应用程序中）分片逻辑
  • 在单节点 PostgreSQL 中保存比可能更多的数据
  • 无需放弃 SQL 即可进行横向扩展
  • 在高并发下保持性能
  • 跨客户群进行快速指标分析
  • 轻松扩展以处理新客户注册
  • 隔离大客户和小客户的资源使用情况

1.6、使用注意事项

Citus 扩展了 PostgreSQL 的分布式功能，但它并不是一个可以扩展所有工作负载的嵌入式替代品。高性能 Citus 集群需要考虑数据模型、工具和所使用的 SQL 功能的选择。
思考工具和 SQL 功能的一个好方法是：如果您的工作负载与此处描述的用例一致，并且您碰巧遇到不受支持的工具或查询，那么通常有一个很好的解决方法。

1.7、不适合使用Citus的场景

有些工作负载不需要强大的分布式数据库，而有些则需要工作节点之间有大量的信息流。在第一种情况下，Citus 是不必要的，在第二种情况下，Citus 通常性能不佳。以下是一些示例：

  • 当你不希望你的工作负载超出单个 Postgres 节点
  • 离线分析，无需实时采集或实时查询
  • 不需要支持大量并发用户的分析应用程序
  • 返回数据密集型 ETL 结果而非摘要的查询

二、初始化环境

2.1、环境规划

主机名称 主机IP 操作系统 PostgreSQL版本 citus版本 角色
Node1 192.168.1.61 CentOS Linux 7.9 16.4 coordinate
Node2 192.168.1.62 CentOS Linux 7.9 16.4 worker1
Node3 192.168.1.63 CentOS Linux 7.9 16.4 worker2

2.2、统一主机名称

vi /etc/hostname
或 hostnamectl set-hostname Node1

复制

2.3、关闭SELINUX

sed -i  "s/SELINUX=enforcing/SELINUX=disabled/"  /etc/selinux/config

复制

2.4、关闭防火墙

systemctl stop firewalld.service
systemctl disable firewalld.service
或设置开放端口：
查看开放的端口：
firewall-cmd --list-ports
开启防火墙端口：
firewall-cmd --zone=public --add-port=5432/tcp --permanent
命令含义：
–zone #作用域
–add-port=9200/tcp #添加端口，格式为：端口/通讯协议
–permanent #永久生效，没有此参数重启后失效
重新加载一遍才会生效：
firewall-cmd --reload

复制

2.5、安装插件

yum -y install readline readline-devel zlib zlib-devel gettext gettext-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl perl-devel tcl-devel uuid-devel gcc gcc-c++ make flex bison perl-ExtUtils*  libicu  libicu-devel  ntp libcurl-devel

复制

2.6、编辑hosts

将主机名和 ip 解析写到/etc/hosts 文件。
cat>>/etc/hosts<<EOF
192.168.1.61  Node1
192.168.1.62  Node2
192.168.1.63  Node3
EOF

复制

2.6、用户limits设置

cat>>/etc/security/limits.conf<<EOF
postgres soft nofile 1048576
postgres hard nofile 1048576
postgres soft nproc 131072
postgres hard nproc 131072
postgres soft stack 10240
postgres hard stack 32768
postgres soft core 6291456
postgres hard core 6291456
EOF

复制

2.7、调整内核

cat>>/etc/sysctl.conf<<EOF
fs.file-max = 76724200
kernel.sem = 10000 10240000 10000 1024
kernel.shmmni = 4096
kernel.shmall = 253702
kernel.shmmax = 1039163392
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 40960000
vm.dirty_ratio=20
vm.dirty_background_ratio=3
vm.dirty_writeback_centisecs=100
vm.dirty_expire_centisecs=500
vm.swappiness=10
vm.min_free_kbytes=524288
vm.swappiness=0
vm.overcommit_memory=2
vm.overcommit_ratio=75
net.ipv4.ip_local_port_range = 10000 65535
EOF

sysctl -p 

复制

2.8、时间同步

[root@Node1 ~]# date
Mon Sep  9 10:58:48 CST 2024

[root@Node2 ~]# date
Mon Sep  9 10:58:50 CST 2024

[root@Node3 ~]# date
Mon Sep  9 10:58:52 CST 2024

复制

2.9、创建用户

  • 注：在在所有节点上创建DBA用户
groupadd postgres -g 4000
useradd postgres -g 4000 -u 4000
echo "postgres"|passwd --stdin postgres

复制

2.10、所有节点配置sudo

sed -i.bak '100apostgres   ALL=(ALL)       NOPASSWD:ALL' /etc/sudoers

复制

三、部署PostgreSQL

3.1、目录规划

目录编号 目录名称 说明
1 /postgres postgres根目录
2 /postgres/server postgres根目录
3 /postgres/data 数据目录
4 /postgres/wal wal目录
5 /postgres/archive 归档目录
6 /postgres/backup 备份目录
7 /install 安装包上传目录
8 /postgres/citus 分布式扩展安装目录
9 /postgres/citus/log 分布式扩展日志目录 
 mkdir /install
 chmod -R 777 /install
 mkdir /postgres
 mkdir -p /postgres/server /postgres/data  /postgres/wal  /postgres/archive  /postgres/backup /postgres/citus/log
 sudo chown -R postgres:postgres /postgres /install
 chmod 0775 /postgres
 chmod 0700 /postgres/data

复制

3.2、部署PostgreSQL（三台主机Node1、Node2、Node3都要部署）

su  postgres
cd /install	

wget https://ftp.postgresql.org/pub/source/v16.4/postgresql-16.4.tar.gz

复制

3.3、拷贝安装包到其他主机

[postgres@Node1 install]$ scp -r ./* 192.168.1.62:/install && scp -r ./* 192.168.1.63:/install
The authenticity of host '192.168.1.62 (192.168.1.62)' can't be established.
ECDSA key fingerprint is SHA256:lx7Ykd3KUValFLX4c0Bf7tbXHeJNbcXZ5VgnBxn43LI.
ECDSA key fingerprint is MD5:5d:41:7c:42:00:f7:e3:f9:d8:fb:ba:19:7a:66:67:70.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.62' (ECDSA) to the list of known hosts.
postgres@192.168.1.62's password:
postgresql-16.4.tar.gz                                                                             100%   31MB 101.8MB/s   00:00
v12.1.4.tar.gz                                                                                     100% 6745KB 113.4MB/s   00:00
The authenticity of host '192.168.1.63 (192.168.1.63)' can't be established.
ECDSA key fingerprint is SHA256:S40RFtrnLXgbo6ZCJYE0Y50pCCGBYbwYUSWLBAUfQCQ.
ECDSA key fingerprint is MD5:c6:ef:8d:2d:c4:0d:d1:67:21:4d:73:67:fd:69:3d:fb.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.63' (ECDSA) to the list of known hosts.
postgres@192.168.1.63's password:
postgresql-16.4.tar.gz                                                                             100%   31MB 100.3MB/s   00:00
v12.1.4.tar.gz                                                                                     100% 6745KB  91.3MB/s   00:00
[postgres@Node1 install]$

复制

3.4、解压postgresql并安装


tar -zxvf postgresql-16.4.tar.gz

cd /install/postgresql-16.4

./configure --prefix=/postgres/server --with-pgport=5431 --with-openssl
./configure --prefix=/postgres/server --with-pgport=5432 --with-openssl

./configure --prefix=/postgres/server --with-pgport=5433 --with-openssl


使用  make 或者  make world
$ make world
使用 make install 或者 make install-world 进行安装
$ make install-world     #包含扩展包和文档


复制
  • Node1：
[postgres@Node1 install]$ cd /install/postgresql-16.4
[postgres@Node1 postgresql-16.4]$
[postgres@Node1 postgresql-16.4]$ ./configure --prefix=/postgres/server --with-pgport=5431 --with-openssl
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
checking whether NLS is wanted... no
checking for default port number... 5431
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
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 for gcc option to accept ISO C99... -std=gnu99
checking for g++... g++
checking whether we are using the GNU C++ compiler... yes
checking whether g++ accepts -g... yes
checking for gawk... gawk
.............................................
checking whether gcc -std=gnu99 supports -Wl,--as-needed, for LDFLAGS... yes
checking whether gcc -std=gnu99 supports -Wl,--export-dynamic, for LDFLAGS_EX_BE... yes
configure: using compiler=gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44)
configure: using CFLAGS=-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
configure: using CPPFLAGS= -D_GNU_SOURCE
configure: using LDFLAGS=  -Wl,--as-needed
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/include/pg_config_ext.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port
[postgres@Node1 postgresql-16.4]$


[postgres@Node1 postgresql-16.4]$ make && make install
make -C ./src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.4/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.4/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
prereqdir=`cd './' >/dev/null && pwd` && \
...........................................
make -C config install
make[1]: Entering directory `/install/postgresql-16.4/config'
/usr/bin/mkdir -p '/postgres/server/lib/pgxs/config'
/usr/bin/install -c -m 755 ./install-sh '/postgres/server/lib/pgxs/config/install-sh'
/usr/bin/install -c -m 755 ./missing '/postgres/server/lib/pgxs/config/missing'
make[1]: Leaving directory `/install/postgresql-16.4/config'
[postgres@Node1 postgresql-16.4]$

复制
  • Node2：
[postgres@Node2 install]$ cd /install/postgresql-16.4
[postgres@Node2 postgresql-16.4]$ ./configure --prefix=/postgres/server --with-pgport=5432 --with-openssl
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
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 for gcc option to accept ISO C99... -std=gnu99
checking for g++... g++
checking whether we are using the GNU C++ compiler... yes
checking whether g++ accepts -g... yes
checking for gawk... gawk
.............................................
checking whether gcc -std=gnu99 supports -Wl,--as-needed, for LDFLAGS... yes
checking whether gcc -std=gnu99 supports -Wl,--export-dynamic, for LDFLAGS_EX_BE... yes
configure: using compiler=gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44)
configure: using CFLAGS=-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
configure: using CPPFLAGS= -D_GNU_SOURCE
configure: using LDFLAGS=  -Wl,--as-needed
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/include/pg_config_ext.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port
[postgres@Node2 postgresql-16.4]$


[postgres@Node2 postgresql-16.4]$ make && make install
make -C ./src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.4/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.4/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
prereqdir=`cd './' >/dev/null && pwd` && \
...........................................
make -C config install
make[1]: Entering directory `/install/postgresql-16.4/config'
/usr/bin/mkdir -p '/postgres/server/lib/pgxs/config'
/usr/bin/install -c -m 755 ./install-sh '/postgres/server/lib/pgxs/config/install-sh'
/usr/bin/install -c -m 755 ./missing '/postgres/server/lib/pgxs/config/missing'
make[1]: Leaving directory `/install/postgresql-16.4/config'
[postgres@Node2 postgresql-16.4]$


复制
  • Node3：
[postgres@Node3 install]$ cd /install/postgresql-16.4
[postgres@Node3 postgresql-16.4]$ ./configure --prefix=/postgres/server --with-pgport=5433 --with-openssl
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
checking whether NLS is wanted... no
checking for default port number... 5433
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
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 for gcc option to accept ISO C99... -std=gnu99
checking for g++... g++
checking whether we are using the GNU C++ compiler... yes
checking whether g++ accepts -g... yes
checking for gawk... gawk
.............................................
checking whether gcc -std=gnu99 supports -Wl,--as-needed, for LDFLAGS... yes
checking whether gcc -std=gnu99 supports -Wl,--export-dynamic, for LDFLAGS_EX_BE... yes
configure: using compiler=gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44)
configure: using CFLAGS=-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
configure: using CPPFLAGS= -D_GNU_SOURCE
configure: using LDFLAGS=  -Wl,--as-needed
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/include/pg_config_ext.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port
[postgres@Node3 postgresql-16.4]$


[postgres@Node3 postgresql-16.4]$ make && make install
make -C ./src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.4/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.4/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
prereqdir=`cd './' >/dev/null && pwd` && \
...........................................
make -C config install
make[1]: Entering directory `/install/postgresql-16.4/config'
/usr/bin/mkdir -p '/postgres/server/lib/pgxs/config'
/usr/bin/install -c -m 755 ./install-sh '/postgres/server/lib/pgxs/config/install-sh'
/usr/bin/install -c -m 755 ./missing '/postgres/server/lib/pgxs/config/missing'
make[1]: Leaving directory `/install/postgresql-16.4/config'
[postgres@Node3 postgresql-16.4]$


复制

3.5、设置软链接

su root
mkdir /postgresql
chmod -R 777 /postgresql
chown -R postgres:postgres /postgresql
cd /postgres
ln -s /postgres/server /postgresql

复制

3.6、设置环境变量

  • Node1：
vi /home/postgres/.bashrc
export PGPORT=5431
export PGUSER=postgres
export PGHOME=/postgres/server
export PGDATA=/postgres/data
export PATH=$PGHOME/bin:$PATH

source  /home/postgres/.bashrc

复制
  • Node2：
vi /home/postgres/.bashrc
export PGPORT=5432
export PGUSER=postgres
export PGHOME=/postgres/server
export PGDATA=/postgres/data
export PATH=$PGHOME/bin:$PATH

source  /home/postgres/.bashrc

复制
  • Node3：
vi /home/postgres/.bashrc
export PGPORT=5433
export PGUSER=postgres
export PGHOME=/postgres/server
export PGDATA=/postgres/data
export PATH=$PGHOME/bin:$PATH

source  /home/postgres/.bashrc

复制

3.7、初始化数据库

/postgresql/server/bin/initdb -D/postgres/data -X/postgres/wal -EUTF8 -Upostgres -W --data-checksums --pwprompt

复制
  • Node1：
[postgres@Node1 ~]$ /postgresql/server/bin/initdb -D/postgres/data -X/postgres/wal -EUTF8 -Upostgres -W --data-checksums --pwprompt
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

Enter new superuser password:
Enter it again:

fixing permissions on existing directory /postgres/data ... ok
fixing permissions on existing directory /postgres/wal ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /postgresql/server/bin/pg_ctl -D /postgres/data -l logfile start

[postgres@Node1 ~]$

复制
  • Node2:
[postgres@Node2 ~]$ /postgresql/server/bin/initdb -D/postgres/data -X/postgres/wal -EUTF8 -Upostgres -W --data-checksums --pwprompt
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

Enter new superuser password:
Enter it again:

fixing permissions on existing directory /postgres/data ... ok
fixing permissions on existing directory /postgres/wal ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /postgresql/server/bin/pg_ctl -D /postgres/data -l logfile start

[postgres@Node2 ~]$

复制
  • Node3：
[postgres@Node3 ~]$ /postgresql/server/bin/initdb -D/postgres/data -X/postgres/wal -EUTF8 -Upostgres -W --data-checksums --pwprompt
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

Enter new superuser password:
Enter it again:

fixing permissions on existing directory /postgres/data ... ok
fixing permissions on existing directory /postgres/wal ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /postgresql/server/bin/pg_ctl -D /postgres/data -l logfile start

[postgres@Node3 ~]$

复制

3.8、设置开机自启

PostgreSQL的开机自启动脚本位于PostgreSQL源码目录的contrib/start-scripts路径下
这个路径是安装包解压完的路径。

linux文件即为linux系统上的启动脚本
1）修改linux文件属性，添加X属性
exit
切换到root用户
cd /install/postgresql-16.2/contrib/start-scripts
切换到解压目录下
chmod a+x linux

  1. 复制linux文件到/etc/init.d目录下，更名为PostgreSQL
    cp linux /etc/init.d/PostgreSQL

3）修改/etc/init.d/PostgreSQL文件的两个变量
vi /etc/init.d/PostgreSQL

prefix设置为PostgreSQL的安装路径：prefix=/postgres/server
PGDATA设置为PostgreSQL的数据目录路径：PGDATA="/postgres/data"

  1. 执行service PostgreSQL start，就可以启动PostgreSQL服务
    service PostgreSQL start

5）设置PostgreSQL服务开机自启动
chkconfig --add PostgreSQL

执行上面的命令，就可以实现PostgreSQL服务的开机自启动。
Reboot

3.9、设置postgresql.conf

说明：Node1：port=5431 ；Node2：port=5432；Node3：port=5433

vi $PGDATA/postgresql.conf

  • Node1：
#主机IP
listen_addresses = '*'
port = 5431

shared_preload_libraries = 'citus'
#开启归档
archive_mode = on

#归档文件存放路径
archive_command = 'test ! -f /postgres/archive/%f && cp %p /postgres/archive/%f'

复制
  • Node2：
#主机IP
listen_addresses = '*'
port = 5432

shared_preload_libraries = 'citus'
#开启归档
archive_mode = on

#归档文件存放路径
archive_command = 'test ! -f /postgres/archive/%f && cp %p /postgres/archive/%f'

复制
  • Node3：
#主机IP
listen_addresses = '*'
port = 5433

shared_preload_libraries = 'citus'
#开启归档
archive_mode = on

#归档文件存放路径
archive_command = 'test ! -f /postgres/archive/%f && cp %p /postgres/archive/%f'

复制

3.10、配置pg_hba.conf

vi $PGDATA/pg_hba.conf

#3 台数据库服务器之间相互信任访问。
host    all             all             192.168.1.1/24          trust
#允许用户通过用户名密码访问。
host    all             all             0.0.0.0/0               scram-sha-256

复制

四、部署citus

4.1、下载citus安装包

[postgres@Node1 install]$ wget https://github.com/citusdata/citus/archive/refs/tags/v12.1.4.tar.gz
--2024-09-08 20:29:36--  https://github.com/citusdata/citus/archive/refs/tags/v12.1.4.tar.gz
Resolving github.com (github.com)... 20.205.243.166
Connecting to github.com (github.com)|20.205.243.166|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/citusdata/citus/tar.gz/refs/tags/v12.1.4 [following]
--2024-09-08 20:29:37--  https://codeload.github.com/citusdata/citus/tar.gz/refs/tags/v12.1.4
Resolving codeload.github.com (codeload.github.com)... 20.205.243.165
Connecting to codeload.github.com (codeload.github.com)|20.205.243.165|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/x-gzip]
Saving to: ‘v12.1.4.tar.gz’

    [                                                                               <=>          ] 6,906,485    131KB/s   in 65s

2024-09-08 20:30:43 (103 KB/s) - ‘v12.1.4.tar.gz’ saved [6906485]

[postgres@Node1 install]$

复制

4.2、拷贝安装包到其他主机

[postgres@Node1 install]$ scp -r ./* 192.168.1.62:/install && scp -r ./* 192.168.1.63:/install

复制

4.3、安装citus安装包

[postgres@Node1 install]$ tar xzvf v12.1.4.tar.gz

[postgres@Node2 install]$ tar xzvf v12.1.4.tar.gz

[postgres@Node3 install]$ tar xzvf v12.1.4.tar.gz


复制

4.4、安装插件

sudo yum -y install lz4-devel epel-release  libzstd-devel
sudo yum install autoconf automake libtool

复制

4.5、安装citus

说明：

  • 安装脚本：
    cd /install/citus-12.1.4/
    ./autogen.sh
    ./configure --prefix=/postgres/citus
    make -j 8 && make install
  • Node1：
[postgres@Node1 install]$ cd /install/citus-12.1.4/
[postgres@Node1 citus-12.1.4]$  ./autogen.sh
[postgres@Node1 citus-12.1.4]$
[postgres@Node1 citus-12.1.4]$ ./configure --prefix=/postgres/citus
checking for a sed that does not truncate output... /usr/bin/sed
checking for gawk... gawk
checking for flex... /usr/bin/flex
checking for pg_config... /postgres/server/bin/pg_config
configure: building against PostgreSQL 16
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... /usr/bin/grep
checking for egrep... /usr/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... yes
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... yes
checking lz4.h usability... yes
checking lz4.h presence... yes
checking for lz4.h... yes
checking for ZSTD_decompress in -lzstd... yes
checking zstd.h usability... yes
checking zstd.h presence... yes
checking for zstd.h... yes
checking for git... /usr/bin/git
checking for .git... no
configure: creating ./config.status
config.status: creating Makefile.global
config.status: creating src/include/citus_config.h
config.status: src/include/citus_config.h is unchanged
config.status: creating src/include/citus_version.h
config.status: src/include/citus_version.h is unchanged
[postgres@Node1 citus-12.1.4]$


[postgres@Node1 citus-12.1.4]$  make -j 8 && make install
Makefile:64: warning: overriding recipe for target `check'
/postgres/server/lib/pgxs/src/makefiles/pgxs.mk:449: warning: ignoring old recipe for target `check'
make -C src/backend/columnar all
make[1]: Entering directory `/install/citus-12.1.4/src/backend/columnar'
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -std=gnu99 -Wall -Wextra -Wno-unused-parameter -Wno-sign-compare -Wno-missing-field-initializers -Wno-clobbered -Wno-declaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wmissing-declarations -Wmissing-prototypes -Wshadow -Werror=vla -Werror=implicit-int -Werror=implicit-function-declaration -Werror=return-type -fstack-clash-protection -I '/install/citus-12.1.4/src/include' -I'../../../src/include' -I/postgres/server/include -I/install/citus-12.1.4/vendor/safestringlib/include -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o columnar.o columnar.c


复制
  • Node2：
[postgres@Node2 install]$ cd /install/citus-12.1.4/
[postgres@Node2 citus-12.1.4]$
[postgres@Node2 citus-12.1.4]$  ./autogen.sh
[postgres@Node2 citus-12.1.4]$
[postgres@Node2 citus-12.1.4]$ ./configure --prefix=/postgres/citus
checking for a sed that does not truncate output... /usr/bin/sed
checking for gawk... gawk
checking for flex... /usr/bin/flex
checking for pg_config... /postgres/server/bin/pg_config
configure: building against PostgreSQL 16
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... /usr/bin/grep
checking for egrep... /usr/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... yes
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... yes
checking lz4.h usability... yes
checking lz4.h presence... yes
checking for lz4.h... yes
checking for ZSTD_decompress in -lzstd... yes
checking zstd.h usability... yes
checking zstd.h presence... yes
checking for zstd.h... yes
checking for git... /usr/bin/git
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
[postgres@Node2 citus-12.1.4]$


[postgres@Node2 citus-12.1.4]$  make -j 8 && make install
Makefile:64: warning: overriding recipe for target `check'
/postgres/server/lib/pgxs/src/makefiles/pgxs.mk:449: warning: ignoring old recipe for target `check'
make -C src/backend/columnar all
make[1]: Entering directory `/install/citus-12.1.4/src/backend/columnar'
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -std=gnu99 -Wall -Wextra -Wno-unused-parameter -Wno-sign-compare -Wno-missing-field-initializers -Wno-clobbered -Wno-declaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wmissing-declarations -Wmissing-prototypes -Wshadow -Werror=vla -Werror=implicit-int -Werror=implicit-function-declaration -Werror=return-type -fstack-clash-protection -I '/install/citus-12.1.4/src/include' -I'../../../src/include' -I/postgres/server/include -I/install/citus-12.1.4/vendor/safestringlib/include -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o columnar.o columnar.c

复制
  • Node3:
[postgres@Node3 install]$ cd /install/citus-12.1.4/
[postgres@Node3 citus-12.1.4]$
[postgres@Node3 citus-12.1.4]$ ./autogen.sh
[postgres@Node3 citus-12.1.4]$
[postgres@Node3 citus-12.1.4]$ ./configure --prefix=/postgres/citus
checking for a sed that does not truncate output... /usr/bin/sed
checking for gawk... gawk
checking for flex... /usr/bin/flex
checking for pg_config... /postgres/server/bin/pg_config
configure: building against PostgreSQL 16
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... /usr/bin/grep
checking for egrep... /usr/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... yes
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... yes
checking lz4.h usability... yes
checking lz4.h presence... yes
checking for lz4.h... yes
checking for ZSTD_decompress in -lzstd... yes
checking zstd.h usability... yes
checking zstd.h presence... yes
checking for zstd.h... yes
checking for git... /usr/bin/git
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
[postgres@Node3 citus-12.1.4]$

[postgres@Node3 citus-12.1.4]$  make -j 8 && make install
Makefile:64: warning: overriding recipe for target `check'
/postgres/server/lib/pgxs/src/makefiles/pgxs.mk:449: warning: ignoring old recipe for target `check'
make -C src/backend/columnar all
make[1]: Entering directory `/install/citus-12.1.4/src/backend/columnar'
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -std=gnu99 -Wall -Wextra -Wno-unused-parameter -Wno-sign-compare -Wno-missing-field-initializers -Wno-clobbered -Wno-declaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wmissing-declarations -Wmissing-prototypes -Wshadow -Werror=vla -Werror=implicit-int -Werror=implicit-function-declaration -Werror=return-type -fstack-clash-protection -I '/install/citus-12.1.4/src/include' -I'../../../src/include' -I/postgres/server/include -I/install/citus-12.1.4/vendor/safestringlib/include -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o columnar.o columnar.c

复制

4.6、启动PostgreSQL

[postgres@Node1 ~]$ pg_ctl  start -D $PGDATA -l /postgres/citus/log/cnlog
waiting for server to start.... done
server started
[postgres@Node1 ~]$

[postgres@Node2 ~]$pg_ctl  start -D $PGDATA -l /postgres/citus/log/work1log
waiting for server to start.... done
server started
[postgres@Node2 ~]$

[postgres@Node3 ~]$ pg_ctl  start -D $PGDATA -l /postgres/citus/log/work2log
waiting for server to start.... done
server started
[postgres@Node3 ~]$


复制

4.7、添加扩展

[postgres@Node1 ~]$ psql -h 192.168.1.61 -d postgres -U postgres -p 5431
psql (16.4)
Type "help" for help.

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


[postgres@Node2 ~]$ psql -h 192.168.1.62 -d postgres -U postgres -p 5432
psql (16.4)
Type "help" for help.

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


[postgres@Node3 ~]$ psql -h 192.168.1.63 -d postgres -U postgres -p 5433
psql (16.4)
Type "help" for help.

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

postgres=#
postgres=# \dx
                     List of installed extensions
      Name      | Version |   Schema   |         Description
----------------+---------+------------+------------------------------
 citus          | 12.1-1  | pg_catalog | Citus distributed database
 citus_columnar | 11.3-1  | pg_catalog | Citus Columnar extension
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=#


复制

五、Citus使用

5.1、添Citus使用说明

create_distributed_table()

功能：用于创建分布式表并创建分片，如果未指定分发方法，则该函数默认是“hash”分布

参数名	功能描述
table_name	需要分发的表的名称
distribution_column	要将表分布到的列
distribution_type（可选）	表的分布方法，允许的值为追加或哈希，默认为“hash”





alter_distributed_table() 

功能：更改分布式表的分布列、分片计数或共置属性
参数名	功能描述
table_name	将要更改的分布式表的名称
distribution_column（可选）	新分发列的名称
shard_count（可选）	新分片计数
colocate_with（可选）	当前分布式表将与之共置的表。可能的值为 ，用于启动新的共置组，或用于共置的另一个表的名称
cascade_to_colocated（可选）	当此参数设置为“true”时，更改也将应用于以前与该表共置的所有表，并且将保留共置。如果它是“false”，则此表的当前共置将被破坏


master_add_node()
功能：添加工作节点
参数名称	描述
ip	IP地址
port	端口号

undistribute_table()
功能：撤消create_distributed_table或create_reference_table的操作
参数名称	描述
table_name	要取消分发的分布式表或引用表的名称
cascade_via_foreign_keys（可选）	当此参数设置为“true”时，undistribute_table还会通过外键取消分布与table_name相关的所有表。请谨慎使用此参数，因为它可能会影响许多表

复制

5.2、添加工作节点式主机

说明：在Node1节点登录数据库，添加工作节点（Node2、Node3）的ip地址， 数据库端口号

[postgres@Node1 ~]$ psql -h 192.168.1.61 -d postgres -U postgres -p 5431
psql (16.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, compression: off)
Type "help" for help.

postgres=#  select * from master_add_node('192.168.1.62' ,5432);
 master_add_node
-----------------
               1
(1 row)

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

postgres=#

复制

5.3、查看工作节点信息

说明：如果添加成功，则会显示已添加的节点信息

postgres=# SELECT * FROM master_get_active_worker_nodes();
  node_name   | node_port
--------------+-----------
 192.168.1.62 |      5432
 192.168.1.63 |      5433
(2 rows)

postgres=#


复制

5.4、添加分布式表

#添加表
create table test (id int primary key,name varchar);
#配置分片策略
#设置分片数，3个主机，设置分片3，每个主机1个分片

postgres=# alter system set citus.shard_count=3;
ALTER SYSTEM
# 配置副本数为2
postgres=# alter system set citus.shard_replication_factor=2;
ALTER SYSTEM
postgres=#
postgres=# create table test (id int primary key,name varchar);
CREATE TABLE
postgres=# select create_distributed_table('test','id','hash');
 create_distributed_table
--------------------------

(1 row)

postgres=# insert into test select id,md5(random()::text) from generate_series(1,500000) as id;
INSERT 0 500000
postgres=#


复制

5.5、查看test分布表所在节点信息

postgres=# select * from citus_shards;
 table_name | shardid | shard_name  | citus_table_type | colocation_id |   nodename    | nodeport | shard_size
------------+---------+-------------+------------------+---------------+---------------+----------+------------
 test       |  102008 | test_102008 | distributed      |             1 | 192.168.1.62 |     5432 |   14581760
 test       |  102008 | test_102008 | distributed      |             1 | 192.168.1.63 |     5433 |   14581760
 test       |  102009 | test_102009 | distributed      |             1 | 192.168.1.62 |     5432 |   14450688
 test       |  102009 | test_102009 | distributed      |             1 | 192.168.1.63 |     5433 |   14450688
 test       |  102010 | test_102010 | distributed      |             1 | 192.168.1.63 |     5433 |   14655488
 test       |  102010 | test_102010 | distributed      |             1 | 192.168.1.62 |     5432 |   14655488
 test       |  102011 | test_102011 | distributed      |             1 | 192.168.1.62 |     5432 |   14655488
 test       |  102011 | test_102011 | distributed      |             1 | 192.168.1.63 |     5433 |   14655488
 test       |  102012 | test_102012 | distributed      |             1 | 192.168.1.62 |     5432 |   14794752
 test       |  102012 | test_102012 | distributed      |             1 | 192.168.1.63 |     5433 |   14794752
 test       |  102013 | test_102013 | distributed      |             1 | 192.168.1.62 |     5432 |   14647296
 test       |  102013 | test_102013 | distributed      |             1 | 192.168.1.63 |     5433 |   14647296
 test       |  102014 | test_102014 | distributed      |             1 | 192.168.1.62 |     5432 |   14368768
 test       |  102014 | test_102014 | distributed      |             1 | 192.168.1.63 |     5433 |   14368768
 test       |  102015 | test_102015 | distributed      |             1 | 192.168.1.63 |     5433 |   14655488
 test       |  102015 | test_102015 | distributed      |             1 | 192.168.1.62 |     5432 |   14655488
 test       |  102016 | test_102016 | distributed      |             1 | 192.168.1.62 |     5432 |   14319616
 test       |  102016 | test_102016 | distributed      |             1 | 192.168.1.63 |     5433 |   14319616
 test       |  102017 | test_102017 | distributed      |             1 | 192.168.1.62 |     5432 |   14704640
 test       |  102017 | test_102017 | distributed      |             1 | 192.168.1.63 |     5433 |   14704640
 test       |  102018 | test_102018 | distributed      |             1 | 192.168.1.63 |     5433 |   14368768
 test       |  102018 | test_102018 | distributed      |             1 | 192.168.1.62 |     5432 |   14368768
 test       |  102019 | test_102019 | distributed      |             1 | 192.168.1.62 |     5432 |   14647296
 test       |  102019 | test_102019 | distributed      |             1 | 192.168.1.63 |     5433 |   14647296
 test       |  102020 | test_102020 | distributed      |             1 | 192.168.1.63 |     5433 |   14671872
 test       |  102020 | test_102020 | distributed      |             1 | 192.168.1.62 |     5432 |   14671872
 test       |  102021 | test_102021 | distributed      |             1 | 192.168.1.62 |     5432 |   14712832
 test       |  102021 | test_102021 | distributed      |             1 | 192.168.1.63 |     5433 |   14712832
 test       |  102022 | test_102022 | distributed      |             1 | 192.168.1.63 |     5433 |   14639104
 test       |  102022 | test_102022 | distributed      |             1 | 192.168.1.62 |     5432 |   14639104
 test       |  102023 | test_102023 | distributed      |             1 | 192.168.1.62 |     5432 |   14786560
 test       |  102023 | test_102023 | distributed      |             1 | 192.168.1.63 |     5433 |   14786560
 test       |  102024 | test_102024 | distributed      |             1 | 192.168.1.63 |     5433 |   14639104
 test       |  102024 | test_102024 | distributed      |             1 | 192.168.1.62 |     5432 |   14639104
 test       |  102025 | test_102025 | distributed      |             1 | 192.168.1.63 |     5433 |   14647296
 test       |  102025 | test_102025 | distributed      |             1 | 192.168.1.62 |     5432 |   14647296
 test       |  102026 | test_102026 | distributed      |             1 | 192.168.1.63 |     5433 |   14655488
 test       |  102026 | test_102026 | distributed      |             1 | 192.168.1.62 |     5432 |   14655488
 test       |  102027 | test_102027 | distributed      |             1 | 192.168.1.62 |     5432 |   14663680
 test       |  102027 | test_102027 | distributed      |             1 | 192.168.1.63 |     5433 |   14663680
 test       |  102028 | test_102028 | distributed      |             1 | 192.168.1.63 |     5433 |   14516224
 test       |  102028 | test_102028 | distributed      |             1 | 192.168.1.62 |     5432 |   14516224
 test       |  102029 | test_102029 | distributed      |             1 | 192.168.1.62 |     5432 |   14786560
 test       |  102029 | test_102029 | distributed      |             1 | 192.168.1.63 |     5433 |   14786560
 test       |  102030 | test_102030 | distributed      |             1 | 192.168.1.62 |     5432 |   14647296
 test       |  102030 | test_102030 | distributed      |             1 | 192.168.1.63 |     5433 |   14647296
 test       |  102031 | test_102031 | distributed      |             1 | 192.168.1.62 |     5432 |   14712832
 test       |  102031 | test_102031 | distributed      |             1 | 192.168.1.63 |     5433 |   14712832
 test       |  102032 | test_102032 | distributed      |             1 | 192.168.1.63 |     5433 |   14573568
 test       |  102032 | test_102032 | distributed      |             1 | 192.168.1.62 |     5432 |   14573568
 test       |  102033 | test_102033 | distributed      |             1 | 192.168.1.63 |     5433 |   14671872
 test       |  102033 | test_102033 | distributed      |             1 | 192.168.1.62 |     5432 |   14671872
 test       |  102034 | test_102034 | distributed      |             1 | 192.168.1.62 |     5432 |   14663680
 test       |  102034 | test_102034 | distributed      |             1 | 192.168.1.63 |     5433 |   14663680
 test       |  102035 | test_102035 | distributed      |             1 | 192.168.1.62 |     5432 |   14639104
 test       |  102035 | test_102035 | distributed      |             1 | 192.168.1.63 |     5433 |   14639104
 test       |  102036 | test_102036 | distributed      |             1 | 192.168.1.63 |     5433 |   14393344
 test       |  102036 | test_102036 | distributed      |             1 | 192.168.1.62 |     5432 |   14393344
 test       |  102037 | test_102037 | distributed      |             1 | 192.168.1.62 |     5432 |   14647296
 test       |  102037 | test_102037 | distributed      |             1 | 192.168.1.63 |     5433 |   14647296
 test       |  102038 | test_102038 | distributed      |             1 | 192.168.1.63 |     5433 |   14589952
 test       |  102038 | test_102038 | distributed      |             1 | 192.168.1.62 |     5432 |   14589952
 test       |  102039 | test_102039 | distributed      |             1 | 192.168.1.63 |     5433 |   14647296
 test       |  102039 | test_102039 | distributed      |             1 | 192.168.1.62 |     5432 |   14647296
(64 rows)

postgres=#

复制

5.6、查看分布表信息

postgres=# select * from citus_tables ;
 table_name | citus_table_type | distribution_column | colocation_id | table_size | shard_count | tabl
e_owner | access_method
------------+------------------+---------------------+---------------+------------+-------------+-----
--------+---------------
 test       | distributed      | id                  |             1 | 93 MB      |          32 | post
gres    | heap
(1 row)

postgres=#

复制

5.7、查看分布表所在节点的信息

postgres=# select * from pg_dist_placement where shardid in(select shardid from pg_dist_shard where logicalrelid='test'::regclass);
 placementid | shardid | shardstate | shardlength | groupid
-------------+---------+------------+-------------+---------
           1 |  102008 |          1 |           0 |       1
           2 |  102008 |          1 |           0 |       2
           3 |  102009 |          1 |           0 |       2
           4 |  102009 |          1 |           0 |       1
           5 |  102010 |          1 |           0 |       1
           6 |  102010 |          1 |           0 |       2
           7 |  102011 |          1 |           0 |       2
           8 |  102011 |          1 |           0 |       1
           9 |  102012 |          1 |           0 |       1
          10 |  102012 |          1 |           0 |       2
          11 |  102013 |          1 |           0 |       2
          12 |  102013 |          1 |           0 |       1
          13 |  102014 |          1 |           0 |       1
          14 |  102014 |          1 |           0 |       2
          15 |  102015 |          1 |           0 |       2
          16 |  102015 |          1 |           0 |       1
          17 |  102016 |          1 |           0 |       1
          18 |  102016 |          1 |           0 |       2
          19 |  102017 |          1 |           0 |       2
          20 |  102017 |          1 |           0 |       1
          21 |  102018 |          1 |           0 |       1
          22 |  102018 |          1 |           0 |       2
          23 |  102019 |          1 |           0 |       2
          24 |  102019 |          1 |           0 |       1
          25 |  102020 |          1 |           0 |       1
          26 |  102020 |          1 |           0 |       2
          27 |  102021 |          1 |           0 |       2
          28 |  102021 |          1 |           0 |       1
          29 |  102022 |          1 |           0 |       1
          30 |  102022 |          1 |           0 |       2
          31 |  102023 |          1 |           0 |       2
          32 |  102023 |          1 |           0 |       1
          33 |  102024 |          1 |           0 |       1
          34 |  102024 |          1 |           0 |       2
          35 |  102025 |          1 |           0 |       2
          36 |  102025 |          1 |           0 |       1
          37 |  102026 |          1 |           0 |       1
          38 |  102026 |          1 |           0 |       2
          39 |  102027 |          1 |           0 |       2
          40 |  102027 |          1 |           0 |       1
          41 |  102028 |          1 |           0 |       1
          42 |  102028 |          1 |           0 |       2
          43 |  102029 |          1 |           0 |       2
          44 |  102029 |          1 |           0 |       1
          45 |  102030 |          1 |           0 |       1
          46 |  102030 |          1 |           0 |       2
          47 |  102031 |          1 |           0 |       2
          48 |  102031 |          1 |           0 |       1
          49 |  102032 |          1 |           0 |       1
          50 |  102032 |          1 |           0 |       2
          51 |  102033 |          1 |           0 |       2
          52 |  102033 |          1 |           0 |       1
          53 |  102034 |          1 |           0 |       1
          54 |  102034 |          1 |           0 |       2
          55 |  102035 |          1 |           0 |       2
          56 |  102035 |          1 |           0 |       1
          57 |  102036 |          1 |           0 |       1
          58 |  102036 |          1 |           0 |       2
          59 |  102037 |          1 |           0 |       2
          60 |  102037 |          1 |           0 |       1
          61 |  102038 |          1 |           0 |       1
          62 |  102038 |          1 |           0 |       2
          63 |  102039 |          1 |           0 |       2
          64 |  102039 |          1 |           0 |       1
(64 rows)

postgres=#

复制

5.8、在线扩容

5.8.1、部署PostgreSQL，相关设置参考上述部署方法。

5.8.2、在新增节点Node4添加扩展citus。

[postgres@Node4 ~]$  psql -h 192.168.1.64 -d postgres -U postgres -p 5434
psql (16.4)
Type "help" for help.

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

复制

5.8.3、在管理节点Node1添加扩展集群Node4。

[postgres@Node1 ~]$ psql -U postgres -dpostgres -h192.168.1.61
psql (16.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, compression: off)
Type "help" for help.

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

postgres=#

复制

5.8.5、查询最新节点信息。

postgres=# select * from master_get_active_worker_nodes();
   node_name   | node_port
---------------+-----------
 192.168.1.62 |      5432
 192.168.1.63 |      5433
 192.168.1.64 |      5434
(3 rows)

postgres=#

复制

六、相关问题

6.1、部署citus出错，代码如下

[postgres@Node2 citus-12.1.4]$ ./autogen.sh
./autogen.sh: line 7: autoreconf: command not found

复制

解决方案：安装扩展

sudo yum install autoconf automake libtool

复制

6.2、多台主机部署分布式集群时，出现以下错误

解决方案：
修改pg_hba.conf 增加以下信息：

vi $PGDATA/pg_hba.conf

host all all 192.168.1.1/24 trust

host all all 0.0.0.0/0 scram-sha-256

七、总结

Citus是一个开源的扩展，它将PostgreSQL数据库转换为分布式数据库，适用于处理大规模数据集。它通过水平分片（sharding）和分布式查询来提高查询性能和可扩展性。以下是Citus的基本使用方法：

1、安装Citus： 可以通过Docker镜像在开发或测试环境中快速启动Citus。 对于生产环境，可以在Linux系统上通过包管理器安装Citus。

2、初始化集群： 创建一个新的PostgreSQL数据库实例，并初始化Citus扩展。 配置shared_preload_libraries以包含Citus扩展。

3、创建分布式表： 使用Citus提供的函数创建分布式表，并指定分片键。 数据将根据分片键自动分布到集群的不同节点上。

4、数据加载： 使用标准的PostgreSQL命令（如COPY）将数据加载到分布式表中。

5、执行查询： 在Citus协调器节点上执行查询，Citus会自动将查询分发到集群的工作节点进行处理。

6、扩展集群： 随着数据量的增长，可以通过添加新的工作节点来水平扩展Citus集群。

7、 管理和维护： 监控集群性能，调整配置参数以优化查询执行。 使用Citus提供的管理工具和命令进行集群管理。

墨力计划citus分布式数据库postgresqlopengauss
最后修改时间：2024-09-11 12:06:50
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

信创数据库学习中心（共76篇）
信创数据库学习中心

评论

A
admin@@
暂无图片
4月前
评论
暂无图片 0
大佬👍
4月前
暂无图片 点赞
评论
暂无图片
TDengine
暂无图片
7月前
评论
暂无图片 0
你好，我们诚挚邀请您参与 2024 TDengine 征文活动。您在过往的文章中展现出的才华和热情，让我们印象深刻。此次，我们准备了全新的主题和更加丰厚的奖品，包括AirPods等精美礼品。我们相信，您的见解和经验将再次为TDengine技术社区带来宝贵的贡献。期待您的佳作，让我们共同推动技术的进步！ 报名链接：https://taosdata.feishu.cn/share/base/form/shrcnX183FWh2WqkdT7poxPC3Kc?hide_%E5%A4%87%E6%B3%A8=1 活动详情：https://mp.weixin.qq.com/s/i6DqYB7F8cAHTBLREVZH7w
7月前
暂无图片 点赞
评论

相关阅读

2025年3月国产数据库大事记
墨天轮编辑部
923次阅读
2025-04-03 15:21:16
外国CTO也感兴趣的开源数据库项目——openHalo
小满未满、
658次阅读
2025-04-21 16:58:09
国产数据库需要扩大场景覆盖面才能在竞争中更有优势
白鳝的洞穴
489次阅读
2025-04-14 09:40:20
9.9 分高危漏洞，尽快升级到 pgAdmin 4 v9.2 进行修复
严少安
364次阅读
2025-04-11 10:43:23
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
349次阅读
2025-04-15 14:48:05
openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
严少安
322次阅读
2025-04-07 12:14:29
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
291次阅读
2025-04-17 10:41:41
AI关键场景得到全面支持！OceanBase入选Forrester报告三大领域代表厂商
OceanBase数据库
248次阅读
2025-04-19 22:27:54
openGauss 7.0.0-RC1 版本正式发布!
Gauss松鼠会
214次阅读
2025-04-01 12:27:03
openGauss 7.0.0-RC1 版本体验：一主一备快速安装指南
孙莹
193次阅读
2025-04-01 10:30:07
达
达梦大数据
关注
978
文章
154
粉丝
635K+
浏览量
暂无图片
获得了1175次点赞
暂无图片
内容获得901次评论
暂无图片
获得了187次收藏
TA的专栏
信创数据库学习中心
收录76篇内容
热门文章
openGauss智能化运维监控平台——DataKit
2024-10-29 10454浏览
openGauss6.0.0（LTS）企业版搭建主备集群保姆级部署手册
2024-10-16 5724浏览
GBase Data Studio管理工具使用方法
2024-03-22 3552浏览
人大金仓KingBase V9集群保姆级部署手册
2024-06-03 3038浏览
PostgreSQL+repmgr集群部署（一主一备一仲裁）
2024-05-12 2960浏览
在线实训环境入口
openGauss在线实训环境
查看详情
最新文章
【KWDB 2025 创作者计划】_轻松搞定！KaiwuDB单节点裸机部署（二进制安装包版）
2025-04-08 318浏览
微信公众号接入腾讯元宝实现智能应答模式保姆式操作手册
2025-02-24 340浏览
SQLServer 2022主备集群保姆级部署手册（按文章操作，保证成功）
2025-01-22 812浏览
这样部署学习YashanDB，保证您在2025年成为大师
2024-12-31 241浏览
CentOS 7.9部署MySQL 8.4.3 LTS保姆级手册
2024-12-15 665浏览
目录
  • 一、citus功能概述
    • 1.1、什么是 Citus
    • 1.2、获取 Citus 的两种方法
    • 1.3、Citus 为您提供任意规模的 Postgres
    • 1.4、Citus集群构成
    • 1.5、Citus 对于多租户应用程序的一些优势：
    • 1.6、使用注意事项
    • 1.7、不适合使用Citus的场景
  • 二、初始化环境
    • 2.1、环境规划
    • 2.2、统一主机名称
    • 2.3、关闭SELINUX
    • 2.4、关闭防火墙
    • 2.5、安装插件
    • 2.6、编辑hosts
    • 2.6、用户limits设置
    • 2.7、调整内核
    • 2.8、时间同步
    • 2.9、创建用户
    • 2.10、所有节点配置sudo
  • 三、部署PostgreSQL
    • 3.1、目录规划
    • 3.2、部署PostgreSQL（三台主机Node1、Node2、Node3都要部署）
    • 3.3、拷贝安装包到其他主机
    • 3.4、解压postgresql并安装
    • 3.5、设置软链接
    • 3.6、设置环境变量
    • 3.7、初始化数据库
    • 3.8、设置开机自启
    • 3.9、设置postgresql.conf
    • 3.10、配置pg_hba.conf
  • 四、部署citus
    • 4.1、下载citus安装包
    • 4.2、拷贝安装包到其他主机
    • 4.3、安装citus安装包
    • 4.4、安装插件
    • 4.5、安装citus
    • 4.6、启动PostgreSQL
    • 4.7、添加扩展
  • 五、Citus使用
    • 5.1、添Citus使用说明
    • 5.2、添加工作节点式主机
    • 5.3、查看工作节点信息
    • 5.4、添加分布式表
    • 5.5、查看test分布表所在节点信息
    • 5.6、查看分布表信息
    • 5.7、查看分布表所在节点的信息
    • 5.8、在线扩容
      • 5.8.1、部署PostgreSQL，相关设置参考上述部署方法。
      • 5.8.2、在新增节点Node4添加扩展citus。
      • 5.8.3、在管理节点Node1添加扩展集群Node4。
      • 5.8.5、查询最新节点信息。
  • 六、相关问题
    • 6.1、部署citus出错，代码如下
    • 6.2、多台主机部署分布式集群时，出现以下错误
  • 七、总结
    • Citus是一个开源的扩展，它将PostgreSQL数据库转换为分布式数据库，适用于处理大规模数据集。它通过水平分片（sharding）和分布式查询来提高查询性能和可扩展性。以下是Citus的基本使用方法：
    • 1、安装Citus： 可以通过Docker镜像在开发或测试环境中快速启动Citus。 对于生产环境，可以在Linux系统上通过包管理器安装Citus。
    • 2、初始化集群： 创建一个新的PostgreSQL数据库实例，并初始化Citus扩展。 配置shared_preload_libraries以包含Citus扩展。
    • 3、创建分布式表： 使用Citus提供的函数创建分布式表，并指定分片键。 数据将根据分片键自动分布到集群的不同节点上。
    • 4、数据加载： 使用标准的PostgreSQL命令（如COPY）将数据加载到分布式表中。
    • 5、执行查询： 在Citus协调器节点上执行查询，Citus会自动将查询分发到集群的工作节点进行处理。
    • 6、扩展集群： 随着数据量的增长，可以通过添加新的工作节点来水平扩展Citus集群。
    • 7、 管理和维护： 监控集群性能，调整配置参数以优化查询执行。 使用Citus提供的管理工具和命令进行集群管理。