2021.04.22, 2020.09.10
Crunchy PostgreSQL Operator #1
1. Crunchy PostgreSQL Operator 소개
2. PostgreSQL Operator Configuration
3. Create a PostgreSQL Cluster (Multi-Tenancy)
4. pgAdmin4
5. High-Availability
6. Disaster Recovery: Backups & Restores
7. Monitoring
8. pgBouncer
1. Crunchy PostgreSQL Operator 소개
- Crunchy PostgreSQL Operator 4.4.1
https://access.crunchydata.com/documentation/postgres-operator/latest/
The Crunchy PostgreSQL Operator automates and simplifies deploying and managing open source PostgreSQL clusters on Kubernetes and other Kubernetes-enabled Platforms by providing the essential features you need to keep your PostgreSQL clusters up and running, including:
✓ PostgreSQL Cluster Provisioning
✓ High-Availability (Safe, automated failover backed by a distributed consensus based high-availability solution, Uses Pod Anti-Affinity)
✓ Disaster Recovery (Backups and restores leverage the open source pgBackRest utility)
✓ Scheduled Backups
✓ Backup to S3
✓ Monitoring (pgMonitor - Prometheus, Grafana)
✓ Advanced Replication Support
✓ Connection Pooling (pgBouncer)
✓ Crunchy pgBouncer is a lightweight connection pooler for PostgreSQL databases.
✓ Primary, Replica별로 Connection pool 제공
- Crunchy PostgreSQL Operator Architecture
- The PostgreSQL Operator 4.4.1 release includes the following software versions upgrades:
The PostgreSQL containers now use versions 12.4, 11.9, 10.14, 9.6.19, and 9.5.23
https://access.crunchydata.com/documentation/postgres-operator/4.4.1/configuration/compatibility/
https://access.crunchydata.com/documentation/postgres-operator/4.6.0/configuration/compatibility/
- PostgreSQL Operator is tested with Kubernetes 1.13 - 1.18, OpenShift 3.11+, OpenShift 4.3+, Google Kubernetes Engine (GKE), and VMware Enterprise PKS 1.3+.
✓ Storage
The Crunchy PostgreSQL Operator is tested with a variety of different types of Kubernetes storage and Storage Classes, including:
* Rook
* StorageOS
* Google Compute Engine persistent volumes
* NFS
* HostPath
- Crunchy PostgreSQL Operator 소개
✓ postgresql.org : Crunchy PostgreSQL Operator 4.4 release
https://www.postgresql.org/about/news/2053/
✓ kubernetes.io : Creating a PostgreSQL Cluster using Helm
https://kubernetes.io/blog/2016/09/creating-postgresql-cluster-using-helm/
2. PostgreSQL Operator Configuration
a. Get the PostgreSQL Operator Installer Manifest
$ curl https://raw.githubusercontent.com/CrunchyData/postgres-operator/v4.4.1/installers/kubectl/postgres-operator.yml > postgres-operator.yml
b. Configure the PostgreSQL Operator Installer
- 주요 속성
✓ pgo_admin_password: you will use with the pgo client to manage your PostgreSQL clusters.
✓ primary_storage, replica_storage, backup_storage, and backrest_storage : to set the storage default storage classes that you would like the PostgreSQL Operator to use.
* Rook ceph를 사용하는 경우 [primary|replica|backup|backrest]_storage는 “rook”으로 변경, storage9_class는 생성된 StorageClass명으로 변경
https://info.crunchydata.com/blog/crunchy-postgresql-operator-with-rook-ceph-storage
* Minikube인 경우 [primary|replica|backup|backrest]_storage는 기본 값(hostpathstorage) 사용
$ vi postgres-operator.yml
…
apiVersion: v1
kind: ConfigMap
metadata:
name: pgo-deployer-cm
namespace: pgo
data:
archive_mode: “true"
ccp_image_tag: "centos7-12.4-4.4.1”
namespace: "pgo"
…
backrest_storage: “rook” # default: “hostpathstorage”, Minikuke에서는 default 사용
backup_storage: “rook” # default: "hostpathstorage"
primary_storage: “rook” # default: "hostpathstorage"
replica_storage: “rook” # default: "hostpathstorage"
…
storage9_name: "rook"
storage9_access_mode: "ReadWriteOnce"
storage9_size: "1Gi"
storage9_type: "dynamic"
storage9_class: ”rook-ceph-block-sc-iap” # default: “rook-ceph-block”
…
$- Config Metrics Infrastructure
PostgreSQL clusters created by the Crunchy PostgreSQL Operator can optionally be configured to serve performance metrics via Prometheus Exporters.
$ vi postgres-operator.yml
apiVersion: batch/v1
kind: Job
metadata:
name: pgo-deploy
namespace: pgo
spec:
env:
- name: DEPLOY_ACTION
value: install,install-metrics # default: “install"
…
data:
…
grafana_install: "true” # default: false
grafana_admin_password: "admin" # default: ''
grafana_storage_class_name: "rook-ceph-block-sc-iap" # default: fast, Minikue는 standard, k8s(IAP)는 rook-ceph-block-sc-iap
metrics: "true" # default: false
prometheus_install: “true" # default: false
prometheus_storage_class_name: "rook-ceph-block-sc-iap" # default: fast
…
$- K8s (Rook Ceph)
$ k get storageclasses.storage.k8s.io
NAME PROVISIONER AGE
rook-ceph-block-sc-iap (default) rook-ceph.rbd.csi.ceph.com 34d
$- Minikube
yoosungjeon@ysjeon-Dev ~ % k get storageclasses.storage.k8s.io
NAME PROVISIONER AGE
standard (default) k8s.io/minikube-hostpath 14d
yoosungjeon@ysjeon-Dev ~ %
c. Install the PostgreSQL Operator
$ kubectl create namespace pgo
$ kubectl apply -f postgres-operator.yml
serviceaccount/pgo-deployer-sa created
clusterrole.rbac.authorization.k8s.io/pgo-deployer-cr created
configmap/pgo-deployer-cm created
clusterrolebinding.rbac.authorization.k8s.io/pgo-deployer-crb created
job.batch/pgo-deploy created
$ kubectl patch service postgres-operator -n pgo -p '{ "spec": { "type": "NodePort" } }'
$ kubectl patch service crunchy-grafana -n pgo -p '{ "spec": { "type": "NodePort" } }'
d. Install the PostgreSQL Operator (pgo) Client
$ curl https://raw.githubusercontent.com/CrunchyData/postgres-operator/v4.4.1/installers/kubectl/client-setup.sh > client-setup.sh
$ chmod +x client-setup.sh
$ ./client-setup.sh
Operating System found is Darwin...
Downloading pgo-mac version: v4.4.1...
…
pgo client files have been generated, please add the following to your bashrc
export PATH=/Users/yoosungjeon/.pgo/pgo:$PATH
export PGOUSER=/Users/yoosungjeon/.pgo/pgo/pgouser
export PGO_CA_CERT=/Users/yoosungjeon/.pgo/pgo/client.crt
export PGO_CLIENT_CERT=/Users/yoosungjeon/.pgo/pgo/client.crt
export PGO_CLIENT_KEY=/Users/yoosungjeon/.pgo/pgo/client.key
$ kubectl get svc postgres-operator -n pgo -o jsonpath='{.spec.ports[?(@.name=="apiserver")].nodePort}'
30144
$ cat <<EOF >> ~/.bash_profile
# Crunchy PostgreSQL Operator
export PATH=/Users/yoosungjeon/.pgo/pgo:\$PATH
export PGOUSER=/Users/yoosungjeon/.pgo/pgo/pgouser
export PGO_CA_CERT=/Users/yoosungjeon/.pgo/pgo/client.crt
export PGO_CLIENT_CERT=/Users/yoosungjeon/.pgo/pgo/client.crt
export PGO_CLIENT_KEY=/Users/yoosungjeon/.pgo/pgo/client.key
export PGO_APISERVER_URL='https://192.168.64.10:30144'
export PGO_NAMESPACE=pgo
EOF
$ source ~/.bash_profile
e. Verification
$ pgo version
pgo client version 4.4.1
pgo-apiserver version 4.4.1
$ kubectl -n pgo get deployments
NAME READY UP-TO-DATE AVAILABLE AGE
crunchy-grafana 1/1 1 1 14m
crunchy-prometheus 1/1 1 1 14m
postgres-operator 1/1 1 1 149m
$ k get pod -n pgo
NAME READY STATUS RESTARTS AGE
crunchy-grafana-5746f458bb-7ksvc 1/1 Running 0 15m
crunchy-prometheus-7998c49689-hcxbl 1/1 Running 0 15m
pgo-deploy-4trrp 0/1 Completed 0 16m
postgres-operator-66cc8bd589-sq4jc 4/4 Running 0 15m
$ kubectl get svc -n pgo
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
crunchy-grafana NodePort 10.102.130.57 <none> 3000:31814/TCP 41m
crunchy-prometheus ClusterIP 10.103.163.145 <none> 9090/TCP 41m
postgres-operator NodePort 10.100.54.87 <none> 8443:30144/TCP,4171:31479/TCP,4150:31741/TCP 42m
$ kubectl get pvc -n pgo -w
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
grafanadata Bound pvc-d7091a61-8392-4df1-9f27-ca5b5265ccca 1G RWO standard 117s
prometheusdata Bound pvc-08f8fb46-72ce-4ed5-ba79-1f98d9f14d65 1G RWO standard 2m5s
$
3. Create a PostgreSQL Cluster (Multi-Tenancy)
- pgo command
$ pgo create cluster --help
--database string If specified, sets the name of the initial database that is created for the user.
--username string The username to use for creating the PostgreSQL user with standard permissions.
--password string The password to use for standard user account created during cluster initialization.
--namespace string The namespace to use for pgo requests.
--metrics Adds the crunchy-collect container to the database pod.
--ccp-image-tag string The CCPImageTag to use for cster creation. If specified, overrides the pgo.yaml setting.
...--ccp-image-tag
PostgreSQL 12 : centos7-12.4-4.4.1 (registry.developers.crunchydata.com/crunchydata/crunchy-postgres-ha:centos7-12.4-4.4.1)
PostgreSQL 11 : centos7-11.9-4.4.1 (registry.developers.crunchydata.com/crunchydata/crunchy-postgres-ha:centos7-11.9-4.4.1)
PostgreSQL 10.14, 9.6.19, 9.5.23 : Not supported (Docker image not found)
- Create PostgreSQL Cluster
$ pgo create cluster emo-dev --username emo --password ***** --metrics --memory 1Gi --pvc-size 150Gi --pgbackrest-pvc-size 100Gi
created cluster: emo-dev
workflow id: 2e49c5f2-5f42-4f93-9578-35c97af7ea89
database name: emo-dev
users:
username: emo password: *****
$ #pgo create cluster gmd-prd --username gmd --password gmd12#$ --metrics --memory 4Gi --ccp-image-tag centos7-11.9-4.4.1 --replica-count=1 --pvc-size 500Gi --pgbackrest-pvc-size 350Gi
$ pgo test emo-dev
cluster : emo-dev
Services
primary (10.97.232.243:5432): UP
Instances
primary (emo-dev-65847bbd9c-jrpqk): UP
$ pgo show cluster emo-dev
cluster : emo-dev (crunchy-postgres-ha:centos7-12.4-4.4.1)
pod : emo-dev-65847bbd9c-jrpqk (Running) on iap08 (1/1) (primary)
pvc: emo-dev (100Gi)
resources : Memory: 1Gi
deployment : emo-dev
deployment : emo-dev-backrest-shared-repo
service : emo-dev - ClusterIP (10.97.232.243)
labels : crunchy_collect=false deployment-name=emo-dev name=emo-dev pg-cluster=emo-dev pg-pod-anti-affinity= pgo-backrest=true pgo-version=4.4.1 autofail=true crunchy-pgbadger=false crunchy-pgha-scope=emo-dev pgouser=admin workflowid=6b633dba-90b4-410f-b7ed-81d2f1b1abd8
$ kubectl get pod -n pgo | egrep 'NAME|emo-dev'
NAME READY STATUS RESTARTS AGE
backrest-backup-emo-dev-ttz9q 0/1 Completed 0 48s
emo-dev-65847bbd9c-jrpqk 1/1 Running 0 2m6s
emo-dev-backrest-shared-repo-65464bb747-md97g 1/1 Running 0 2m29s
emo-dev-stanza-create-fz66l 0/1 Completed 0 52s
$ k get pvc -n pgo | egrep 'NAME|emo-dev'
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
emo-dev Bound pvc-750d18ae-2204-446d-bccd-ef6b9c89313b 100Gi RWO rook-ceph-block-sc-iap 3m6s
emo-dev-pgbr-repo Bound pvc-fdce61e8-750f-4e31-a9ec-560346573805 100Gi RWO rook-ceph-block-sc-iap 3m6s
$ k get svc -n pgo | egrep -w 'NAME|emo-dev'
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
emo-dev ClusterIP 10.97.232.243 <none> 2022/TCP,5432/TCP 2m47s
$ kubectl patch service emo-dev -n pgo -p '{ "spec": { "type": "NodePort" } }'
$ kubectl get svc emo-dev -n pgo -o jsonpath='{.spec.ports[?(@.name=="postgres")].nodePort}'
30066
$ pgo update user emo-dev --username=emo --password=*****
$ pgo show user --all --show-system-accounts
CLUSTER USERNAME PASSWORD EXPIRES STATUS ERROR
------- -------------- ------------------------ -------- ------ -----
emo-dev ccp_monitoring never ok
...
$- on Remote client
yoosungjeon@ysjeon-Dev ~ % ~/PostgreSQL/9.5/bin/psql -V
psql (PostgreSQL) 9.5.0
yoosungjeon@ysjeon-Dev ~ % export PGPASSWORD=*****; psql -h 14.52.244.136 -p 30066 -U emo emo-dev
emo-dev=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
emo-dev=> \q
- Troubeshooting
▷ Problem: PostgreSQL POD 외부에서 요청시 "psql: FATAL: password authentication failed for user “emo” 에러 발생
▷ Solution: pgo update user 명령어 실행
4. pgAdmin4
- pgAdmin 4 is a popular graphical user interface that makes it easy to work with PostgreSQL databases from both a desktop or web-based client.
https://access.crunchydata.com/documentation/postgres-operator/4.4.1/architecture/pgadmin4/
$ pgo create pgadmin emo-dev
emo-dev pgAdmin addition scheduled
$ kubectl patch service emo-dev-pgadmin -n pgo -p '{ "spec": { "type": "NodePort" } }'
$ kubectl get svc emo-dev-pgadmin -n pgo -o jsonpath='{.spec.ports[0].nodePort}'
31241
$ pgo update user emo-dev --username=emo --password=*****
- Troubeshooting #1
▷ Problem: 왼쪽 패널에 있는 "Crunch ProgreSQL Operator” 선택시 다음과 같은 에러 발생
Failed to decrypt the saved password Error: ‘utf8’ code can’t codec can’t decode byte 0x93 in position 1:
Invalid start byte
▷ Solution: “emo-dev” 오른쪽 클릭 > “Clear Saved Password” 선택
- Troubeshooting #2
▷ Problem: PostgreSQL로 접속이 실패한 경우
could not connect to server: No route to host Is the server running on host "10.103.174.39"
▷ Cause: emo-dev-pgadmin이 재 생성되는 과정 중에, 과거에 생성되었던 PVC(Pysical Volume Clarim)을 사용하고 있으며,
PostgreSQL Instance의 접속 정보가 과거 IP로 되어 있기 때문에 발생됨
$ k get svc -n pgo | egrep -w "NAME|emo-dev "
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
emo-dev NodePort 10.101.243.65 <none> 9187:32063/TCP,2022:30732/TCP,5432:31147/TCP 4h6m
$ kubectl exec emo-dev-pgadmin-685f597974-r59qt -n pgo -it — sh
sh-4.2$ sqlite3 /var/lib/pgadmin/pgadmin4.db
SQLite version 3.7.17 2013-05-20 00:56:22
sqlite> select id, name, host, port, username, password from server;
1|emo-dev|10.103.213.82|5432|testuser|Nv1W6H1wOv9lZ51lfCrSb3lqRJwOsngk
2|emo-dev|10.103.174.39|5432|emo|
sqlite>▷ Solution: SQLite3의 server 테이블 정보를 업데이트 하고, pgAdmin4 POD를 재기동 후 접속
sqlite> update server set host = ’10.101.243.65’ where id = 2;
sqlite> .quit
exit
$ kubectl rollout restart deployment emo-dev-pgadmin -n pgo
'Kubernetes > DBMS' 카테고리의 다른 글
| MariaDB - replication (0) | 2021.10.29 |
|---|---|
| MariaDB (0) | 2021.10.04 |
| Crunchy PostgreSQL Managements (0) | 2021.09.23 |
| Crunchy PostgreSQL Operator #2 (0) | 2021.09.23 |
댓글