Post

Kubernetes - postgresql-ha 배포해 보기

Kubernetes에서 Postgres DB를 HA 구성 해주는 postgresql-ha를 배포해 보았다.

Posted Updated
By Dor1
views 22 min read
Kubernetes - postgresql-ha 배포해 보기

Kubernetes v1.24.7 / Helm 3.9.0 / postgresql-ha(bitnami) 11.1.5 / Ubuntu 22.04 LTS

Kubernetes

CLI

개요

  • 기존에 postgres-operator로 진행하려 했지만 기능도 너무 많고 다루기가 어려워, 간단하게 HA(High Availability)로 구성되어있는 항목 중 Bitnami 의 chart를 발견하여 진행하게 되었다.
    (사실 넘어오게 된 가장 큰 이유 중 하나는 구체적인 postgres의 DB설정에 직접적으로 접근하기가 힘들기 때문)
  • postgres-operator에 비해서 postgresql-ha는 patroni와 같은 기능은 없고, 단순하게 pgpool을 이용한다.

Bitnami의 PostgreSQL HA Architecture Bitnami의 PostgreSQL HA Architecture

Deploy via helm

TL;DR 항목으로 보면 간단하게 두 줄로 이뤄져 있지만 막상 진행해보면 pending 상태로 진행이 안된다.
관리자 입장에 맞춰 설정 변경을 하여 진행 하는 것이 좋다.
https://github.com/bitnami/charts/tree/main/bitnami/postgresql-ha

1. Add helm repo

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# add helm repo
dor1@is-master ~ > helm repo add bitnami https://charts.bitnami.com/bitnami
"bitnami" has been added to your repositories

# update helm repo (recommend)
dor1@is-master ~ > helm repo update
Hang tight while we grab the latest from your chart repositories...
...Successfully got an update from the "bitnami" chart repository
Update Complete. ⎈Happy Helming!⎈

# check helm repo
dor1@is-master ~ > helm repo ls
NAME    URL
bitnami https://charts.bitnami.com/bitnami

2. vaules.yaml를 통한 설정 변경

helm chart는 기본적으로 values.yaml을 바라보고 그 안에는 parameters가 존재한다.
parameters들은 pod의 env에 맞춰져 있으며 대다수 official manual에 요구하는 정의가 적혀 있다.

1
2
3
4
5
6

# vaules.yaml
dor1@is-master ~ > mkdir postgresql-ha
mkdir: created directory 'postgresql-ha'

dor1@is-master ~ > cd postgresql-ha
dor1@is-master ~/postgresql-ha ❯ helm show values bitnami/postgresql-ha > values.yaml

values.yaml에는 엄청 많은 parameters와 함께 주석이 들어가 있기 때문에 주석을 제거 해준 뒤 필요한 parameter만 정의해준다.
vim으로 연 뒤에 vim 명령 정리 항목을 따라 주석 제거를 해준다.
vim으로 주석 제거 후 아래 항목에서 필요한 설정을 진행한다. (parameter가 너무 많다 보니 필요 부분 외 모두 주석 처리)
witness항목은 필요 시에 true로 설정하여 만들어준다.
또한 rook-ceph을 사용한다면 persistence 부분의 storageClass를 변경해준다.

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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566

# values.yaml
dor1@is-master ~/postgresql-ha ❯ vi ./values.yaml
#global:
#  imageRegistry: ""
#  imagePullSecrets: []
#  storageClass: ""
#  postgresql:
#    username: ""
#    password: ""
#    database: ""
#    repmgrUsername: ""
#    repmgrPassword: ""
#    repmgrDatabase: ""
#    existingSecret: ""
#  ldap:
#    bindpw: ""
#    existingSecret: ""
#  pgpool:
#    adminUsername: ""
#    adminPassword: ""
#    existingSecret: ""

#kubeVersion: ""
#nameOverride: ""
#fullnameOverride: ""
#namespaceOverride: ""
#commonLabels: {}
#commonAnnotations: {}
clusterDomain: cluster.local
#extraDeploy: []
diagnosticMode:
  enabled: false
  command:
    - sleep
  args:
    - infinity

postgresql:
  image:
    registry: docker.io
    repository: bitnami/postgresql-repmgr
    tag: 15.2.0-debian-11-r12
    digest: ""
    pullPolicy: IfNotPresent
    pullSecrets: []
    # change that false -> true for looging
    debug: true
