使用 Citus 和 ClusterManager 实现生产级高可用分布式 PostgreSQL 分片集群
什么是分片集群
Sharded Cluster（分片集群）是一种实现数据库分片的集群架构。通过将大型数据库的数据分布在多台机器之间，可以实现水平扩展。这种方法通过在多个 PostgreSQL 实例之间分配表行来实现，从而提升了读写吞吐量，并能够通过数据分区实现数据隔离和合规性要求。
分片集群的实现原理
分片集群由一个称为协调器的 ClusterManager 和多个称为分片的 DataNodes 组成。协调器负责管理分片实例，并通过路由请求到适当的分片来实现透明的数据分片。Citus 作为分布式数据库扩展，提供了实现分片集群的核心功能。
创建基本的分布式集群
以下是一个使用 Kubernetes 创建分布式集群的示例配置：
apiVersion: database.stack/v1alpha1
kind: DistributedCluster
metadata:
name: my-distributed-cluster
spec:
type: citus
database: mydb
coordinator:
replicas: 2
resources:
requests:
memory: "8Gi"
cpu: "4"
config:
pgVersion: "15"
shards:
count: 4
replicasPerShard: 2
resources:
requests:
memory: "8Gi"
cpu: "4"
config:
pgVersion: "15"
此配置创建了一个包含 2 个协调器实例和 4 个分片（每个分片包含 2 个副本）的分布式集群。所有 Pod 启动后，可以通过以下命令查看集群状态：
kubectl exec -n my-cluster cluster-coord-0 -c clustermanager -- cmctl list
+ Cluster: my-distributed-cluster --+------------------+--------------+---------+----+-----------+
| Group | Member | Host | Role | State | TL | Lag in MB |
+-------+------------------+------------------+--------------+---------+----+-----------+
| 0 | cluster-coord-0 | 10.244.0.16:7433 | Leader | running | 1 | |
| 0 | cluster-coord-1 | 10.244.0.34:7433 | Follower | running | 1 | 0 |
| 1 | cluster-shard0-0 | 10.244.0.19:7433 | Leader | running | 1 | |
| 1 | cluster-shard0-1 | 10.244.0.48:7433 | Replica | running | 1 | 0 |
| 2 | cluster-shard1-0 | 10.244.0.20:7433 | Leader | running | 1 | |
| 2 | cluster-shard1-1 | 10.244.0.42:7433 | Replica | running | 1 | 0 |
| 3 | cluster-shard2-0 | 10.244.0.22:7433 | Leader | running | 1 | |
| 3 | cluster-shard2-1 | 10.244.0.43:7433 | Replica | running | 1 | 0 |
| 4 | cluster-shard3-0 | 10.244.0.27:7433 | Leader | running | 1 | |
| 4 | cluster-shard3-1 | 10.244.0.45:7433 | Replica | running | 1 | 0 |
+-------+------------------+------------------+--------------+---------+----+-----------+
自定义生产级集群配置
自定义配置
您可以通过以下步骤自定义分布式集群：
- 定义资源配额：
apiVersion: resource.stack/v1
kind: ResourceProfile
metadata:
name: small-instance
spec:
limits:
cpu: 4
memory: 8Gi
- 配置数据库参数：
apiVersion: database.stack/v1
kind: DBConfig
metadata:
name: pgconfig1
spec:
version: "11"
settings:
password_encryption: 'scram-sha-256'
random_page_cost: '1.5'
shared_buffers: '256MB'
wal_compression: 'on'
- 配置连接池：
apiVersion: database.stack/v1
kind: PoolConfig
metadata:
name: poolconfig1
spec:
max_connections: 2000
default_pool_size: 50
databases:
foodb:
max_connections: 1000
pool_size: 20
dbname: 'bardb'
reserve_pool: 5
users:
user1:
pool_mode: transaction
max_connections: 50
user2:
pool_mode: session
max_connections: '100'
- 配置对象存储：
apiVersion: storage.stack/v1beta1
kind: ObjectStorage
metadata:
name: backupconfig1
spec:
type: s3Compatible
s3:
bucket: stackgres
region: k8s
endpoint: http://my-cluster-minio:9000
credentials:
accessKey:
secretKeyRef:
name: my-cluster-minio
key: accesskey
secretAccessKey:
secretKeyRef:
name: my-cluster-minio
key: secretkey
- 配置分布式日志：
apiVersion: monitoring.stack/v1
kind: DistributedLog
metadata:
name: distributedlogs
spec:
storage:
size: 10Gi
创建分布式集群
完整的分布式集群配置如下：
apiVersion: database.stack/v1alpha1
kind: DistributedCluster
metadata:
namespace: my-cluster
name: my-distributed-cluster
spec:
type: citus
database: mydb
coordinator:
replicas: 2
profile: small-instance
config:
dbConfig: pgconfig1
poolConfig: poolconfig1
scripts:
- name: init-database
fromSecret: pgbench-user-password-secret
shards:
count: 3
replicasPerShard: 2
profile: small-instance
config:
dbConfig: pgconfig1
poolConfig: poolconfig1
backups:
- storage: backupconfig1
schedule: '*/5 * * * *'
retention: 6
logs:
distributedLog: distributedlogs
monitoring:
prometheus: true
脚本示例：
apiVersion: database.stack/v1
kind: DatabaseScript
metadata:
name: cluster-scripts
spec:
scripts:
- name: create-user
fromSecret: pgbench-user-password-secret
path: pgbench-create-user-sql
- name: create-tables
database: mydb
user: pgbench
script: |
CREATE TABLE pgbench_accounts (
id integer NOT NULL,
bid integer,
balance integer,
filler character(84)
);
- name: distribute-tables
database: mydb
user: pgbench
script: |
SELECT create_distributed_table('pgbench_history', 'id');
