PostgreSQL 是一个功能强大的开源关系数据库，它使用和扩展了 SQL 语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能。PostgreSQL 的起源可以追溯到 1986 年，作为加利福尼亚大学伯克利分校 POSTGRES 项目的一部分，与 Linux 内核相似，PostgreSQL 是由社区驱动的开源项目，由全世界贡献者所维护。
PostgreSQL 号称是 "世界上最先进的开源关系型数据库"，具有很多先进的数据库理念，它对标的应该是 Oracle 和 SQLserver 这类商用库，与 MySQL 相比各有优劣，从使用角度来看，MySQL 偏向开发者，不过在开源社区 PostgreSQL 更受青睐，大多开源项目都使用 PostgreSQL 取代了 MySQL。
1、Crunchy
PostgreSQL 有很多高可用方案，如 Pgpool II 、Bucardo、PostgresXL 和 Postgres-XC 等。Crunchy 是一个容器化解决方案，它能快速部署 PostgreSQL 。
PostgreSQL Operator 是一个 kubernetes 应用控制器，用于在 Kubernetes 上运行生产级 PostgreSQL ，它简化了 PostgreSQL 集群的部署，并提供了集群配置管理、高可用性、计划备份、灾难恢复、监控和连接池等功能。
2、安装 Crunchy PostgreSQL Operator
PostgreSQL 数据需要持久化，kubernetes 需要先创建存储类，PostgreSQL Operator 支持 NFS、ceph 等存储类，这里用的是 rook-ceph-block 。
1[root@k8s-test-master01 ~]# kubectl get sc 2NAME PROVISIONER RECLAIMPOLICY VOLUMEBINDINGMODE ALLOWVOLUMEEXPANSION AGE 3ceph-storage kubernetes.io/rbd Delete Immediate true 4d1h 4rook-ceph-block kubernetes.io/rbd Delete Immediate true 3d23h
创建命名空间及下载 yaml 文件
1kubectl create namespace pgo 2curl https://raw.githubusercontent.com/CrunchyData/postgres-operator/v4.6.1/installers/kubectl/postgres-operator.yml > postgres-operator.yml
修改 yaml 文件，加入下列环境变量，用于设置存储类
1 - name: BACKREST_STORAGE 2 value: "rook" 3 - name: BACKUP_STORAGE 4 value: "rook" 5 - name: PRIMARY_STORAGE 6 value: "rook" 7 - name: REPLICA_STORAGE 8 value: "rook" 9 - name: STORAGE9_SIZE 10 value: "50G" 11# 安装 postgres operator 12kubectl apply -f postgres-operator.yml
安装 pgo 客户端
1curl https://raw.githubusercontent.com/CrunchyData/postgres-operator/v4.6.1/installers/kubectl/client-setup.sh > client-setup.sh 2chmod +x client-setup.sh 3./client-setup.sh 4 5cat <<EOF >> ~/.bashrc 6export PGOUSER="${HOME?}/.pgo/pgo/pgouser" 7export PGO_CA_CERT="${HOME?}/.pgo/pgo/client.crt" 8export PGO_CLIENT_CERT="${HOME?}/.pgo/pgo/client.crt" 9export PGO_CLIENT_KEY="${HOME?}/.pgo/pgo/client.key" 10export PGO_APISERVER_URL='https://127.0.0.1:8443' 11export PGO_NAMESPACE=pgo 12EOF 13 14source ~/.bashrc
配置 pgo 客户端
1# postgres operator 的 service 类型默认是 ClusterIP ，需要开启端口转发 2kubectl -n pgo port-forward svc/postgres-operator 8443:8443 & 3 4wget https://github.com/CrunchyData/postgres-operator/releases/download/v4.6.1/pgo 5chmod +x pgo 6mv pgo /usr/local/bin/ 7[root@k8s-test-master01 ~]# pgo version 8pgo client version 4.6.1 9Handling connection for 8443 10pgo-apiserver version 4.6.1 11[root@k8s-test-master01 ~]# 12 13# 注：kubernetes 节点需要安装 socat，否则提示 unable to do port forwarding: socat not found. 14# RHEL 系 15yum install socat 16# Debian 系 17sudo apt-get install socat 18# SUSE 19sudo zypper in socat
3、安装 PostgreSQL 集群
PostgreSQL Operator 可定制很高，可以根据自己的实际情况进行设置
1# PostgreSQL 集群的名称 2pgo_cluster_name=hippo 3# PostgreSQL 部署的命名空间 4cluster_namespace=pgo 5# 自定义 PostgreSQL 配置 6cat postgres-ha.yaml 7--- 8bootstrap: 9 dcs: 10 postgresql: 11 parameters: 12 max_connections: 30 13 shared_buffers: 2GB 14 password_encryption: scram-sha-256 15# 创建 hippo-custom-config configmap 16kubectl -n pgo create configmap hippo-custom-config --from-file=postgres-ha.yaml 17 18# 超级用户密码 19kubectl create secret generic -n "${cluster_namespace}" "${pgo_cluster_name}-postgres-secret" \ 20 --from-literal=username=postgres \ 21 --from-literal=password=Supersecurepassword* 22 23# 同步复制用户密码 24kubectl create secret generic -n "${cluster_namespace}" "${pgo_cluster_name}-primaryuser-secret" \ 25 --from-literal=username=primaryuser \ 26 --from-literal=password=Anothersecurepassword* 27 28# 普通用户 hippo 用户密码 29kubectl create secret generic -n "${cluster_namespace}" "${pgo_cluster_name}-hippo-secret" \ 30 --from-literal=username=hippo \ 31 --from-literal=password=Moresecurepassword*
创建 PostgreSQL 集群
1pgo create cluster ${pgo_cluster_name} \ 2 --cpu=1.0 --cpu-limit=4.0 \ 3 --memory=2Gi --memory-limit=6Gi \ 4 --metrics \ 5 --exporter-cpu=0.5 --exporter-cpu-limit=1.0 \ 6 --exporter-memory=256Mi --exporter-memory-limit=1Gi \ 7 --replica-count=2 \ 8 --replica-storage-config='rook' \ 9 --wal-storage-config='rook' \ 10 --wal-storage-size=10Gi \ 11 --custom-config=hippo-custom-config \ 12 --pgbouncer \ 13 --storage-config='rook' \ 14 --pvc-size=20Gi \ 15 --pgbackrest-cpu=0.5 --pgbackrest-cpu-limit=1.0 \ 16 --pgbackrest-memory=256Mi --pgbackrest-memory-limit=1Gi \ 17 --pgbackrest-pvc-size=100Gi \ 18 --pgbackrest-storage-config='rook' \ 19 --username=hippo
--metrics 开启 PostgreSQL exporter，接入普罗米修斯监控
--pgbouncer 开启pgbouncer连接池
--replica-count=2 创建2个副本的高可用性PostgreSQL集群
--sync-replication 开启同步复制,防止事务丢失
生产环境可以设置 pod 调度策略，避免 pod 运行在同一个 node 节点
--pod-anti-affinity
--node-label=region=us-east-1
--node-affinity-type=required
查看 PostgreSQL 集群
1[root@k8s-test-master01 ~]# pgo show cluster hippo 2 3cluster : hippo (crunchy-postgres-ha:centos8-13.2-4.6.1) 4 pod : hippo-5f69bb897d-cqjgh (Running) on k8s-test-node01 (2/2) (primary) 5 pvc: hippo (20Gi) 6 pod : hippo-pksw-7588f857f7-m6w72 (Running) on k8s-test-master01 (2/2) (replica) 7 pvc: hippo-pksw (20Gi) 8 pod : hippo-vpcy-6796f5b79b-kddqg (Running) on k8s-test-node01 (2/2) (replica) 9 pvc: hippo-vpcy (20Gi) 10 resources : CPU: 1 Memory: 2Gi 11 limits : CPU: 4 Memory: 6Gi 12 deployment : hippo 13 deployment : hippo-backrest-shared-repo 14 deployment : hippo-pgbouncer 15 deployment : hippo-pksw 16 deployment : hippo-vpcy 17 service : hippo - ClusterIP (10.254.76.44) - Ports (9187/TCP, 2022/TCP, 5432/TCP) 18 service : hippo-pgbouncer - ClusterIP (10.254.48.160) - Ports (5432/TCP) 19 service : hippo-replica - ClusterIP (10.254.238.91) - Ports (9187/TCP, 2022/TCP, 5432/TCP) 20 pgreplica : hippo-pksw 21 pgreplica : hippo-vpcy 22 labels : deployment-name=hippo name=hippo pg-cluster=hippo pgo-version=4.6.1 pgouser=admin workflowid=452b51f0-848c-4891-9131-f4e1b8fbcc36 crunchy-pgha-scope=hippo crunchy-postgres-exporter=true
接入 Prometheus
1--- 2apiVersion: monitoring.coreos.com/v1 3kind: ServiceMonitor 4metadata: 5 annotations: 6 meta.helm.sh/release-name: demo 7 meta.helm.sh/release-namespace: monitoring 8 labels: 9 app: prometheus-operator-postgres-replica-exporter 10 app.kubernetes.io/managed-by: Helm 11 chart: prometheus-operator-9.3.2 12 heritage: Helm 13 release: demo 14 name: demo-prometheus-operator-postgres-replica-exporter 15 namespace: monitoring 16spec: 17 endpoints: 18 - port: postgres-exporter 19 path: /metrics 20 interval: 30s 21 namespaceSelector: 22 matchNames: 23 - pgo 24 selector: 25 matchLabels: 26 name: hippo-replica 27 pg-cluster: hippo 28 vendor: crunchydata 29--- 30apiVersion: monitoring.coreos.com/v1 31kind: ServiceMonitor 32metadata: 33 annotations: 34 meta.helm.sh/release-name: demo 35 meta.helm.sh/release-namespace: monitoring 36 labels: 37 app: prometheus-operator-postgres-exporter 38 app.kubernetes.io/managed-by: Helm 39 chart: prometheus-operator-9.3.2 40 heritage: Helm 41 release: demo 42 name: demo-prometheus-operator-postgres-exporter 43 namespace: monitoring 44spec: 45 endpoints: 46 - port: postgres-exporter 47 path: /metrics 48 interval: 30s 49 namespaceSelector: 50 matchNames: 51 - pgo 52 selector: 53 matchLabels: 54 name: hippo 55 pg-cluster: hippo 56 vendor: crunchydata
安装 pgAdmin 的图形管理工具
1pgo create pgadmin -n pgo hippo 2 3# service 类型默认是 ClusterIP ，新增一个 NodePort 类型的 service ，用与外部访问 4apiVersion: v1 5kind: Service 6metadata: 7 labels: 8 name: hippo-pgadmin 9 pg-cluster: hippo 10 pgadmin: "true" 11 vendor: crunchydata 12 name: hippo-pgadmin-external 13 namespace: pgo 14spec: 15 ports: 16 - port: 5050 17 protocol: TCP 18 targetPort: 5050 19 nodePort: 5050 20 selector: 21 name: hippo-pgadmin 22 type: NodePort 23 24# 如果登录 pgAdmin 提示密码错误，可以使用 pgo 更新下密码即可 25pgo update user -n pgo hippo --username=hippo --password=Moresecurepassword*
4、同步复制
进入 PostgreSQL 主实例创建一个表，并插入数据。
1[root@k8s-test-master01 ~]# kubectl get po -n pgo -lcrunchy-pgha-scope=hippo 2NAME READY STATUS RESTARTS AGE 3hippo-749d494c4c-52cf6 2/2 Running 0 4d5h 4hippo-hvaz-56bb495b94-qw6zl 2/2 Running 0 4d5h 5hippo-snmw-6d7877d78b-rbrjs 2/2 Running 0 4d5h 6[root@k8s-test-master01 ~]# kubectl -n pgo exec -it hippo-749d494c4c-52cf6 -- psql 7Defaulting container name to database. 8Use 'kubectl describe pod/hippo-749d494c4c-52cf6 -n pgo' to see all of the containers in this pod. 9psql (13.2) 10Type "help" for help. 11 12postgres=# \c hippo 13You are now connected to database "hippo" as user "postgres". 14hippo=# CREATE TABLE demo ( 15hippo(# id integer, 16hippo(# name text, 17hippo(# val numeric 18hippo(# ); 19CREATE TABLE 20hippo=# \dt 21 List of relations 22 Schema | Name | Type | Owner 23--------+------+-------+---------- 24 public | demo | table | postgres 25(1 row) 26 27hippo=# INSERT INTO demo VALUES (1, 'test', 9.99); 28INSERT 0 1 29hippo=# select * from demo; 30 id | name | val 31----+------+------ 32 0 | test | 9.99 33(1 row) 34 35hippo=#
进入 PostgreSQL副本库查看数据
两个副本都可以同步主库数据。
5、逻辑备份
创建备份的 PVC
1--- 2kind: PersistentVolumeClaim 3apiVersion: v1 4metadata: 5 name: postgres-backup 6 namespace: pgo 7spec: 8 accessModes: 9 - ReadWriteOnce 10 resources: 11 requests: 12 storage: 20Gi 13 storageClassName: rook-ceph-block
创建 hippo 库的逻辑备份，指定 PVC
1[root@k8s-test-master01 ~]# pgo backup hippo --backup-type=pgdump --database=hippo --pvc-name=postgres-backup -n pgo 2created Pgtask backup-hippo-pgdump 3[root@k8s-test-master01 ~]# kubectl get po -n pgo -lpgdump=true 4NAME READY STATUS RESTARTS AGE 5backup-hippo-pgdump-fdgj-xwzml 0/1 Completed 0 56m 6backup-hippo-pgdump-jxis-flhpr 0/1 Completed 0 34m 7backup-hippo-pgdump-utaq-6tzfh 0/1 Completed 0 48m 8backup-hippo-pgdump-vlzn-d5z5x 0/1 Completed 0 15s
查看备份
1[root@k8s-test-master01 ~]# pgo show backup --backup-type=pgdump hippo 2Handling connection for 8443 3 4pgdump : backup-hippo-pgdump 5 PVC Name: postgres-backup 6 Access Mode: ReadWriteOnce 7 PVC Size: 1G 8 Creation: 2021-03-05 10:23:14 +0000 UTC 9 CCPImageTag: centos8-13.2-4.6.1 10 Backup Status: job completed [backup-hippo-pgdump-vlzn] 11 Backup Host: hippo 12 Backup User Secret: hippo-postgres-secret 13 Backup Port: 5432 14 Backup Opts:
删除表
1postgres=# \c hippo 2You are now connected to database "hippo" as user "postgres". 3hippo=# \dt 4 List of relations 5 Schema | Name | Type | Owner 6--------+------+-------+---------- 7 public | demo | table | postgres 8(1 row) 9 10hippo=# drop table demo ; 11DROP TABLE 12hippo=# \dt 13Did not find any relations. 14hippo=#
从逻辑备份还原数据，需要查看逻辑备份存储到 PVC 以及逻辑备份创建的时间戳。
实际上通过 pgo show backup 获取时间是不准的，创建容器与启动备份任务是有时间差的，最准确的备份时间点应该查看 pod 的日志，或者把 PVC 挂载出来查看。
1[root@k8s-test-master01 ~]# kubectl get po -n pgo -lpgdump=true 2NAME READY STATUS RESTARTS AGE 3backup-hippo-pgdump-fdgj-xwzml 0/1 Completed 0 62m 4backup-hippo-pgdump-jxis-flhpr 0/1 Completed 0 39m 5backup-hippo-pgdump-utaq-6tzfh 0/1 Completed 0 53m 6backup-hippo-pgdump-vlzn-d5z5x 0/1 Completed 0 5m18s 7[root@k8s-test-master01 ~]# kubectl logs -f backup-hippo-pgdump-vlzn-d5z5x -n pgo 8Fri Mar 5 10:23:23 UTC 2021 INFO: Image mode found: pgdump 9Fri Mar 5 10:23:23 UTC 2021 INFO: Starting in 'pgdump' mode 10Fri Mar 5 10:23:23 UTC 2021 INFO: Waiting for PostgreSQL to be ready.. 11hippo:5432 - accepting connections 12Fri Mar 5 10:23:23 UTC 2021 INFO: Checking if PostgreSQL is accepting queries.. 13 now 14------------------------------- 15 2021-03-05 10:23:23.601422+00 16(1 row) 17 18Fri Mar 5 10:23:23 UTC 2021 INFO: Dumping to -f /pgdata/hippo-backups/2021-03-05-10-23-23/dump 19Fri Mar 5 10:23:23 UTC 2021 INFO: Taking logical backup of the hippo database.. 20Fri Mar 5 10:23:23 UTC 2021 INFO: Logical backup completed. Exiting..
恢复数据
1[root@k8s-test-master01 ~]# pgo restore hippo --backup-type=pgdump --backup-pvc=postgres-backup \ 2> --pgdump-database=hippo --pitr-target="2021-03-05-10-23-23" -n pgo 3WARNING: Are you sure? (yes/no): yes 4Handling connection for 8443 5restore performed on hippo to postgres-backup opts= pitr-target=2021-03-05-10-23-23 6[root@k8s-test-master01 ~]# kubectl get po -n pgo -ljob-name=pgrestore-hippo-tbps 7NAME READY STATUS RESTARTS AGE 8pgrestore-hippo-tbps-g8xcv 0/1 Completed 0 67s
查看数据已经恢复。
1postgres=# \c hippo 2You are now connected to database "hippo" as user "postgres". 3hippo=# \dt 4 List of relations 5 Schema | Name | Type | Owner 6--------+------+-------+---------- 7 public | demo | table | postgres 8(1 row) 9 10hippo=# select * from demo; 11 id | name | val 12----+------+------ 13 1 | test | 9.99 14(1 row)
6、故障转移
手动故障转移，可以自动选择最佳的候选副本进行故障转移，或指定的副本候选者进行故障转移。
1# 自动选择副本 2pgo failover hippo 3# 指定副本 4pgo failover hippo --target==hippo-wvqy
查询集群的副本实例数
1[root@k8s-test-master01 ~]# kubectl get po -n pgo -lcrunchy-pgha-scope=hippo 2NAME READY STATUS RESTARTS AGE 3hippo-679cd9ddf5-5rfjf 2/2 Running 0 135m 4hippo-ohbu-68d479877c-4mt5h 2/2 Running 0 134m 5hippo-wvqy-7f949f79c6-2p7jq 2/2 Running 0 134m 6[root@k8s-test-master01 ~]# pgo failover hippo --query 7 8Cluster: hippo 9REPLICA STATUS NODE REPLICATION LAG PENDING RESTART 10hippo-ohbu running k8s-test-master01 0 MB false 11hippo-wvqy running k8s-test-node01 0 MB false
手动故障转移
1[root@k8s-test-master01 ~]# pgo failover hippo 2WARNING: Are you sure? (yes/no): yes 3failover success for cluster hippo
查看副本实例已经发生变化
1[root@k8s-test-master01 ~]# pgo failover hippo --query 2 3Cluster: hippo 4REPLICA STATUS NODE REPLICATION LAG PENDING RESTART 5hippo running k8s-test-node01 0 MB false 6hippo-ohbu running k8s-test-master01 0 MB false
进入转移后的主实例 hippo-wvqy ，删掉数据目录
1[root@k8s-test-master01 ~]# kubectl exec -it -n pgo hippo-wvqy-7f949f79c6-2p7jq -- bash 2Defaulting container name to database. 3Use 'kubectl describe pod/hippo-wvqy-7f949f79c6-2p7jq -n pgo' to see all of the containers in this pod. 4bash-4.4$ lsblk 5NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT 6rbd0 251:0 0 10G 0 disk 7rbd1 251:16 0 20G 0 disk 8rbd2 251:32 0 20G 0 disk /pgdata 9rbd3 251:48 0 10G 0 disk /pgwal 10vda 252:0 0 20G 0 disk 11└─vda1 252:1 0 20G 0 part /etc/resolv.conf 12bash-4.4$ cd /pgdata/ 13bash-4.4$ ls 14hippo-wvqy lost+found 15bash-4.4$ rm -rf hippo-wvqy/ 16bash-4.4$ ls 17lost+found 18bash-4.4$
再执行故障转移
1[root@k8s-test-master01 ~]# pgo failover hippo 2WARNING: Are you sure? (yes/no): yes 3Handling connection for 8443 4failover success for cluster hippo
再查看 hippo-wvqy 实例，数据已恢复。
1[root@k8s-test-master01 ~]# pgo failover hippo --query 2Handling connection for 8443 3 4Cluster: hippo 5REPLICA STATUS NODE REPLICATION LAG PENDING RESTART 6hippo-ohbu running k8s-test-master01 0 MB false 7hippo-wvqy running k8s-test-node01 0 MB false 8[root@k8s-test-master01 ~]# kubectl exec -it -n pgo hippo-wvqy-7f949f79c6-2p7jq -- bash 9Defaulting container name to database. 10Use 'kubectl describe pod/hippo-wvqy-7f949f79c6-2p7jq -n pgo' to see all of the containers in this pod. 11bash-4.4$ lsblk 12NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT 13rbd0 251:0 0 10G 0 disk 14rbd1 251:16 0 20G 0 disk 15rbd2 251:32 0 20G 0 disk /pgdata 16rbd3 251:48 0 10G 0 disk /pgwal 17vda 252:0 0 20G 0 disk 18└─vda1 252:1 0 20G 0 part /etc/resolv.conf 19bash-4.4$ ls /pgdata/ 20hippo-wvqy lost+found 21bash-4.4$ du -sh /pgdata/hippo-wvqy/ 22160M /pgdata/hippo-wvqy/
7、总结
除了文中列举的功能外，crunchy 还提供了连接池、定时备份、增量备份等，这里就不一一列举了，crunchy 的实现原理和逻辑还有待研究，感兴趣的读者可以到官网查看，官方的文档还是挺详细的。
参考文档： https://access.crunchydata.com/documentation/postgres-operator/4.6.1/
感兴趣的读者可以关注下微信号