#  labels: {}
#  podLabels: {}
#  serviceAnnotations: {}
  replicaCount: 3
  updateStrategy:
    type: RollingUpdate
  containerPorts:
    postgresql: 5432
#  hostAliases: []
  hostNetwork: false
  hostIPC: false
#  podAnnotations: {}
#  podAffinityPreset: ""
  podAntiAffinityPreset: soft
#  nodeAffinityPreset:
#    type: ""
#    key: ""
#    values: []
#  affinity: {}
#  nodeSelector: {}
#  tolerations: []
#  topologySpreadConstraints: []
#  priorityClassName: ""
#  schedulerName: ""
#  terminationGracePeriodSeconds: ""
  podSecurityContext:
    enabled: true
    fsGroup: 1001
  containerSecurityContext:
    enabled: true
    runAsUser: 1001
    runAsNonRoot: true
    readOnlyRootFilesystem: false
#  command: []
#  args: []
#  lifecycleHooks: {}
#  extraEnvVars: []
#  extraEnvVarsCM: ""
#  extraEnvVarsSecret: ""
#  extraVolumes: []
#  extraVolumeMounts: []
#  initContainers: []
#  sidecars: []
#  resources:
#    limits: {}
#    requests: {}
  livenessProbe:
    enabled: true
    initialDelaySeconds: 30
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 6
  readinessProbe:
    enabled: true
    initialDelaySeconds: 5
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 6
  startupProbe:
    enabled: false
    initialDelaySeconds: 5
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 10
#  customLivenessProbe: {}
#  customReadinessProbe: {}
#  customStartupProbe: {}
  pdb:
    create: false
    minAvailable: 1
    maxUnavailable: ""
  ## change userid (postgres -> ???)
  username: deepadmin
  password: ""
  ## add DB name match userid
  database: deepadmin
  ## add secrets and then create secrets file
  existingSecret: postgresql-secrets
  postgresPassword: ""
#  usePasswordFile: ""
#  repmgrUsePassfile: ""
#  repmgrPassfilePath: ""
  upgradeRepmgrExtension: false
  pgHbaTrustAll: false
  syncReplication: false
  repmgrUsername: repmgr
  repmgrPassword: ""
  repmgrDatabase: repmgr
  repmgrLogLevel: NOTICE
  repmgrConnectTimeout: 5
  repmgrReconnectAttempts: 2
  repmgrReconnectInterval: 3
  repmgrFenceOldPrimary: false
  repmgrChildNodesCheckInterval: 5
  repmgrChildNodesConnectedMinCount: 1
  repmgrChildNodesDisconnectTimeout: 30
  usePgRewind: false
  audit:
    logHostname: true
    logConnections: false
    logDisconnections: false
    pgAuditLog: ""
    pgAuditLogCatalog: "off"
    clientMinMessages: error
    logLinePrefix: ""
    ## setting TZ
    logTimezone: Asia/Seoul
  sharedPreloadLibraries: "pgaudit, repmgr"
#  maxConnections: ""
#  postgresConnectionLimit: ""
#  dbUserConnectionLimit: ""
#  tcpKeepalivesInterval: ""
#  tcpKeepalivesIdle: ""
#  tcpKeepalivesCount: ""
#  statementTimeout: ""
#  pghbaRemoveFilters: ""
#  extraInitContainers: []
#  repmgrConfiguration: ""
#  configuration: ""
#  pgHbaConfiguration: ""
#  configurationCM: ""
#  extendedConf: ""
#  extendedConfCM: ""
#  initdbScripts: {}
#  initdbScriptsCM: ""
#  initdbScriptsSecret: ""
  tls:
    enabled: false
    preferServerCiphers: true
    certificatesSecret: ""
    certFilename: ""
    certKeyFilename: ""

witness:
  ## if use change that false -> true
  create: true
#  labels: {}
#  podLabels: {}
  replicaCount: 1
  updateStrategy:
    type: RollingUpdate
  containerPorts:
    postgresql: 5432
#  hostAliases: []
  hostNetwork: false
  hostIPC: false
#  podAnnotations: {}
#  podAffinityPreset: ""
  podAntiAffinityPreset: soft
