citus：PostgresQL多租户案例

环境

• PostgresQL：PostgreSQL 13.3
• citus：Citus 10.0.3
• 操作系统：CentOS Linux release 7.6.1810 (Core) x86_64

安装PostgresQL和citus

• 安装PostgresQL：PostgreSQL介绍和centos7安装部署
• 安装citus：citus介绍和centos7安装部署和集群搭建
• 如果以上两步已进行，已经做好了安装和集群搭建，请忽略，直接进入下面的多租户场景测试
• 如果没有安装，请安装以上两步，安装postgres和搭建集群，并启动起来

创建Database

• 根据需要，每一个需要分库分表的database，都要创建extension

mydb=# create database test_db1;
NOTICE:  Citus partially supports CREATE DATABASE for distributed databases
DETAIL:  Citus does not propagate CREATE DATABASE command to workers
HINT:  You can manually create a database and its extensions on workers.
CREATE DATABASE
mydb=# 

• 在coordinator中执行创建Database命令时，只能在Coordinator一个节点中创建，因此还需要在每个Worker节点中进行创建。以下在每个worker节点分别创建数据库，并且需要在新创建的数据库中创建citus Extension。

create database mydb;
create extension citus;

设置协调节点和工作节点

• 每个需要分库分表的数据库，都要设置自己的协调节点和工作节点
• 只需要在协调节点执行

## 仅在协调节点执行
# 设置协调节点cn
 SELECT citus_set_coordinator_host('192.168.1.76');
 # 增加节点 
 SELECT * from master_add_node('192.168.1.73', 5432);
 SELECT * from master_add_node('192.168.1.74', 5432);
  # 查看所有节点
 select * from pg_dist_node;
 # 查看工作节点
 SELECT * FROM master_get_active_worker_nodes();
 
 ## 另外，下面提供删除、禁用、启用节点的方法
 # 删除节点 
 SELECT master_remove_node('192.168.1.74', '9700');
 # 禁用节点（可能会导致一部分数据查询不到了）
 SELECT master_disable_node('192.168.1.74', '5432');
 # 启用节点
 SELECT master_activate_node('192.168.1.74', 5432);
 

• Citus已经建议从基于语句的复制切换到流式复制，参数replication_model = ‘streaming’
• Citus还提供了两个参数以支持写入能力扩展及数据节点读写分离

use_secondary_node 开启use_secondary_node = always，读请求可以发往数据节点备节点。

writable_standby_coordinator
开启writable_standby_coordinator = on 这个参数的作用就是使CN-Standby也支持DML，比如Insert、Copy等操作

创建表

在协调节点创建表

• 直接使用官网给的示例

CREATE TABLE companies (
    id bigint NOT NULL,
    name text NOT NULL,
    image_url text,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);

CREATE TABLE campaigns (
    id bigint NOT NULL,
    company_id bigint NOT NULL,
    name text NOT NULL,
    cost_model text NOT NULL,
    state text NOT NULL,
    monthly_budget bigint,
    blacklisted_site_urls text[],
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);

CREATE TABLE ads (
    id bigint NOT NULL,
    company_id bigint NOT NULL,
    campaign_id bigint NOT NULL,
    name text NOT NULL,
    image_url text,
    target_url text,
    impressions_count bigint DEFAULT 0,
    clicks_count bigint DEFAULT 0,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);

ALTER TABLE companies ADD PRIMARY KEY (id);
ALTER TABLE campaigns ADD PRIMARY KEY (id, company_id);
ALTER TABLE ads ADD PRIMARY KEY (id, company_id);

• 准备数据，直接使用官方给的示例数据，可以下载到

curl https://examples.citusdata.com/tutorial/companies.csv > companies.csv
curl https://examples.citusdata.com/tutorial/campaigns.csv > campaigns.csv
curl https://examples.citusdata.com/tutorial/ads.csv > ads.csv

分片数量和副本数

分布式表有两个重要的配置参数：

• 分片数量 citus.shard_count
  指定分布式表要被切分成多少个Shard，默认配置为32个Shard;
  一般是建议配置为CPU总核数的2~4倍；
• 默认的，使用hash的分布方式，citus会创建32个shard，需要根据实际情况进行调整。citus有很多的配置选项，其中一个是配置shard总共的数量，建议配置为cpu核数×希望每个物理节点的shard数×物理节点数
• 副本数 citus.shard_replication_factor
  Citus还支持多副本存储Shard，并提供副本数量的配置参数；
  在多副本存储情况下，同一个shard的不同副本使用相同的shardid，pg_dist_placement表中针对每个副本都有一条记录；
• 这里不同于PostgreSQL的Stream Replication，是Citus提供的副本能力，当然也可以选择使用Stream Replication而不是Citus的副本功能，将此处的副本数配置为1；

创建分布表

• 不需要分库分表的table，不需要处理
• 对每个需要分片的表创建分布表，默认hash

-- before distributing tables, enable some extra features
SET citus.replication_model = 'streaming';

SELECT create_distributed_table('companies', 'id');
SELECT create_distributed_table('campaigns', 'company_id');
SELECT create_distributed_table('ads', 'company_id');

导入数据

• 作为PostgreSQL的一个扩展，Citus支持使用COPY命令批量加载。

\copy companies from 'companies.csv' with csv
\copy campaigns from 'campaigns.csv' with csv
\copy ads from 'ads.csv' with csv

测试

查询

• 在psql shell里，使用 \d+，可以查看表和大小，可以看到协调节点未存储数据
• 在协调节点，执行查询操作，不需要管分片情况，直接查询皆可以

select * from campaigns;

插入

• 会按照hash分散插入到某一节点，插入成功

INSERT INTO companies VALUES (5000, 'New Company', 'https://randomurl/image.png', now(), now());

更新

• 会找到对应分片对应表，更新数据，更新成功

UPDATE campaigns
SET monthly_budget = monthly_budget*2
WHERE company_id = 5;

group by

• 可以很好的分组

SELECT campaigns.id, campaigns.name, campaigns.monthly_budget,
       sum(impressions_count) as total_impressions, sum(clicks_count) as total_clicks
FROM ads, campaigns
WHERE ads.company_id = campaigns.company_id
AND campaigns.company_id = 5
AND campaigns.state = 'running'
GROUP BY campaigns.id, campaigns.name, campaigns.monthly_budget

排序

• 可以排序

SELECT name, cost_model, state, monthly_budget
FROM campaigns
WHERE company_id = 5
ORDER BY monthly_budget DESC
LIMIT 10;

参考

• 官方示例：多租户应用