Citus 数据库是 Postgres 的开源扩展，可以获得分布式 Postgres 功能，例如 分片、分布式表、引用表、分布式查询引擎、列式存储等。
从 Citus 11.0 开始支持从任何节点进行查询。 Citus结合了并行性，在内存中保留了更多数据，并且更高 I/O 带宽可以显著提高多租户的性能 SaaS 应用程序、面向客户的实时分析仪表板和时间系列工作负载。
获取citus路径
rpm包：https://www.citusdata.com/download/
源码：https://github.com/citusdata/citus
以下实验为rpm包多节点部署环境及步骤：
| 信息 | |
|---|---|
| 系统版本 | Red Hat Enterprise Linux release 8.8 (Ootpa) |
| 数据库版本 | PostgreSQL 16.1 |
| citus版本 | Citus 12.1.1 |
| 部署节点 | master:192.168.126.162，192.168.126.163 |
安装依赖
[root@rh8-cituspg-162 ~]# yum -y install readline-devel zlib-devel gcc perl perl-devel perl-ExtUtils-Embed python python-devel gcc gcc-c++ libyaml zlib zlib-devel openssl openssl-devel
要在所有节点上执行的步骤
下载存储库
[root@rh8-cituspg-162 ~]# curl https://install.citusdata.com/community/rpm.sh |bash
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 8471 100 8471 0 0 5351 0 0:00:01 0:00:01 --:--:-- 5347
Detected operating system as rhel/8.
Checking for curl...
Detected curl...
Checking for postgresql16-server...
Detected postgresql16-server...
Checking for EPEL repositories...
Detected EPEL repoitories
Downloading repository file: https://repos.citusdata.com/community/config_file.repo?os=rhel&dist=8&source=script... done.
Installing pygpgme to verify GPG signatures...
WARNING:
The pygpgme package could not be installed. This means GPG verification is not possible for any RPM installed on your system.
To fix this, add a repository with pygpgme. Usualy, the EPEL repository for your system will have this.
More information: https://fedoraproject.org/wiki/EPEL#How_can_I_use_these_extra_packages.3F
done.
Installing yum-utils... done.
Generating yum cache for citusdata_community... done.
The repository is set up! You can now install packages.
安装citus及postgresql
[root@rh8-cituspg-162 ~]# yum install -y citus121_16
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered with an entitlement server. You can use subscription-manager to register.
Last metadata expiration check: 0:00:48 ago on Mon 08 Jan 2024 12:18:45 AM PST.
Dependencies resolved.
=============================================================================================================================================================================================================================================
Package Architecture Version Repository Size
=============================================================================================================================================================================================================================================
Installing:
citus121_16 x86_64 12.1.1.citus-1.el8 citusdata_community 3.4 M
Installing dependencies:
postgresql16 x86_64 16.1-4PGDG.rhel8 pgdg16 1.9 M
postgresql16-libs x86_64 16.1-4PGDG.rhel8 pgdg16 327 k
postgresql16-server x86_64 16.1-4PGDG.rhel8 pgdg16 6.7 M
Transaction Summary
=============================================================================================================================================================================================================================================
Install 4 Packages
Total download size: 12 M
Installed size: 48 M
Downloading Packages:
(1/4): postgresql16-libs-16.1-4PGDG.rhel8.x86_64.rpm 125 kB/s | 327 kB 00:02
(2/4): postgresql16-16.1-4PGDG.rhel8.x86_64.rpm 586 kB/s | 1.9 MB 00:03
(3/4): citus121_16-12.1.1.citus-1.el8.x86_64.rpm 675 kB/s | 3.4 MB 00:05
(4/4): postgresql16-server-16.1-4PGDG.rhel8.x86_64.rpm 2.5 MB/s | 6.7 MB 00:02
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 2.3 MB/s | 12 MB 00:05
PostgreSQL 16 for RHEL / Rocky / AlmaLinux 8 - x86_64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql16-libs-16.1-4PGDG.rhel8.x86_64 1/4
Running scriptlet: postgresql16-libs-16.1-4PGDG.rhel8.x86_64 1/4
Installing : postgresql16-16.1-4PGDG.rhel8.x86_64 2/4
Running scriptlet: postgresql16-16.1-4PGDG.rhel8.x86_64 2/4
Running scriptlet: postgresql16-server-16.1-4PGDG.rhel8.x86_64 3/4
Installing : postgresql16-server-16.1-4PGDG.rhel8.x86_64 3/4
Running scriptlet: postgresql16-server-16.1-4PGDG.rhel8.x86_64 3/4
Installing : citus121_16-12.1.1.citus-1.el8.x86_64 4/4
Running scriptlet: citus121_16-12.1.1.citus-1.el8.x86_64 4/4
Verifying : citus121_16-12.1.1.citus-1.el8.x86_64 1/4
Verifying : postgresql16-16.1-4PGDG.rhel8.x86_64 2/4
Verifying : postgresql16-libs-16.1-4PGDG.rhel8.x86_64 3/4
Verifying : postgresql16-server-16.1-4PGDG.rhel8.x86_64 4/4
Installed products updated.
Installed:
citus121_16-12.1.1.citus-1.el8.x86_64 postgresql16-16.1-4PGDG.rhel8.x86_64 postgresql16-libs-16.1-4PGDG.rhel8.x86_64 postgresql16-server-16.1-4PGDG.rhel8.x86_64
Complete!
创建数据目录，初始化
[root@rh8-cituspg-162 ~]# su - postgres
[postgres@rh8-cituspg-162 ~]$ mkdir citus
[postgres@rh8-cituspg-162 ~]$ /usr/pgsql-16/bin/initdb -D citus/
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 database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory citus ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Los_Angeles
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:
/usr/pgsql-16/bin/pg_ctl -D citus/ -l logfile start
修改配置文件，使用citus组件
[postgres@rh8-cituspg-162 citus]$ vim citus/postgresql.conf
listen_addresses = '*'
shared_preload_libraries = 'citus'
设置环境变量
[postgres@rh8-cituspg-162 ~]$ vim .bash_profile
export PATH=$PATH:/usr/pgsql-16/bin
export PGHOME=/usr/pgsql-16
export PGDATA=/var/lib/pgsql/citus
设置hosts文件
[root@rh8-cituspg-162 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.126.162 rh8-cituspg-162
192.168.126.163 rh8-cituspg-163
设置白名单
[postgres@rh8-cituspg-162 ~]$ vim citus/pg_hba.conf
host all all 192.168.126.0/24 trust
启动数据库
[postgres@rh8-cituspg-162 ~]$ source .bash_profile
[postgres@rh8-cituspg-162 ~]$ pg_ctl -D citus -o "-p 9700" -l citus_logfile start
waiting for server to start.... done
server started
加载citus组件，查看版本
[postgres@rh8-cituspg-162 ~]$ psql -p 9700
psql (16.1)
Type "help" for help.
postgres=# create extension citus;
CREATE EXTENSION
postgres=# select citus_version();
citus_version
------------------------------------------------------------------------------------------------------
Citus 12.1.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit
(1 row)
--查看citus相关的表
citusdb=# \dt *dist*
List of relations
Schema | Name | Type | Owner
------------+--------------------------------+-------+----------
pg_catalog | pg_dist_authinfo | table | postgres
pg_catalog | pg_dist_background_job | table | postgres
pg_catalog | pg_dist_background_task | table | postgres
pg_catalog | pg_dist_background_task_depend | table | postgres
pg_catalog | pg_dist_cleanup | table | postgres
pg_catalog | pg_dist_colocation | table | postgres
pg_catalog | pg_dist_local_group | table | postgres
pg_catalog | pg_dist_node | table | postgres
pg_catalog | pg_dist_node_metadata | table | postgres
pg_catalog | pg_dist_object | table | postgres
pg_catalog | pg_dist_partition | table | postgres
pg_catalog | pg_dist_placement | table | postgres
pg_catalog | pg_dist_poolinfo | table | postgres
pg_catalog | pg_dist_rebalance_strategy | table | postgres
pg_catalog | pg_dist_schema | table | postgres
pg_catalog | pg_dist_shard | table | postgres
pg_catalog | pg_dist_transaction | table | postgres
(17 rows)
在协调器节点上执行的步骤
添加并查看工作节点信息
citusdb=# SELECT citus_set_coordinator_host('rh8-cituspg-162', 9700);
citus_set_coordinator_host
----------------------------
(1 row)
citusdb=# SELECT * from citus_add_node('rh8-cituspg-163', 9700);
citus_add_node
----------------
4
(1 row)
citusdb=# SELECT * FROM citus_get_active_worker_nodes();
node_name | node_port
-----------------+-----------
rh8-cituspg-163 | 9700
(1 row)
citusdb=# select * from pg_dist_node;
nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+-----------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
2 | 0 | rh8-cituspg-162 | 9700 | default | t | t | primary | default | t | f
4 | 3 | rh8-cituspg-163 | 9700 | default | t | t | primary | default | t | t
(2 rows)
创建测试表
--在创建表的时候可以直接设置PRIMARY KEY和FOREIGN KEY，建表时指定的则可以自动对表进行分片
citusdb=# CREATE TABLE campaigns (
citusdb(# id bigserial,
citusdb(# company_id bigint REFERENCES companies (id),
citusdb(# name text NOT NULL,
citusdb(# cost_model text NOT NULL,
citusdb(# state text NOT NULL,
citusdb(# monthly_budget bigint,
citusdb(# blacklisted_site_urls text[],
citusdb(# created_at timestamp without time zone NOT NULL,
citusdb(# updated_at timestamp without time zone NOT NULL,
citusdb(# PRIMARY KEY (company_id, id)
citusdb(# );
CREATE TABLE
citusdb=# CREATE TABLE ads (
citusdb(# id bigserial,
citusdb(# company_id bigint,
citusdb(# campaign_id bigint,
citusdb(# name text NOT NULL,
citusdb(# image_url text,
citusdb(# target_url text,
citusdb(# impressions_count bigint DEFAULT 0,
citusdb(# clicks_count bigint DEFAULT 0,
citusdb(# created_at timestamp without time zone NOT NULL,
citusdb(# updated_at timestamp without time zone NOT NULL,
citusdb(# PRIMARY KEY (company_id, id),
citusdb(# FOREIGN KEY (company_id, campaign_id)
citusdb(# REFERENCES campaigns (company_id, id)
citusdb(# );
CREATE TABLE
--也可以建好表之后添加主键信息，不过就需要使用create_distributed_table函数将表分布到不同节点上，如果不执行，则该表未进行分片
citusdb=# CREATE TABLE companies (
citusdb(# id bigint NOT NULL,
citusdb(# name text NOT NULL,
citusdb(# image_url text,
citusdb(# created_at timestamp without time zone NOT NULL,
citusdb(# updated_at timestamp without time zone NOT NULL
citusdb(# );
CREATE TABLE
citusdb=# ALTER TABLE companies ADD PRIMARY KEY (id);
ALTER TABLE
citusdb=# SELECT create_distributed_table('companies', 'id');
create_distributed_table
--------------------------
(1 row)
--导入数据
citusdb=# \copy campaigns from 'campaigns.csv' with csv
COPY 978
citusdb=# \copy ads from 'ads.csv' with csv
COPY 7364
citusdb=# \copy companies from 'companies.csv' with csv;
COPY 100
rh8-cituspg-163节点查看表信息
[postgres@rh8-cituspg-163 citus]$ psql citusdb -p 9700
psql (16.1)
Type "help" for help.
citusdb=# SELECT * FROM citus_tables;
table_name | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method
------------+------------------+---------------------+---------------+------------+-------------+-------------+---------------
ads | distributed | company_id | 2 | 2944 kB | 32 | postgres | heap
campaigns | distributed | company_id | 2 | 1008 kB | 32 | postgres | heap
companies | distributed | id | 2 | 1008 kB | 32 | postgres | heap
(3 rows)
citusdb=# select count(*) from ads;
count
-------
7364
(1 row)
citusdb=# select count(*) from campaigns;
count
-------
978
(1 row)
citusdb=# select count(*) from companies;
count
-------
100
(1 row)
--查看分布表信息
citusdb=# select * from pg_dist_shard where logicalrelid=25639;
logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue
--------------+---------+--------------+---------------+---------------
companies | 102232 | t | -2147483648 | -2013265921
companies | 102233 | t | -2013265920 | -1879048193
companies | 102234 | t | -1879048192 | -1744830465
companies | 102235 | t | -1744830464 | -1610612737
companies | 102236 | t | -1610612736 | -1476395009
companies | 102237 | t | -1476395008 | -1342177281
companies | 102238 | t | -1342177280 | -1207959553
companies | 102239 | t | -1207959552 | -1073741825
companies | 102240 | t | -1073741824 | -939524097
companies | 102241 | t | -939524096 | -805306369
companies | 102242 | t | -805306368 | -671088641
companies | 102243 | t | -671088640 | -536870913
companies | 102244 | t | -536870912 | -402653185
companies | 102245 | t | -402653184 | -268435457
companies | 102246 | t | -268435456 | -134217729
companies | 102247 | t | -134217728 | -1
companies | 102248 | t | 0 | 134217727
companies | 102249 | t | 134217728 | 268435455
companies | 102250 | t | 268435456 | 402653183
companies | 102251 | t | 402653184 | 536870911
companies | 102252 | t | 536870912 | 671088639
companies | 102253 | t | 671088640 | 805306367
companies | 102254 | t | 805306368 | 939524095
companies | 102255 | t | 939524096 | 1073741823
companies | 102256 | t | 1073741824 | 1207959551
companies | 102257 | t | 1207959552 | 1342177279
companies | 102258 | t | 1342177280 | 1476395007
companies | 102259 | t | 1476395008 | 1610612735
companies | 102260 | t | 1610612736 | 1744830463
companies | 102261 | t | 1744830464 | 1879048191
companies | 102262 | t | 1879048192 | 2013265919
companies | 102263 | t | 2013265920 | 2147483647
(32 rows)
以上为citus的简单部署及示例，先分享到这里，欢迎补充。