#  nodeAffinityPreset:
#    type: ""
#    key: ""
#    values: []
#  affinity: {}
#  nodeSelector: {}
#  tolerations: []
#  topologySpreadConstraints: []
#  priorityClassName: ""
#  schedulerName: ""
#  terminationGracePeriodSeconds: ""
  podSecurityContext:
    enabled: true
    fsGroup: 1001
  containerSecurityContext:
    enabled: true
    runAsUser: 1001
    runAsNonRoot: true
    readOnlyRootFilesystem: false
#  command: []
#  args: []
#  lifecycleHooks: {}
#  extraEnvVars: []
#  extraEnvVarsCM: ""
#  extraEnvVarsSecret: ""
#  extraVolumes: []
#  extraVolumeMounts: []
#  initContainers: []
#  sidecars: []
#  resources:
#    limits: {}
#    requests: {}
  livenessProbe:
    enabled: true
    initialDelaySeconds: 30
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 6
  readinessProbe:
    enabled: true
    initialDelaySeconds: 5
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 6
  startupProbe:
    enabled: false
    initialDelaySeconds: 5
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 10
#  customLivenessProbe: {}
#  customReadinessProbe: {}
#  customStartupProbe: {}
  pdb:
    create: false
    minAvailable: 1
    maxUnavailable: ""
  upgradeRepmgrExtension: false
  pgHbaTrustAll: false
  repmgrLogLevel: NOTICE
  repmgrConnectTimeout: 5
  repmgrReconnectAttempts: 2
  repmgrReconnectInterval: 3
  audit:
    logHostname: true
    logConnections: false
    logDisconnections: false
    pgAuditLog: ""
    pgAuditLogCatalog: "off"
    clientMinMessages: error
    logLinePrefix: ""
    ## setting TZ
    logTimezone: Asia/Seoul
#  maxConnections: ""
#  postgresConnectionLimit: ""
#  dbUserConnectionLimit: ""
#  tcpKeepalivesInterval: ""
#  tcpKeepalivesIdle: ""
#  tcpKeepalivesCount: ""
#  statementTimeout: ""
#  pghbaRemoveFilters: ""
#  extraInitContainers: []
#  repmgrConfiguration: ""
#  configuration: ""
#  pgHbaConfiguration: ""
#  configurationCM: ""
#  extendedConf: ""
#  extendedConfCM: ""
#  initdbScripts: {}
#  initdbScriptsCM: ""
#  initdbScriptsSecret: ""

pgpool:
  image:
    registry: docker.io
    repository: bitnami/pgpool
    tag: 4.4.2-debian-11-r15
    digest: ""
    pullPolicy: IfNotPresent
    pullSecrets: []
    ## change that false -> true for looging
    debug: false
  customUsers: {}
  usernames: ""
  passwords: ""
#  hostAliases: []
  ## add secrets and then create secrets file
  customUsersSecret: pgpool-secrets
#  existingSecret: ""
  srCheckDatabase: postgres
#  labels: {}
#  podLabels: {}
#  serviceLabels: {}
#  serviceAnnotations: {}
#  customLivenessProbe: {}
#  customReadinessProbe: {}
#  customStartupProbe: {}
#  command: []
#  args: []
#  lifecycleHooks: {}
#  extraEnvVars: []
#  extraEnvVarsCM: ""
#  extraEnvVarsSecret: ""
#  extraVolumes: []
#  extraVolumeMounts: []
#  initContainers: []
#  sidecars: []
  replicaCount: 1
#  podAnnotations: {}
#  priorityClassName: ""
#  schedulerName: ""
#  terminationGracePeriodSeconds: ""
#  topologySpreadConstraints: []
#  podAffinityPreset: ""
  podAntiAffinityPreset: soft
#  nodeAffinityPreset:
#    type: ""
#    key: ""
#    values: []
#  affinity: {}
#  nodeSelector: {}
#  tolerations: []
  podSecurityContext:
    enabled: true
    fsGroup: 1001
  containerSecurityContext:
    enabled: true
    runAsUser: 1001
    runAsNonRoot: true
    readOnlyRootFilesystem: false
#  resources:
#    limits: {}
#    requests: {}
  livenessProbe:
    enabled: true
    initialDelaySeconds: 30
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 5
  readinessProbe:
    enabled: true
    initialDelaySeconds: 5
    periodSeconds: 5
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 5
  startupProbe:
    enabled: false
    initialDelaySeconds: 5
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 10
  pdb:
    create: false
    minAvailable: 1
    maxUnavailable: ""
#  updateStrategy: {}
  containerPorts:
    postgresql: 5432
