PostgreSQl学习实践:PG16.1 多节点部署 Citus 12.1.1

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

安装依赖

  
  
  
  1. [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

要在所有节点上执行的步骤
下载存储库

  
  
  
  1. [root@rh8-cituspg-162 ~]# curl https://install.citusdata.com/community/rpm.sh |bash

  2. % Total % Received % Xferd Average Speed Time Time Time Current

  3. Dload Upload Total Spent Left Speed

  4. 100 8471 100 8471 0 0 5351 0 0:00:01 0:00:01 --:--:-- 5347

  5. Detected operating system as rhel/8.

  6. Checking for curl...

  7. Detected curl...

  8. Checking for postgresql16-server...

  9. Detected postgresql16-server...

  10. Checking for EPEL repositories...

  11. Detected EPEL repoitories

  12. Downloading repository file: https://repos.citusdata.com/community/config_file.repo?os=rhel&dist=8&source=script... done.

  13. Installing pygpgme to verify GPG signatures...

  14. WARNING:

  15. The pygpgme package could not be installed. This means GPG verification is not possible for any RPM installed on your system.

  16. To fix this, add a repository with pygpgme. Usualy, the EPEL repository for your system will have this.

  17. More information: https://fedoraproject.org/wiki/EPEL#How_can_I_use_these_extra_packages.3F

  18. done.

  19. Installing yum-utils... done.

  20. Generating yum cache for citusdata_community... done.

  21. The repository is set up! You can now install packages.

安装citus及postgresql

  
  
  
  1. [root@rh8-cituspg-162 ~]# yum install -y citus121_16

  2. Updating Subscription Management repositories.

  3. Unable to read consumer identity

  4. This system is not registered with an entitlement server. You can use subscription-manager to register.

  5. Last metadata expiration check: 0:00:48 ago on Mon 08 Jan 2024 12:18:45 AM PST.

  6. Dependencies resolved.

  7. =============================================================================================================================================================================================================================================

  8. Package Architecture Version Repository Size

  9. =============================================================================================================================================================================================================================================

  10. Installing:

  11. citus121_16 x86_64 12.1.1.citus-1.el8 citusdata_community 3.4 M

  12. Installing dependencies:

  13. postgresql16 x86_64 16.1-4PGDG.rhel8 pgdg16 1.9 M

  14. postgresql16-libs x86_64 16.1-4PGDG.rhel8 pgdg16 327 k

  15. postgresql16-server x86_64 16.1-4PGDG.rhel8 pgdg16 6.7 M

  16. Transaction Summary

  17. =============================================================================================================================================================================================================================================

  18. Install 4 Packages

  19. Total download size: 12 M

  20. Installed size: 48 M

  21. Downloading Packages:

  22. (1/4): postgresql16-libs-16.1-4PGDG.rhel8.x86_64.rpm 125 kB/s | 327 kB 00:02

  23. (2/4): postgresql16-16.1-4PGDG.rhel8.x86_64.rpm 586 kB/s | 1.9 MB 00:03

  24. (3/4): citus121_16-12.1.1.citus-1.el8.x86_64.rpm 675 kB/s | 3.4 MB 00:05

  25. (4/4): postgresql16-server-16.1-4PGDG.rhel8.x86_64.rpm 2.5 MB/s | 6.7 MB 00:02

  26. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  27. Total 2.3 MB/s | 12 MB 00:05

  28. PostgreSQL 16 for RHEL / Rocky / AlmaLinux 8 - x86_64 2.4 MB/s | 2.4 kB 00:00

  29. Importing GPG key 0x08B40D20:

  30. Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"

  31. Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20

  32. From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL

  33. Key imported successfully

  34. Running transaction check

  35. Transaction check succeeded.

  36. Running transaction test

  37. Transaction test succeeded.

  38. Running transaction

  39. Preparing : 1/1

  40. Installing : postgresql16-libs-16.1-4PGDG.rhel8.x86_64 1/4

  41. Running scriptlet: postgresql16-libs-16.1-4PGDG.rhel8.x86_64 1/4

  42. Installing : postgresql16-16.1-4PGDG.rhel8.x86_64 2/4

  43. Running scriptlet: postgresql16-16.1-4PGDG.rhel8.x86_64 2/4

  44. Running scriptlet: postgresql16-server-16.1-4PGDG.rhel8.x86_64 3/4

  45. Installing : postgresql16-server-16.1-4PGDG.rhel8.x86_64 3/4

  46. Running scriptlet: postgresql16-server-16.1-4PGDG.rhel8.x86_64 3/4

  47. Installing : citus121_16-12.1.1.citus-1.el8.x86_64 4/4

  48. Running scriptlet: citus121_16-12.1.1.citus-1.el8.x86_64 4/4

  49. Verifying : citus121_16-12.1.1.citus-1.el8.x86_64 1/4

  50. Verifying : postgresql16-16.1-4PGDG.rhel8.x86_64 2/4

  51. Verifying : postgresql16-libs-16.1-4PGDG.rhel8.x86_64 3/4

  52. Verifying : postgresql16-server-16.1-4PGDG.rhel8.x86_64 4/4

  53. Installed products updated.

  54. Installed:

  55. 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

  56. Complete!

创建数据目录,初始化

  
  
  
  1. [root@rh8-cituspg-162 ~]# su - postgres

  2. [postgres@rh8-cituspg-162 ~]$ mkdir citus

  3. [postgres@rh8-cituspg-162 ~]$ /usr/pgsql-16/bin/initdb -D citus/

  4. The files belonging to this database system will be owned by user "postgres".

  5. This user must also own the server process.

  6. The database cluster will be initialized with locale "en_US.UTF-8".

  7. The default database encoding has accordingly been set to "UTF8".

  8. The default text search configuration will be set to "english".

  9. Data page checksums are disabled.

  10. fixing permissions on existing directory citus ... ok

  11. creating subdirectories ... ok

  12. selecting dynamic shared memory implementation ... posix

  13. selecting default max_connections ... 100

  14. selecting default shared_buffers ... 128MB

  15. selecting default time zone ... America/Los_Angeles

  16. creating configuration files ... ok

  17. running bootstrap script ... ok

  18. performing post-bootstrap initialization ... ok

  19. syncing data to disk ... ok

  20. initdb: warning: enabling "trust" authentication for local connections

  21. 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.

  22. Success. You can now start the database server using:

  23. /usr/pgsql-16/bin/pg_ctl -D citus/ -l logfile start

修改配置文件,使用citus组件

  
  
  
  1. [postgres@rh8-cituspg-162 citus]$ vim citus/postgresql.conf

  2. listen_addresses = '*'

  3. shared_preload_libraries = 'citus'

设置环境变量

  
  
  
  1. [postgres@rh8-cituspg-162 ~]$ vim .bash_profile

  2. export PATH=$PATH:/usr/pgsql-16/bin

  3. export PGHOME=/usr/pgsql-16

  4. export PGDATA=/var/lib/pgsql/citus

设置hosts文件

  
  
  
  1. [root@rh8-cituspg-162 ~]# cat /etc/hosts

  2. 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

  3. ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

  4. 192.168.126.162 rh8-cituspg-162

  5. 192.168.126.163 rh8-cituspg-163

设置白名单

  
  
  
  1. [postgres@rh8-cituspg-162 ~]$ vim citus/pg_hba.conf

  2. host all all 192.168.126.0/24 trust

启动数据库

  
  
  
  1. [postgres@rh8-cituspg-162 ~]$ source .bash_profile

  2. [postgres@rh8-cituspg-162 ~]$ pg_ctl -D citus -o "-p 9700" -l citus_logfile start

  3. waiting for server to start.... done

  4. server started

加载citus组件,查看版本

  
  
  
  1. [postgres@rh8-cituspg-162 ~]$ psql -p 9700

  2. psql (16.1)

  3. Type "help" for help.

  4. postgres=# create extension citus;

  5. CREATE EXTENSION

  6. postgres=# select citus_version();

  7. citus_version

  8. ------------------------------------------------------------------------------------------------------

  9. 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

  10. (1 row)

  11. --查看citus相关的表

  12. citusdb=# \dt *dist*

  13. List of relations

  14. Schema | Name | Type | Owner

  15. ------------+--------------------------------+-------+----------

  16. pg_catalog | pg_dist_authinfo | table | postgres

  17. pg_catalog | pg_dist_background_job | table | postgres

  18. pg_catalog | pg_dist_background_task | table | postgres

  19. pg_catalog | pg_dist_background_task_depend | table | postgres

  20. pg_catalog | pg_dist_cleanup | table | postgres

  21. pg_catalog | pg_dist_colocation | table | postgres

  22. pg_catalog | pg_dist_local_group | table | postgres

  23. pg_catalog | pg_dist_node | table | postgres

  24. pg_catalog | pg_dist_node_metadata | table | postgres

  25. pg_catalog | pg_dist_object | table | postgres

  26. pg_catalog | pg_dist_partition | table | postgres

  27. pg_catalog | pg_dist_placement | table | postgres

  28. pg_catalog | pg_dist_poolinfo | table | postgres

  29. pg_catalog | pg_dist_rebalance_strategy | table | postgres

  30. pg_catalog | pg_dist_schema | table | postgres

  31. pg_catalog | pg_dist_shard | table | postgres

  32. pg_catalog | pg_dist_transaction | table | postgres

  33. (17 rows)

在协调器节点上执行的步骤
添加并查看工作节点信息

  
  
  
  1. citusdb=# SELECT citus_set_coordinator_host('rh8-cituspg-162', 9700);

  2. citus_set_coordinator_host

  3. ----------------------------

  4. (1 row)

  5. citusdb=# SELECT * from citus_add_node('rh8-cituspg-163', 9700);

  6. citus_add_node

  7. ----------------

  8. 4

  9. (1 row)

  10. citusdb=# SELECT * FROM citus_get_active_worker_nodes();

  11. node_name | node_port

  12. -----------------+-----------

  13. rh8-cituspg-163 | 9700

  14. (1 row)

  15. citusdb=# select * from pg_dist_node;

  16. nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards

  17. --------+---------+-----------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------

  18. 2 | 0 | rh8-cituspg-162 | 9700 | default | t | t | primary | default | t | f

  19. 4 | 3 | rh8-cituspg-163 | 9700 | default | t | t | primary | default | t | t

  20. (2 rows)

创建测试表

  
  
  
  1. --在创建表的时候可以直接设置PRIMARY KEYFOREIGN KEY,建表时指定的则可以自动对表进行分片

  2. citusdb=# CREATE TABLE campaigns (

  3. citusdb(# id bigserial,

  4. citusdb(# company_id bigint REFERENCES companies (id),

  5. citusdb(# name text NOT NULL,

  6. citusdb(# cost_model text NOT NULL,

  7. citusdb(# state text NOT NULL,

  8. citusdb(# monthly_budget bigint,

  9. citusdb(# blacklisted_site_urls text[],

  10. citusdb(# created_at timestamp without time zone NOT NULL,

  11. citusdb(# updated_at timestamp without time zone NOT NULL,

  12. citusdb(# PRIMARY KEY (company_id, id)

  13. citusdb(# );

  14. CREATE TABLE

  15. citusdb=# CREATE TABLE ads (

  16. citusdb(# id bigserial,

  17. citusdb(# company_id bigint,

  18. citusdb(# campaign_id bigint,

  19. citusdb(# name text NOT NULL,

  20. citusdb(# image_url text,

  21. citusdb(# target_url text,

  22. citusdb(# impressions_count bigint DEFAULT 0,

  23. citusdb(# clicks_count bigint DEFAULT 0,

  24. citusdb(# created_at timestamp without time zone NOT NULL,

  25. citusdb(# updated_at timestamp without time zone NOT NULL,

  26. citusdb(# PRIMARY KEY (company_id, id),

  27. citusdb(# FOREIGN KEY (company_id, campaign_id)

  28. citusdb(# REFERENCES campaigns (company_id, id)

  29. citusdb(# );

  30. CREATE TABLE

  31. --也可以建好表之后添加主键信息,不过就需要使用create_distributed_table函数将表分布到不同节点上,如果不执行,则该表未进行分片

  32. citusdb=# CREATE TABLE companies (

  33. citusdb(# id bigint NOT NULL,

  34. citusdb(# name text NOT NULL,

  35. citusdb(# image_url text,

  36. citusdb(# created_at timestamp without time zone NOT NULL,

  37. citusdb(# updated_at timestamp without time zone NOT NULL

  38. citusdb(# );

  39. CREATE TABLE

  40. citusdb=# ALTER TABLE companies ADD PRIMARY KEY (id);

  41. ALTER TABLE

  42. citusdb=# SELECT create_distributed_table('companies', 'id');

  43. create_distributed_table

  44. --------------------------

  45. (1 row)

  46. --导入数据

  47. citusdb=# \copy campaigns from 'campaigns.csv' with csv

  48. COPY 978

  49. citusdb=# \copy ads from 'ads.csv' with csv

  50. COPY 7364

  51. citusdb=# \copy companies from 'companies.csv' with csv;

  52. COPY 100

rh8-cituspg-163节点查看表信息

  
  
  
  1. [postgres@rh8-cituspg-163 citus]$ psql citusdb -p 9700

  2. psql (16.1)

  3. Type "help" for help.

  4. citusdb=# SELECT * FROM citus_tables;

  5. table_name | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method

  6. ------------+------------------+---------------------+---------------+------------+-------------+-------------+---------------

  7. ads | distributed | company_id | 2 | 2944 kB | 32 | postgres | heap

  8. campaigns | distributed | company_id | 2 | 1008 kB | 32 | postgres | heap

  9. companies | distributed | id | 2 | 1008 kB | 32 | postgres | heap

  10. (3 rows)

  11. citusdb=# select count(*) from ads;

  12. count

  13. -------

  14. 7364

  15. (1 row)

  16. citusdb=# select count(*) from campaigns;

  17. count

  18. -------

  19. 978

  20. (1 row)

  21. citusdb=# select count(*) from companies;

  22. count

  23. -------

  24. 100

  25. (1 row)

  26. --查看分布表信息

  27. citusdb=# select * from pg_dist_shard where logicalrelid=25639;

  28. logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue

  29. --------------+---------+--------------+---------------+---------------

  30. companies | 102232 | t | -2147483648 | -2013265921

  31. companies | 102233 | t | -2013265920 | -1879048193

  32. companies | 102234 | t | -1879048192 | -1744830465

  33. companies | 102235 | t | -1744830464 | -1610612737

  34. companies | 102236 | t | -1610612736 | -1476395009

  35. companies | 102237 | t | -1476395008 | -1342177281

  36. companies | 102238 | t | -1342177280 | -1207959553

  37. companies | 102239 | t | -1207959552 | -1073741825

  38. companies | 102240 | t | -1073741824 | -939524097

  39. companies | 102241 | t | -939524096 | -805306369

  40. companies | 102242 | t | -805306368 | -671088641

  41. companies | 102243 | t | -671088640 | -536870913

  42. companies | 102244 | t | -536870912 | -402653185

  43. companies | 102245 | t | -402653184 | -268435457

  44. companies | 102246 | t | -268435456 | -134217729

  45. companies | 102247 | t | -134217728 | -1

  46. companies | 102248 | t | 0 | 134217727

  47. companies | 102249 | t | 134217728 | 268435455

  48. companies | 102250 | t | 268435456 | 402653183

  49. companies | 102251 | t | 402653184 | 536870911

  50. companies | 102252 | t | 536870912 | 671088639

  51. companies | 102253 | t | 671088640 | 805306367

  52. companies | 102254 | t | 805306368 | 939524095

  53. companies | 102255 | t | 939524096 | 1073741823

  54. companies | 102256 | t | 1073741824 | 1207959551

  55. companies | 102257 | t | 1207959552 | 1342177279

  56. companies | 102258 | t | 1342177280 | 1476395007

  57. companies | 102259 | t | 1476395008 | 1610612735

  58. companies | 102260 | t | 1610612736 | 1744830463

  59. companies | 102261 | t | 1744830464 | 1879048191

  60. companies | 102262 | t | 1879048192 | 2013265919

  61. companies | 102263 | t | 2013265920 | 2147483647

  62. (32 rows)

以上为citus的简单部署及示例,先分享到这里,欢迎补充。


本文分享自微信公众号 - 开源软件联盟PostgreSQL分会(kaiyuanlianmeng)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部