postgresql高可用_Postgresql高可用实现方案

本文主要讲解一种Postgresql高可用实现方案。由于项目需要，我们需要在短时间实现底层Postgresql DB的高可用，而网络上大多数的解决方案比较复杂，需要深入了解Postgresql。

背景

我们原先只是使用Postgresql DB来存放一些kong的配置信息，作为单实例以k8s pod的形式进行部署使用。这样，在项目中postgresql DB就存在单点故障的隐患。为了解决这个问题，我们需要实现高可用的Postgresql。本文中，利用PostDock来实现master-slave架构的Postgresql高可用集群。

下面是实现后的架构图。

PostDock

PostDock项目的代码参见GitHub项目PostDock，该项目在云和docker环境中提供高可用和自愈作用的Postgresql集群。

项目中使用PostDock提供的k8s中的样例example2-single-statefulset并对其进行了简化。简化内容如下：

• 使用hostPath来替换样例中的PVC;
• 去除样例中的configMap和secret，将其直接配置在SatatefulSet和Deployment中;
• 将所有k8s资源创建在default namespace，不单独创建其他namespace;

简化后的PostDock样例包含2个service，一个具有3个Pod的Postgresql StatefulSet和一个具有2个Pod的Pgpool Deployment。postgresql service为headless service，对应一个postgresql statefuleset,其中包含3个Postgresql Pod，1个master DB,2个slave DB; pgpool service对应一个pgpool deploy，其中包含2个pgpool pod。

-Postgresql Service

apiVersion: v1
kind: Service
metadata:
  namespace: default
  name: postgresql-db-service
  labels:
    name: database
    system: postgresql
spec:
  clusterIP: None
  ports:
    - port: 5432
      targetPort: 5432
  selector:
    name: database
    system: postgresql

-Postgresql SatetfulSet

apiVersion: apps/v1
kind: StatefulSet
metadata:
  namespace: default
  name: postgresql-db-node
  labels:
    name: database
    system: postgresql
    app: postgresql
spec:
  replicas: 3
  serviceName: "postgresql-db-service"
  selector:
    matchLabels:
      name: database
      system: postgresql
  template:
    metadata:
      labels:
        name: database
        system: postgresql
    spec:
      affinity:
        podAntiAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
          - labelSelector:
              matchLabels:
                name: database
                system: postgresql
            namespaces:
            - default
            topologyKey: kubernetes.io/hostname
      containers:
        - name: db-node
          image: postdock/postgres:latest-postgres11-repmgr40
          livenessProbe:
            exec:
              command: ['bash', '-c', '/usr/local/bin/cluster/healthcheck/is_major_master.sh']
            initialDelaySeconds: 600
            timeoutSeconds: 10
            periodSeconds: 30
            successThreshold: 1
            failureThreshold: 3
          imagePullPolicy: Always
          resources:
            requests:
              memory: "100Mi"
              cpu: "100m"
          env:
            - name: MY_POD_NAME
              valueFrom:
                fieldRef:
                  fieldPath: metadata.name
            
            - name: "REPMGR_WAIT_POSTGRES_START_TIMEOUT"
              value: "600"
            - name: "REPLICATION_PRIMARY_HOST"
              value: "postgresql-db-node-0.postgresql-db-service"
            - name: "PARTNER_NODES"
              value: "postgresql-db-node-0.postgresql-db-service,postgresql-db-node-1.postgresql-db-service,postgresql-db-node-2.postgresql-db-service"
            - name: "NODE_NAME"
              value: "$(MY_POD_NAME)"
            - name: "CLUSTER_NODE_NETWORK_NAME"
              value: "$(MY_POD_NAME).postgresql-db-service"

            - name: "CONFIGS"
              value: "wal_keep_segments:250,shared_buffers:300MB,archive_command:'/bin/true'"

            - name: "POSTGRES_DB"
              value: "postgresql.postgresql"
            - name: "POSTGRES_USER"
              value: "wide"
            - name: "POSTGRES_PASSWORD"
              value: "pass"

            - name: "CLUSTER_NAME"
              value: "postgresql_cluster"
            - name: "REPLICATION_DB"
              value: "rain"
            - name: "REPLICATION_USER"
              value: "rain"
            - name: "REPLICATION_PASSWORD"
              value: "passwd"
          ports:
            - containerPort: 5432
          volumeMounts:
            - name: postgresql-data
              mountPath: /var/lib/postgresql/data
      volumes:
      - hostPath:
          path: /var/project_deps/postgresql-data
          typ