#  minReadySeconds: ""
  adminUsername: admin
  adminPassword: ""
#  usePasswordFile: ""
  authenticationMethod: scram-sha-256
  logConnections: false
  logHostname: true
  logPerNodeStatement: false
  logLinePrefix: ""
  clientMinMessages: error
#  numInitChildren: ""
  reservedConnections: 1
#  maxPool: ""
#  childMaxConnections: ""
#  childLifeTime: ""
#  clientIdleLimit: ""
#  connectionLifeTime: ""
  useLoadBalancing: true
  loadBalancingOnWrite: transaction
#  configuration: ""
#  configurationCM: ""
#  initdbScripts: {}
#  initdbScriptsCM: ""
#  initdbScriptsSecret: ""
  tls:
    enabled: false
    autoGenerated: false
    preferServerCiphers: true
    certificatesSecret: ""
    certFilename: ""
    certKeyFilename: ""
    certCAFilename: ""

ldap:
  enabled: false
  existingSecret: ""
  uri: ""
  basedn: ""
  binddn: ""
  bindpw: ""
  bslookup: ""
  scope: ""
  tlsReqcert: ""
  nssInitgroupsIgnoreusers: root,nslcd

rbac:
  create: false
  rules: []

serviceAccount:
  create: false
  name: ""
  annotations: {}
  automountServiceAccountToken: true

psp:
  create: false

metrics:
  enabled: false
  image:
    registry: docker.io
    repository: bitnami/postgres-exporter
    tag: 0.11.1-debian-11-r69
    digest: ""
    pullPolicy: IfNotPresent
    pullSecrets: []
    debug: false
  podSecurityContext:
    enabled: true
    runAsUser: 1001
#  resources:
#    limits: {}
#    requests: {}
  containerPorts:
    http: 9187
  livenessProbe:
    enabled: true
    initialDelaySeconds: 30
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 6
  readinessProbe:
    enabled: true
    initialDelaySeconds: 5
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 6
  startupProbe:
    enabled: false
    initialDelaySeconds: 5
    periodSeconds: 10
    timeoutSeconds: 5
    successThreshold: 1
    failureThreshold: 10
#  customLivenessProbe: {}
#  customReadinessProbe: {}
#  customStartupProbe: {}
  service:
    type: ClusterIP
    ports:
      metrics: 9187
    nodePorts:
      metrics: ""
    clusterIP: ""
    loadBalancerIP: ""
    loadBalancerSourceRanges: []
    externalTrafficPolicy: Cluster
  annotations:
    prometheus.io/scrape: "true"
    prometheus.io/port: "9187"
#  customMetrics: {}
#  extraEnvVars: []
#  extraEnvVarsCM: ""
#  extraEnvVarsSecret: ""
  serviceMonitor:
    enabled: false
    namespace: ""
    interval: ""
    scrapeTimeout: ""
    annotations: {}
    labels: {}
    selector:
      prometheus: kube-prometheus
    relabelings: []
    metricRelabelings: []
    honorLabels: false
    jobLabel: ""

volumePermissions:
  enabled: false
  image:
    registry: docker.io
    repository: bitnami/bitnami-shell
    tag: 11-debian-11-r96
    digest: ""
    pullPolicy: IfNotPresent
    pullSecrets: []
  podSecurityContext:
    runAsUser: 0
#  resources:
#    limits: {}
#    requests: {}

persistence:
  enabled: true
  existingClaim: ""
  ## if use ceph storge, input storageclass
  storageClass: rook-cephfs
  mountPath: /bitnami/postgresql
  accessModes:
    - ReadWriteMany
  ## 8Gi -> 300Gi
  size: 300Gi
  annotations: {}
  labels: {}
  selector: {}

service:
  type: ClusterIP
  ports:
    postgresql: 5432
  portName: postgresql
  nodePorts:
    postgresql: ""
  loadBalancerIP: ""
  loadBalancerSourceRanges: []
  clusterIP: ""
  externalTrafficPolicy: Cluster
  extraPorts: []
  sessionAffinity: "None"
  sessionAffinityConfig: {}
  annotations: {}
  serviceLabels: {}

networkPolicy:
  enabled: false
  allowExternal: true
  egressRules:
    denyConnectionsToExternal: false
    customRules: []

위와 같이 설정하였다면 중간에 secret 들어가는 항목에 맞춰 password들을 secret.yaml 파일을 만들어 넣어준다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

