一、安装高可用PG到K8s集群（离线）¶
参考链接：https://artifacthub.io/packages/helm/bitnami-aks/postgresql-ha
1、下载chart包
[root@k8s-master01 ~]# mkdir helm
[root@k8s-master01 ~]# cd helm
[root@k8s-master01 helm]# helm pull oci://docker.kubeasy.com/bitnamicharts/postgresql-ha --version 15.0.2
2、解压并修改values.yaml文件
[root@k8s-master01 helm]# tar xf postgresql-ha-15.0.2.tgz
[root@k8s-master01 helm]# cd postgresql-ha/
[root@k8s-master01 postgresql-ha]# vim values.yaml
# 修改第27行内容
27 imageRegistry: "docker.kubeasy.com"
# 修改第33行内容
33 defaultStorageClass: "nfs-csi"
# 修改第37行内容
37 password: "zq123456"
# 修改第40行内容
40 repmgrPassword: "zq123456"
# 修改第48行内容，pgpool可以将查询分发到多个PostgreSQL节点,实现读写分离
48 adminPassword: "zq123456"
# 修改第700行内容，用于提供额外的投票机制,防止脑裂现象,非必须
700 create: true
修改后的完整配置文件
[root@k8s-master01 postgresql-ha]# egrep -v "#|^$" values.yaml
global:
imageRegistry: "docker.kubeasy.com"
imagePullSecrets: []
defaultStorageClass: "nfs-csi"
storageClass: ""
postgresql:
username: ""
password: "zq123456"
database: ""
repmgrUsername: ""
repmgrPassword: "zq123456"
repmgrDatabase: ""
existingSecret: ""
ldap:
bindpw: ""
existingSecret: ""
pgpool:
adminUsername: ""
adminPassword: "zq123456"
existingSecret: ""
compatibility:
openshift:
adaptSecurityContext: auto
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: 17.2.0-debian-12-r1
digest: ""
pullPolicy: IfNotPresent
pullSecrets: []
debug: false
labels: {}
podLabels: {}
serviceAnnotations: {}
replicaCount: 3
updateStrategy:
type: RollingUpdate
containerPorts:
postgresql: 5432
automountServiceAccountToken: false
hostAliases: []
hostNetwork: false
hostIPC: false
podAnnotations: {}
podAffinityPreset: ""
podAntiAffinityPreset: soft
nodeAffinityPreset:
type: ""
key: ""
values: []
affinity: {}
nodeSelector: {}
tolerations: []
topologySpreadConstraints: []
priorityClassName: ""
schedulerName: ""
terminationGracePeriodSeconds: ""
podSecurityContext:
enabled: true
fsGroupChangePolicy: Always
sysctls: []
supplementalGroups: []
fsGroup: 1001
containerSecurityContext:
enabled: true
seLinuxOptions: {}
runAsUser: 1001
runAsGroup: 1001
runAsNonRoot: true
privileged: false
readOnlyRootFilesystem: true
allowPrivilegeEscalation: false
capabilities:
drop: ["ALL"]
seccompProfile:
type: "RuntimeDefault"
command: []
args: []
lifecycleHooks: {}
extraEnvVars: []
extraEnvVarsCM: ""
extraEnvVarsSecret: ""
extraVolumes: []
extraVolumeMounts: []
initContainers: []
sidecars: []
resourcesPreset: "micro"
resources: {}
podManagementPolicy: Parallel
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: {}
networkPolicy:
enabled: true
allowExternal: true
allowExternalEgress: true
extraIngress: []
extraEgress: []
ingressNSMatchLabels: {}
ingressNSPodMatchLabels: {}
pdb:
create: true
minAvailable: ""
maxUnavailable: ""
username: postgres
password: ""
database: ""
existingSecret: ""
postgresPassword: ""
usePasswordFile: ""
repmgrUsePassfile: ""
repmgrPassfilePath: ""
upgradeRepmgrExtension: false
pgHbaTrustAll: false
syncReplication: false
syncReplicationMode: ""
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: ""
logTimezone: ""
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: ""
preStopDelayAfterPgStopSeconds: 25
headlessWithNotReadyAddresses: false
witness:
create: true
labels: {}
podLabels: {}
replicaCount: 1
updateStrategy:
type: RollingUpdate
containerPorts:
postgresql: 5432
automountServiceAccountToken: false
hostAliases: []
hostNetwork: false
hostIPC: false
podAnnotations: {}
podAffinityPreset: ""
podAntiAffinityPreset: soft
nodeAffinityPreset:
type: ""
key: ""
values: []
affinity: {}
nodeSelector: {}
tolerations: []
topologySpreadConstraints: []
priorityClassName: ""
schedulerName: ""
terminationGracePeriodSeconds: ""
podSecurityContext:
enabled: true
fsGroupChangePolicy: Always
sysctls: []
supplementalGroups: []
fsGroup: 1001
containerSecurityContext:
enabled: true
seLinuxOptions: {}
runAsUser: 1001
runAsGroup: 1001
runAsNonRoot: true
privileged: false
readOnlyRootFilesystem: true
allowPrivilegeEscalation: false
capabilities:
drop: ["ALL"]
seccompProfile:
type: "RuntimeDefault"
command: []
args: []
lifecycleHooks: {}
extraEnvVars: []
extraEnvVarsCM: ""
extraEnvVarsSecret: ""
extraVolumes: []
extraVolumeMounts: []
initContainers: []
sidecars: []
resourcesPreset: "micro"
resources: {}
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: true
minAvailable: ""
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: ""
logTimezone: ""
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.5.5-debian-12-r0
digest: ""
pullPolicy: IfNotPresent
pullSecrets: []
debug: false
customUsers:
usernames: ""
passwords: ""
automountServiceAccountToken: false
hostAliases: []
customUsersSecret: ""
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
fsGroupChangePolicy: Always
sysctls: []
supplementalGroups: []
fsGroup: 1001
containerSecurityContext:
enabled: true
seLinuxOptions: {}
runAsUser: 1001
runAsGroup: 1001
runAsNonRoot: true
privileged: false
readOnlyRootFilesystem: true
allowPrivilegeEscalation: false
capabilities:
drop: ["ALL"]
seccompProfile:
type: "RuntimeDefault"
resourcesPreset: "micro"
resources: {}
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
networkPolicy:
enabled: true
allowExternal: true
allowExternalEgress: true
extraIngress: []
extraEgress: []
ingressNSMatchLabels: {}
ingressNSPodMatchLabels: {}
pdb:
create: true
minAvailable: ""
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
disableLoadBalancingOnWrite: 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: true
name: ""
annotations: {}
automountServiceAccountToken: false
psp:
create: false
metrics:
enabled: false
image:
registry: docker.io
repository: bitnami/postgres-exporter
tag: 0.16.0-debian-12-r1
digest: ""
pullPolicy: IfNotPresent
pullSecrets: []
debug: false
podSecurityContext:
enabled: true
seLinuxOptions: {}
runAsUser: 1001
runAsGroup: 1001
runAsNonRoot: true
seccompProfile:
type: RuntimeDefault
resourcesPreset: "nano"
resources: {}
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:
enabled: true
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/os-shell
tag: 12-debian-12-r33
digest: ""
pullPolicy: IfNotPresent
pullSecrets: []
podSecurityContext:
enabled: true
seLinuxOptions: {}
runAsUser: 0
runAsGroup: 0
runAsNonRoot: false
seccompProfile:
type: RuntimeDefault
resourcesPreset: "nano"
resources: {}
persistence:
enabled: true
existingClaim: ""
storageClass: ""
mountPath: /bitnami/postgresql
accessModes:
- ReadWriteOnce
size: 8Gi
annotations: {}
labels: {}
selector: {}
persistentVolumeClaimRetentionPolicy:
enabled: false
whenScaled: Retain
whenDeleted: Retain
service:
type: ClusterIP
ports:
postgresql: 5432
portName: postgresql
nodePorts:
postgresql: ""
loadBalancerIP: ""
loadBalancerSourceRanges: []
clusterIP: ""
externalTrafficPolicy: Cluster
extraPorts: []
sessionAffinity: "None"
sessionAffinityConfig: {}
annotations: {}
serviceLabels: {}
headless:
annotations: {}
backup:
enabled: false
cronjob:
schedule: "@daily"
timeZone: ""
concurrencyPolicy: Allow
failedJobsHistoryLimit: 1
successfulJobsHistoryLimit: 3
startingDeadlineSeconds: ""
ttlSecondsAfterFinished: ""
restartPolicy: OnFailure
podSecurityContext:
enabled: true
fsGroupChangePolicy: Always
sysctls: []
supplementalGroups: []
fsGroup: 1001
containerSecurityContext:
enabled: true
seLinuxOptions: {}
runAsUser: 1001
runAsGroup: 1001
runAsNonRoot: true
allowPrivilegeEscalation: false
readOnlyRootFilesystem: true
seccompProfile:
type: RuntimeDefault
capabilities:
drop:
- ALL
command:
- /bin/sh
- -c
- "pg_dumpall --clean --if-exists --load-via-partition-root --quote-all-identifiers --no-password --file=${PGDUMP_DIR}/pg_dumpall-$(date '+%Y-%m-%d-%H-%M').pgdump"
labels: {}
annotations: {}
nodeSelector: {}
tolerations: []
extraEnvVars: []
extraEnvVarsCM: ""
extraEnvVarsSecret: ""
extraVolumes: []
extraVolumeMounts: []
storage:
existingClaim: ""
resourcePolicy: ""
storageClass: ""
accessModes:
- ReadWriteOnce
size: 8Gi
annotations: {}
mountPath: /backup/pgdump
subPath: ""
volumeClaimTemplates:
selector: {}
3、安装pg
[root@k8s-master01 postgresql-ha]# helm install pg -n helm-test .
4、查看资源
# 查看pod
[root@k8s-master01 postgresql-ha]# kgp -n helm-test
NAME READY STATUS RESTARTS AGE
pg-postgresql-ha-pgpool-68c9b947c9-t6tsn 1/1 Running 0 3m21s
pg-postgresql-ha-postgresql-0 1/1 Running 0 3m21s
pg-postgresql-ha-postgresql-1 1/1 Running 1 (2m48s ago) 3m21s
pg-postgresql-ha-postgresql-2 1/1 Running 1 (36s ago) 3m21s
pg-postgresql-ha-postgresql-witness-0 1/1 Running 0 3m21s
# 查看pvc
[root@k8s-master01 postgresql-ha]# kg pvc -n helm-test
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS VOLUMEATTRIBUTESCLASS AGE
data-pg-postgresql-ha-postgresql-0 Bound pvc-44c5bd13-9a37-4a7a-8d15-f5a2351e7925 8Gi RWO nfs-csi <unset> 28s
data-pg-postgresql-ha-postgresql-1 Bound pvc-2dbad1ed-1d02-482c-9cd7-6d008841932a 8Gi RWO nfs-csi <unset> 28s
data-pg-postgresql-ha-postgresql-2 Bound pvc-85f879e8-345f-4dc3-a791-b4f5540f3e34 8Gi RWO nfs-csi <unset> 28s
data-pg-postgresql-ha-postgresql-witness-0 Bound pvc-d40384f9-9228-48bc-bce9-b9afea012fcb 8Gi RWO nfs-csi <unset> 28s
5、登录测试
[root@k8s-master01 postgresql-ha]# k exec -it pg-postgresql-ha-postgresql-0 -n helm-test -- bash
I have no name!@pg-postgresql-ha-postgresql-0:/$ psql -h pg-postgresql-ha-pgpool -U postgres
Password for user postgres: zq123456
postgres=# create database mydatabase;
postgres=# \c mydatabase
mydatabase=#
create table test (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT,
department VARCHAR(50)
);
mydatabase=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+----------------------------------
id | integer | | not null | nextval('test_id_seq'::regclass)
name | character varying(100) | | |
age | integer | | |
department | character varying(50) | | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
查看集群的状态
[root@k8s-master01 postgresql-ha]# k exec -it pg-postgresql-ha-postgresql-0 -n helm-test -- bash
I have no name!@pg-postgresql-ha-postgresql-0:/$ /opt/bitnami/scripts/postgresql-repmgr/entrypoint.sh repmgr -f /opt/bitnami/repmgr/conf/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
------+---------------------------------------+---------+-----------+-------------------------------+----------+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1000 | pg-postgresql-ha-postgresql-0 | primary | * running | | default | 100 | 1 | user=repmgr password=zq123456 host=pg-postgresql-ha-postgresql-0.pg-postgresql-ha-postgresql-headless.helm-test.svc.cluster.local dbname=repmgr port=5432 connect_timeout=5
1001 | pg-postgresql-ha-postgresql-1 | standby | running | pg-postgresql-ha-postgresql-0 | default | 100 | 1 | user=repmgr password=zq123456 host=pg-postgresql-ha-postgresql-1.pg-postgresql-ha-postgresql-headless.helm-test.svc.cluster.local dbname=repmgr port=5432 connect_timeout=5
1002 | pg-postgresql-ha-postgresql-2 | standby | running | pg-postgresql-ha-postgresql-0 | default | 100 | 1 | user=repmgr password=zq123456 host=pg-postgresql-ha-postgresql-2.pg-postgresql-ha-postgresql-headless.helm-test.svc.cluster.local dbname=repmgr port=5432 connect_timeout=5
2000 | pg-postgresql-ha-postgresql-witness-0 | witness | * running | pg-postgresql-ha-postgresql-0 | default | 0 | n/a | user=repmgr password=zq123456 host=pg-postgresql-ha-postgresql-witness-0.pg-postgresql-ha-postgresql-witness.helm-test.svc.cluster.local dbname=repmgr port=5432 connect_timeout=5
6、故障转移模拟测试
# 手动关闭主节点（触发故障转移）
[root@k8s-master01 postgresql-ha]# kubectl delete pod pg-postgresql-ha-postgresql-0 -n helm-test
# 观察到1001变为主
[root@k8s-master01 postgresql-ha]# kubectl exec -it pg-postgresql-ha-postgresql-1 -n helm-test -- \
/opt/bitnami/scripts/postgresql-repmgr/entrypoint.sh repmgr -f /opt/bitnami/repmgr/conf/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
------+---------------------------------------+---------+-----------+-------------------------------+----------+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1000 | pg-postgresql-ha-postgresql-0 | primary | - failed | ? | default | 100 | | user=repmgr password=zq123456 host=pg-postgresql-ha-postgresql-0.pg-postgresql-ha-postgresql-headless.helm-test.svc.cluster.local dbname=repmgr port=5432 connect_timeout=5
1001 | pg-postgresql-ha-postgresql-1 | primary | * running | | default | 100 | 2 | user=repmgr password=zq123456 host=pg-postgresql-ha-postgresql-1.pg-postgresql-ha-postgresql-headless.helm-test.svc.cluster.local dbname=repmgr port=5432 connect_timeout=5
1002 | pg-postgresql-ha-postgresql-2 | standby | running | pg-postgresql-ha-postgresql-1 | default | 100 | 2 | user=repmgr password=zq123456 host=pg-postgresql-ha-postgresql-2.pg-postgresql-ha-postgresql-headless.helm-test.svc.cluster.local dbname=repmgr port=5432 connect_timeout=5
2000 | pg-postgresql-ha-postgresql-witness-0 | witness | * running | pg-postgresql-ha-postgresql-1 | default | 0 | n/a | user=repmgr password=zq123456 host=pg-postgresql-ha-postgresql-witness-0.pg-postgresql-ha-postgresql-witness.helm-test.svc.cluster.local dbname=repmgr port=5432 connect_timeout=5
7、环境复原
[root@k8s-master01 postgresql-ha]# helm delete pg -n helm-test
# 注意：要等pod删除后，再删除pvc
[root@k8s-master01 postgresql-ha]# kubectl delete pvc data-pg-postgresql-ha-postgresql-0 data-pg-postgresql-ha-postgresql-1 data-pg-postgresql-ha-postgresql-2 data-pg-postgresql-ha-postgresql-witness-0 -n helm-test