pgo体验

发表于 | 分类于

官网

https://github.com/CrunchyData/postgres-operator-examples

https://access.crunchydata.com/documentation/postgres-operator/v5/

一些基本修改

1
cd postgres-operator-examples

指定 namespace 为 postgres-operator

kubectl apply -k kustomize/install/namespace
创建 namespace

1
2
3
4
apiVersion: v1
kind: Namespace
metadata:
  name: postgres-operator

kubectl apply --server-side -k kustomize/install/default

一般只用修改镜像，没什么大的调整

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
namespace: postgres-operator

commonLabels:
  app.kubernetes.io/name: pgo
  # The version below should match the version on the PostgresCluster CRD
  app.kubernetes.io/version: 5.3.0

bases:
- ../crd
- ../rbac/cluster
- ../manager

images:
- name: postgres-operator
  newName: imwl/postgres-operator
  newTag: ubi8-5.3.0-0
- name: postgres-operator-upgrade
  newName: imwl/postgres-operator-upgrade
  newTag: ubi8-5.3.0-0

patchesJson6902:
- target: { group: apps, version: v1, kind: Deployment, name: pgo }
  path: selectors.yaml
- target: { group: apps, version: v1, kind: Deployment, name: pgo-upgrade }
  path: selectors.yaml

安装 pg-cluster 高可用

主要修改的文件，按需修改。存储使用 rook-ceph 搭建的 rook-ceph-block-retain

kubectl apply -k kustomize/high-availability

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: imwl
#  annotations:
#    postgres-operator.crunchydata.com/pgbackrest-ip-version: IPv6
spec:
  users:
    - name: postgres
    - name: hive
      databases:
        - hive
    - name: imwl   # 创建超级管理员用户
      options: "SUPERUSER"
        - imwl
  service:
    metadata:
      annotations:
        my-annotation: value1
      labels:
        my-label: value2
    type: NodePort
    nodePort: 31432
  image: imwl/crunchy-postgres:ubi8-13.9-2
  postgresVersion: 13
  instances:
    - name: pgha1
      replicas: 2
      resources:
        limits:
          cpu: 8.0
          memory: 16Gi
      dataVolumeClaimSpec:
        storageClassName: "rook-ceph-block-retain"
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 20Gi
      walVolumeClaimSpec:
        storageClassName: "rook-ceph-block-retain"
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 10Gi
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 1
            podAffinityTerm:
              topologyKey: kubernetes.io/hostname
              labelSelector:
                matchLabels:
                  postgres-operator.crunchydata.com/cluster: imwl
                  postgres-operator.crunchydata.com/instance-set: pgha1
  backups:    
    pgbackrest:
      #manual:     # 一次性备份
      #  repoName: repo1
      #  options:
      #   - --type=full
      global:
        repo1-retention-full: "14"
        repo1-retention-full-type: time
        repo1-retention-full: "14"
        repo1-retention-full-type: time        
        repo2-path: /pgbackrest/postgres-operator/hippo-multi-repo/repo2
      image: imwl/crunchy-pgbackrest:ubi8-2.41-2
      repos:
      - name: repo1
        schedules:
          full: "0 2 * * 0"
          differential: "0 5 * * 1-6"
        volume:
          volumeClaimSpec:
            storageClassName: "rook-ceph-block-retain"
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 40Gi
      - name: repo2
        schedules:
          full: "0 1 * * 0"
          differential: "0 1 * * 1-6"
        volume:
          volumeClaimSpec:
            storageClassName: "loacl-path"
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 40Gi
                
  proxy:
    pgBouncer:
      config:
        global:
          pool_mode: session
          auth_file: /etc/pgbouncer/users.txt
        files:  # 需要动手创建 cm , 添加账号密码
          - configMap:
              name: pgbouncer-users
              items:
                - key: users.txt
                  path: users.txt
      affinity:
      image: imwl/crunchy-pgbouncer:ubi8-1.17-5
      replicas: 2
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 1
            podAffinityTerm:
              topologyKey: kubernetes.io/hostname
              labelSelector:
                matchLabels:
                  postgres-operator.crunchydata.com/cluster: imwl
                  postgres-operator.crunchydata.com/role: pgbouncer

  patroni:
    switchover:   # 允许主备切换，官方不建议启用，只建议手动切换，切换时开启，切换完关闭
      enabled: true
    dynamicConfiguration:
      synchronous_mode: true
      synchronous_mode_strict: true
      postgresql:
        pg_hba:
          - "host all all 0.0.0.0/0 md5"
          # - "hostnossl all all all md5"
        parameters:
          max_connections: 2000
          log_timezone: 'Asia/Shanghai'
          timezone: 'Asia/Shanghai'
          max_parallel_workers: 16
          max_worker_processes: 16
          shared_buffers: 8GB
          work_mem: 64MB

  # 使用 pgadmin
  userInterface:   
    pgAdmin:
      image: registry.test.datacanvas.com:55443/test/crunchy-pgadmin4:ubi8-4.30-13
      dataVolumeClaimSpec:
        storageClassName: "rook-ceph-block-retain"
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi

  #  开启 export 监控
  monitoring:
    pgmonitor:
      exporter:
        image: registry.test.datacanvas.com:55443/kube-package/crunchy-postgres-exporter:ubi8-5.4.0-0

因为 超级用户 不能访问 pgbouncer

pgbouncer-users-configmap.yaml

1
2
3
4
5
6
7
8
9
10
apiVersion: v1
kind: ConfigMap
metadata:
  namespace: postgres-operator
  name: pgbouncer-users
data:
  users.txt: |
    "imwl"  "password"
    "postgres"  "Password!"
    "hive"   "passWord!"

手动主备切换，当允许时

1
2
3
kubectl annotate -n postgres-operator postgrescluster hippo imwl postgres-operator.crunchydata.com/trigger-switchover="$(date)"

kubectl annotate -n postgres-operator postgrescluster hippo imwl postgres-operator.crunchydata.com/trigger-switchover="$(date)" --overwrite

创建完后，修改账号密码 集群名-pguser-用户名,测试全部密码 password

1
2
3
4
5
kubectl patch secret -n postgres-operator imwl-pguser-hive -p '{"stringData":{"password":"password","verifier":""}}'

kubectl patch secret -n postgres-operator imwl-pguser-imwl -p '{"stringData":{"password":"password","verifier":""}}'

kubectl patch secret -n postgres-operator imwl-pguser-postgres -p '{"stringData":{"password":"password","verifier":""}}'

数据库连接方式

  1. ssh 中转后 imwl-ha.postgres-operator.svc.cluster.local:5432 imwl/password
  2. ip:31432

更多信息参考官网和github