dor1@is-master ~/postgresql-ha ❯ vi postgresql-secrets.yaml
apiVersion: v1
kind: Secret
metadata:
  name: postgresql-secrets
  namespace: postgresql-ha
type: Opaque
data:
  # passwrod for postgres "postgres" passsword
  postgres-password: "VGNHVlNSdDRKQA=="
  # password for postgres "user" password
  password: "YW9vbmkzNjUhQA=="
  # repmgr password
  repmgr-password: "VGNHVlNSdDRKQA=="
  # pgpool admin password
  admin-password: "VGNHVlNSdDRKQA=="
  # pgpool custom username & password
  usernames: "ZGVlcGFkbWlu"
  passwords: "YW9vbmkzNjUhQA=="

1
2
3
4
5
6
7
8
9
10
11
12
13

dor1@is-master ~/postgresql-ha ❯ vi pgpool-secrets.yaml
apiVersion: v1
kind: Secret
metadata:
  name: pgpool-secrets
  namespace: postgresql-ha
type: Opaque
data:
  # pgpool admin password
  admin-password: "VGNHVlNSdDRKQA=="
  # pgpool custom username & password
  usernames: "ZGVlcGFkbWlu"
  passwords: "YW9vbmkzNjUhQA=="

여기까지 다 되었다면 deployment 준비가 다 됐다.

3. Deployment

순차적으로 진행한다. (namespace → secrets → helm install)

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

# namespace
dor1@is-master ~/postgresql-ha ❯ kubectl create ns postgresql-ha
namespace/postgresql-ha created

# secrets
dor1@is-master ~/postgresql-ha ❯ kubectl apply -f ./postgresql-secrets.yaml -f ./pgpool-secrets.yaml
secret/postgresql-secrets created
secret/pgpool-secrets created

# helm install
dor1@is-master ~/postgresql-ha ❯ helm install postgresql-ha bitnami/postgresql-ha -n postgresql-ha -f ./values.yaml
NAME: postgresql-ha
LAST DEPLOYED: Wed Mar 15 16:06:02 2023
NAMESPACE: postgresql-ha
STATUS: deployed
REVISION: 1
TEST SUITE: None
NOTES:
CHART NAME: postgresql-ha
CHART VERSION: 11.1.5
APP VERSION: 15.2.0
** Please be patient while the chart is being deployed **
PostgreSQL can be accessed through Pgpool via port 5432 on the following DNS name from within your cluster:

    postgresql-ha-pgpool.postgresql-ha.svc.cluster.local

Pgpool acts as a load balancer for PostgreSQL and forward read/write connections to the primary node while read-only connections are forwarded to standby nodes.

To get the password for "deepadmin" run:

    export POSTGRES_PASSWORD=$(kubectl get secret --namespace postgresql-ha postgresql-secrets -o jsonpath="{.data.password}" | base64 -d)

To get the password for "repmgr" run:

    export REPMGR_PASSWORD=$(kubectl get secret --namespace postgresql-ha postgresql-secrets -o jsonpath="{.data.repmgr-password}" | base64 -d)

To connect to your database run the following command:

    kubectl run postgresql-ha-client --rm --tty -i --restart='Never' --namespace postgresql-ha --image docker.io/bitnami/postgresql-repmgr:15.2.0-debian-11-r12 --env="PGPASSWORD=$POSTGRES_PASSWORD"  \
        --command -- psql -h postgresql-ha-pgpool -p 5432 -U deepadmin -d deepadmin

To connect to your database from outside the cluster execute the following commands:

    kubectl port-forward --namespace postgresql-ha svc/postgresql-ha-pgpool 5432:5432 &
    psql -h 127.0.0.1 -p 5432 -U deepadmin -d deepadmin

# 확인
dor1@is-master ~/postgresql-ha ❯ helm ls -A
NAME            NAMESPACE       REVISION        UPDATED                                 STATUS          CHART                   APP VERSION
postgresql-ha   postgresql-ha   1               2023-03-15 16:06:02.432037109 +0900 KST deployed        postgresql-ha-11.1.5    15.2.0

