基于Zalando Operator实现PostgreSQL集群读写分离负载均衡求助

核心思路 #

通过统一入口Service + Pgbouncer标签化部署 + Kubernetes流量分流配置实现：

• 单一IP/域名作为数据库接入点
• 写请求自动路由至主节点Pgbouncer
• 读请求自动分发至副本节点Pgbouncer（支持副本间轮询负载均衡）

具体实现步骤 #

1. 调整PostgresCluster CR的Pgbouncer配置 #

为主节点和副本节点的Pgbouncer设置专属标签，方便后续Service精准识别：

apiVersion: acid.zalan.do/v1
kind: PostgresCluster
metadata:
  name: your-cluster-name
spec:
  # 集群基础配置（略）
  pgbouncer:
    enabled: true
    # 主节点Pgbouncer配置
    master:
      enabled: true
      labels:
        pgbouncer-role: master
    # 副本节点Pgbouncer配置
    replicas: 2 # 替换为你的副本数量
    labels:
      pgbouncer-role: replica
    config:
      server_round_robin: 1 # 开启副本间轮询负载均衡
      # 其他Pgbouncer参数（如连接池大小、超时等，按需配置）

2. 创建统一接入的Kubernetes Service #

通过端口区分读写流量，将统一Service的不同端口分别指向主/副本Pgbouncer：

apiVersion: v1
kind: Service
metadata:
  name: pgbouncer-unified-entry
spec:
  type: ClusterIP # 需外部访问可改为LoadBalancer
  ports:
    # 写请求端口：绑定主节点Pgbouncer
    - name: write
      port: 5432
      targetPort: 5432
      protocol: TCP
    # 读请求端口：绑定副本节点Pgbouncer
    - name: read
      port: 5433
      targetPort: 5432
      protocol: TCP
  selector:
    component: pgbouncer # 匹配所有Pgbouncer实例
---
# 主节点Pgbouncer端点绑定（自动维护Pod IP，可结合控制器动态更新）
apiVersion: discovery.k8s.io/v1
kind: EndpointSlice
metadata:
  name: pgbouncer-master-endpoints
  labels:
    kubernetes.io/service-name: pgbouncer-unified-entry
addressType: IPv4
ports:
  - name: write
    port: 5432
    protocol: TCP
endpoints:
  - addresses:
      - <master-pgbouncer-pod-ip>
    conditions:
      ready: true
    labels:
      pgbouncer-role: master
---
# 副本节点Pgbouncer端点绑定
apiVersion: discovery.k8s.io/v1
kind: EndpointSlice
metadata:
  name: pgbouncer-replica-endpoints
  labels:
    kubernetes.io/service-name: pgbouncer-unified-entry
addressType: IPv4
ports:
  - name: read
    port: 5433
    protocol: TCP
endpoints:
  - addresses:
      - <replica-pgbouncer-pod-ip-1>
      - <replica-pgbouncer-pod-ip-2>
    conditions:
      ready: true
    labels:
      pgbouncer-role: replica

3. 透明化接入优化（可选） #

若需完全单一端口接入，可在应用层或引入轻量反向代理（如Nginx）实现SQL语句级分流：

server {
  listen 5432;
  proxy_pass $upstream;

  set $upstream "pgbouncer-unified-entry:5432"; # 默认路由至写节点
  # 匹配读请求转发至副本节点
  if ($request_method ~* 'SELECT') {
    set $upstream "pgbouncer-unified-entry:5433";
  }
}

4. 有效性验证 #

• 连接统一入口5432端口执行写操作（如INSERT/UPDATE），检查主节点日志确认请求到达
• 连接5433端口执行读操作（如SELECT），检查副本节点日志确认请求轮询分发
• 模拟主节点故障，验证写请求是否自动切换至新主节点的Pgbouncer

内容的提问来源于stack exchange，提问作者Raghu Ram