dor1@is-master ~/postgresql-ha ❯ kubectl get po,deployments,svc,cm,secrets,pv,pvc -n postgresql-ha
NAME                                       READY   STATUS    RESTARTS      AGE
pod/postgresql-ha-pgpool-779444dbf-9b5w5   1/1     Running   0             3m41s
pod/postgresql-ha-postgresql-0             1/1     Running   0             3m41s
pod/postgresql-ha-postgresql-1             1/1     Running   0             3m40s
pod/postgresql-ha-postgresql-2             1/1     Running   2 (60s ago)   3m40s
pod/postgresql-ha-postgresql-witness-0     1/1     Running   0             3m41s

NAME                                   READY   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/postgresql-ha-pgpool   1/1     1            1           3m41s

NAME                                        TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGE
service/postgresql-ha-pgpool                ClusterIP   10.233.41.96    <none>        5432/TCP   3m41s
service/postgresql-ha-postgresql            ClusterIP   10.233.15.213   <none>        5432/TCP   3m41s
service/postgresql-ha-postgresql-headless   ClusterIP   None            <none>        5432/TCP   3m41s
service/postgresql-ha-postgresql-witness    ClusterIP   None            <none>        5432/TCP   3m41s

NAME                                               DATA   AGE
configmap/kube-root-ca.crt                         1      3m50s
configmap/postgresql-ha-postgresql-hooks-scripts   1      3m41s

NAME                                         TYPE                 DATA   AGE
secret/pgpool-secrets                        Opaque               3      3m49s
secret/postgresql-ha-pgpool                  Opaque               1      3m41s
secret/postgresql-secrets                    Opaque               6      3m49s
secret/sh.helm.release.v1.postgresql-ha.v1   helm.sh/release.v1   1      3m41s

NAME                                                        CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   CLAIM                                                   STORAGECLASS   REASON   AGE
persistentvolume/pvc-7edb7e28-7504-4dac-9e7d-27456d5392e7   300Gi      RWO            Retain           Bound    postgresql-ha/data-postgresql-ha-postgresql-2           rook-cephfs             3m39s
persistentvolume/pvc-a69ecfa5-db7a-4f10-94c6-f946f98a9295   300Gi      RWO            Retain           Bound    postgresql-ha/data-postgresql-ha-postgresql-0           rook-cephfs             3m40s
persistentvolume/pvc-ae656754-99eb-4249-83b0-9961f95540d9   300Gi      RWO            Retain           Bound    postgresql-ha/data-postgresql-ha-postgresql-1           rook-cephfs             3m39s
persistentvolume/pvc-ea605028-15e4-46d1-9f7a-d47714d94887   300Gi      RWO            Retain           Bound    postgresql-ha/data-postgresql-ha-postgresql-witness-0   rook-cephfs             3m40s

NAME                                                            STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   AGE
persistentvolumeclaim/data-postgresql-ha-postgresql-0           Bound    pvc-a69ecfa5-db7a-4f10-94c6-f946f98a9295   300Gi      RWO            rook-cephfs    3m41s
persistentvolumeclaim/data-postgresql-ha-postgresql-1           Bound    pvc-ae656754-99eb-4249-83b0-9961f95540d9   300Gi      RWO            rook-cephfs    3m40s
persistentvolumeclaim/data-postgresql-ha-postgresql-2           Bound    pvc-7edb7e28-7504-4dac-9e7d-27456d5392e7   300Gi      RWO            rook-cephfs    3m40s
persistentvolumeclaim/data-postgresql-ha-postgresql-witness-0   Bound    pvc-ea605028-15e4-46d1-9f7a-d47714d94887   300Gi      RWO            rook-cephfs    3m41s

postgresql-ha-client

HA로 구성되어있는 postgres의 접근을 위해서는 pgpool에 직접 접근이 되지는 않아 postgresql-ha-client를 따로 만들어서 진행해준다.
위의 Deployment 항목을 본다면 다음과 같은 출력이 되어있는 것을 확인할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Pgpool acts as a load balancer for PostgreSQL and forward read/write connections to the primary node while read-only connections are forwarded to standby nodes.

To get the password for "deepadmin" run:

    export POSTGRES_PASSWORD=$(kubectl get secret --namespace postgresql-ha postgresql-secrets -o jsonpath="{.data.password}" | base64 -d)

To get the password for "repmgr" run:

    export REPMGR_PASSWORD=$(kubectl get secret --namespace postgresql-ha postgresql-secrets -o jsonpath="{.data.repmgr-password}" | base64 -d)

To connect to your database run the following command:

    kubectl run postgresql-ha-client --rm --tty -i --restart='Never' --namespace postgresql-ha --image docker.io/bitnami/postgresql-repmgr:15.2.0-debian-11-r12 --env="PGPASSWORD=$POSTGRES_PASSWORD"  \
        --command -- psql -h postgresql-ha-pgpool -p 5432 -U deepadmin -d deepadmin

To connect to your database from outside the cluster execute the following commands:

    kubectl port-forward --namespace postgresql-ha svc/postgresql-ha-pgpool 5432:5432 &
    psql -h 127.0.0.1 -p 5432 -U deepadmin -d deepadmin

그러나 관리의 편리함에 있어서는 위의 명령어를 매일 치기보다 pod를 하나 만들어서 접근하여 관리 하는 것이 편하다.
pod의 접근을 빨리 하기 위해서는 namespace를 default로 관리 해주는 것이 편하다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# postgresql-ha-client
dor1@is-master ~/postgresql-ha ❯ vi postgresql-ha-client.yaml
apiVersion: v1
kind: Pod
metadata:
  name: postgresql-ha-client
  namespace: default
spec:
  containers:
  - name: postgresql-ha-client
    image: docker.io/bitnami/postgresql-repmgr:15.2.0-debian-11-r5
    command: ["bash"]
    args: ["-c", "while true; do sleep 10; done"]
    stdin: true
    tty: true
    terminationMessagePolicy: "FallbackToLogsOnError"

1
2
3
4
5
6
7

dor1@is-master ~/postgresql-ha ❯ kubectl apply -f ./postgresql-ha-client.yaml
pod/postgresql-ha-client created

# 확인
dor1@is-master ~/postgresql-ha ❯ kubectl get pod
NAME                       READY   STATUS    RESTARTS   AGE
postgresql-ha-client       1/1     Running   0          93s

접근은 쉽게 exec를 사용하여 해 주면 된다.
이미지 특성 상 username이 존재하지는 않아 history가 남지는 않는다.
이걸 생각해보면 어쩌면 다른 이미지로 psql 사용이 가능하게 끔 하여 만들 수도 있을 것으로 생각이 든다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# exec로 접근
dor1@is-master ~/postgresql-ha ❯ kubectl exec -it postgresql-ha-client -- bash
I have no name!@postgresql-ha-client:/$

# psql로 DB 접속
I have no name!@postgresql-ha-client:/$ psql -h postgresql-ha-pgpool.postgresql-ha.svc.cluster.local -p 5432 -U deepadmin
Password for user deepadmin:
psql (15.2)
Type "help" for help.

deepadmin=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 deepadmin | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repmgr    | Superuser, Replication                                     | {}

deepadmin=>

Superuser 권한 주기

postgres의 기본 유저가 아닌 parameter를 통하여 유저를 따로 사용하게 되면 SUPERUSER 권한이 따로 없다.
만약 pgadmin4 같은 곳에서 session에 대해 stop(kill) 시켜야 되는 상황이라면 권한의 문제로 인하여 안된다.
권한 수정을 위해 postgresql-ha-client를 통하여 Superuser 권한이 있는 postgres계정을 접근하고 싶어도 다음과 같은 error가 발생하여 접근이 안된다.

1
2
3

I have no name!@postgresql-ha-client:/$ psql -h postgresql-ha-pgpool.postgresql-ha.svc.cluster.local -p 5432 -U postgres
psql: error: connection to server at "postgresql-ha-pgpool.postgresql-ha.svc.cluster.local" (10.233.41.96), port 5432 failed: FATAL:  SCRAM authentication failed
DETAIL:  pool_passwd file does not contain an entry for "postgres"

이럴 경우엔 HA 구성에 따라 1번 pod의 DB를 접근하여 계정에 대하여 role 권한을 수정해주면 된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

dor1@is-master ~/postgresql-ha ❯ kubectl exec -it -n postgresql-ha postgresql-ha-postgresql-0 -- bash

I have no name!@postgresql-ha-postgresql-0:/$ psql -U postgres
Password for user postgres:
psql (15.2)
Type "help" for help.

postgres=# ALTER ROLE deepadmin SUPERUSER;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 deepadmin | Superuser, Create DB                                       | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repmgr    | Superuser, Replication                                     | {}

postgres=

권한이 수정 되면 HA 구성에 따라 자동으로 다른 postgres pod에도 설정이 된다.

Cluster, Kubernetes
cluster kubernetes postgres
This post is licensed under CC BY 4.0 by the author.