安装KubeDB
使用KubeDB安装PostgresSQL数据库
安装KubeDB
获取License
- 登录AppsCode License Server，输入名字和邮箱，选择社区版本
- 使用
kubectl get ns kube-system -o=jsonpath='{.metadata.uid}'命令获取要部署的KubeDB所在K8s集群的uid，获取后填入到网页上，然后点击提交，邮箱即可收到一封License邮件，将license文件保存为license.txt，后面helm安装KubeDB时候需要用到
使用helm部署KubeDB
# 添加仓库
helm repo add appscode https://charts.appscode.com/stable/
# 更新仓库
helm repo update
# 安装KubeDB
helm install kubedb appscode/kubedb \
--version v2023.06.19 \
--namespace kubedb --create-namespace \
--set-file global.license=/path/to/the/license.txt
# 安装好后查看运行的容器
[root@master ~]# kubectl get pods --all-namespaces -l "app.kubernetes.io/instance=kubedb"
NAMESPACE NAME READY STATUS RESTARTS AGE
kubedb kubedb-kubedb-provisioner-7b67fd5fcb-8pdqp 1/1 Running 0 49m
kubedb kubedb-kubedb-webhook-server-6b9f98979-k2gbm 1/1 Running 0 49m
使用KubeDB部署PostgreSQL的流复制主从模式
创建Postgres集群初始化使用的密码Secret，后面用这个密码连接数据库
# POSTGRES_USER后面指定用户名
# POSTGRES_PASSWORD 后面指定密码
kubectl create secret generic p1-auth -n demo \
--from-literal=POSTGRES_USER=postgres \
--from-literal=POSTGRES_PASSWORD=mypassword
# 查看Secret
[root@master ~]# kubectl get secret -n demo p1-auth -o yaml
apiVersion: v1
data:
POSTGRES_PASSWORD: k89kNkb614=
POSTGRES_USER: cG9zdGdyZXM=
password: k89kNkb614=
username: cG9zdGdyZXM=
kind: Secret
metadata:
creationTimestamp: "2023-07-20T23:06:04Z"
name: p1-auth
namespace: demo
resourceVersion: "6274053"
uid: e772fbuyu5-c7dd-4f99-b478-df0f00985
type: Opaque
创建命名空间
kubedb社区版本必须使用demo名称空间
kind: Namespace
apiVersion: v1
metadata:
name: demo
labels:
type: kubedb
使用到的yaml文件
# hot-postgres.yaml
apiVersion: kubedb.com/v1alpha2
kind: Postgres
metadata:
name: hot-postgres
namespace: demo
spec:
version: "13.2"
replicas: 3
standbyMode: Hot # 开启hot standby 模式
storageType: Durable
authSecret:
name: p1-auth # 初始化集群时候使用前面创建的Secret
storage:
storageClassName: "rook-ceph-block"
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
# 应用yaml文件
kubectl apply -f hot-postgres.yaml
查看集群部署情况
[root@master ~]# kubectl get pods -n demo --selector="app.kubernetes.io/instance=hot-postgres" --show-labels
NAME READY STATUS RESTARTS AGE LABELS
hot-postgres-0 2/2 Running 0 14m app.kubernetes.io/component=database,app.kubernetes.io/instance=hot-postgres,app.kubernetes.io/managed-by=kubedb.com,app.kubernetes.io/name=postgreses.kubedb.com,controller-revision-hash=hot-postgres-69755c59f4,kubedb.com/role=primary,statefulset.kubernetes.io/pod-name=hot-postgres-0
hot-postgres-1 2/2 Running 0 13m app.kubernetes.io/component=database,app.kubernetes.io/instance=hot-postgres,app.kubernetes.io/managed-by=kubedb.com,app.kubernetes.io/name=postgreses.kubedb.com,controller-revision-hash=hot-postgres-69755c59f4,kubedb.com/role=standby,statefulset.kubernetes.io/pod-name=hot-postgres-1
hot-postgres-2 2/2 Running 0 13m app.kubernetes.io/component=database,app.kubernetes.io/instance=hot-postgres,app.kubernetes.io/managed-by=kubedb.com,app.kubernetes.io/name=postgreses.kubedb.com,controller-revision-hash=hot-postgres-69755c59f4,kubedb.com/role=standby,statefulset.kubernetes.io/pod-name=hot-postgres-2
# 说明
# hot-postgres-0是primary节点，该pod上有kubedb.com/role=primary标签
# hot-postgres-1和hot-postgres-2是standby节点，这些pod上有kubedb.com/role=replica标签
登录服务器可以查看PostgreSQL的节点连接情况
# 使用sql查询
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-------+----------+----------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------------+-----------------+-----------------+---------------+------------+-------------------------------
67749 | 10 | postgres | hot-postgres-2 | 10.244.4.14 | | 46434 | 2023-07-21 21:23:10.631716+00 | | streaming | 0/7EAE000 | 0/7EAE000 | 0/7EAE000 | 0/7EACFD0 | 00:00:00.191948 | 00:00:00.742546 | 00:00:01.229254 | 0 | async | 2023-07-21 23:36:04.956438+00
23629 | 10 | postgres | hot-postgres-1 | 10.244.5.154 | | 40646 | 2023-07-21 17:35:26.066605+00 | | streaming | 0/7EAE000 | 0/7EAE000 | 0/7EACFD0 | 0/7EACFD0 | 00:00:00.521492 | 00:00:01.008271 | 00:00:01.008271 | 0 | async | 2023-07-21 23:36:04.848909+00
(2 行记录)
测试连接情况
# 查看service
[root@master ~]# kubectl get service -n demo
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
hot-postgres ClusterIP 10.106.82.137 <none> 5432/TCP,2379/TCP 18m
hot-postgres-pods ClusterIP None <none> 5432/TCP,2380/TCP,2379/TCP 18m
hot-postgres-standby ClusterIP 10.97.213.98 <none> 5432/TCP 18m
primary连接测试
# primary连接测试，primary可以插入数据
[root@master ~]# psql -h 10.106.82.137 -U postgres -W
Password:
psql (15.3, server 13.2)
Type "help" for help.
ostgres=# create table mytable(id int);
CREATE TABLE
postgres=# insert into mytable values(10);
INSERT 0 1
postgres=# insert into mytable values(20);
INSERT 0 1
postgres=# select * from mytable ;
id
----
10
20
(2 rows)
standby连接测试
# standby连接测试,standby为只读的，只能查询，没法插入数据
[root@master ~]# psql -h 10.97.213.98 -U postgres -W
Password:
psql (15.3, server 13.2)
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------+-------+----------
public | kubedb_write_check | table | postgres
public | mytable | table | postgres
(2 rows)
postgres=# select * from mytable ;
id
----
10
20
(2 rows)
postgres=# insert into mytable values (30);
ERROR: cannot execute INSERT in a read-only transaction
部署 Pgpool 作为负载均衡器，自动实现读写分离
架构图
前面已经部署好primary service和standby service， 因此这里部署pgpool即可
pgpool 部署使用的yaml文件
# pgpool-deploy.yml
apiVersion: apps/v1
kind: Deployment
metadata:
name: pgpool
spec:
replicas: 1
selector:
matchLabels:
app: pgpool
template:
metadata:
labels:
app: pgpool
spec:
containers:
- name: pgpool
imagePullPolicy: IfNotPresent
image: pgpool/pgpool
env:
- name: POSTGRES_USERNAME # postgres连接的用户名，前面创建的集群时候使用的用户名
valueFrom:
secretKeyRef:
name: mypostgres-postgres-secret
key: username
- name: POSTGRES_PASSWORD # postgres连接的密码，前面创建的集群时候使用的密码
valueFrom:
secretKeyRef:
name: mypostgres-postgres-secret
key: password
- name: PGPOOL_PASSWORD_ENCRYPTION_METHOD
value: "scram-sha-256"
- name: PGPOOL_ENABLE_POOL_PASSWD
value: "true"
- name: PGPOOL_SKIP_PASSWORD_ENCRYPTION
value: "false"
# The following settings are not required when not using the Pgpool-II PCP command.
# To enable the following settings, you must define a secret that stores the PCP user's
# username and password.
- name: PGPOOL_PCP_USER
valueFrom:
secretKeyRef:
name: pgpool-pcp-secret
key: username
- name: PGPOOL_PCP_PASSWORD
valueFrom:
secretKeyRef:
name: pgpool-pcp-secret
key: password
volumeMounts:
- name: pgpool-config # 挂载pgpool的配置文件到指定路径
mountPath: /config
volumes:
- name: pgpool-config
configMap:
name: pgpool-config # 使用ConfigMap格式的pgpool配置文件
---
apiVersion: v1
kind: Service
metadata:
name: pgpool-service
spec:
type: NodePort
selector:
app: pgpool
ports:
- name: pgpool-port
port: 9999
targetPort: 9999
nodePort: 30000 # 创建pgpool服务，监听在30000端口
在pgpool-deploy.yml定义了PostgreSQL 和 Pgpool PCP的凭证Secret，因此需要提前创建，
# 这里的username需要设置为前面创建的连接PostgreSQL集群的用户名，
# password为连接集群的密码
kubectl create secret generic mypostgres-postgres-secret --from-literal=username=postgres --from-literal=password=some_postgres_pass -n demo
# 下面的这个是连接pgpool进行管理使用的用户名和密码，根据自己需要创建
kubectl create secret generic pgpool-pcp-secret --from-literal=username=postgres --from-literal=password=postgres -n demo
# 说明：
# pgpool-deploy.yml将会从上面创建的Secret中获取值，并且赋值给pgool容器的这些变量上POSTGRES_USERNAME, POSTGRES_PASSWORD, PGPOOL_PCP_USER, PGPOOL_PCP_PASSWORD
pgpool ConfigMap 文件
pgpool的配置文件，以ConfigMap形式保存起来了
apiVersion: v1
kind: ConfigMap
metadata:
name: pgpool-config
labels:
name: pgpool-config
data:
pgpool.conf: |-
listen_addresses = '*'
port = 9999
socket_dir = '/var/run/pgpool'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/pgpool'
backend_hostname0 = 'hot-postgres.demo' # 这里修改为提供primary的service的名字
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'
backend_hostname1 = 'hot-postgres-standby.demo' # 这里修改为提供standby服务的service的名字
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'DISALLOW_TO_FAILOVER'
sr_check_period = 0
sr_check_user = 'postgres'
health_check_period = 0
health_check_user = 'postgres'
backend_clustering_mode = 'streaming_replication'
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
connection_cache = on
load_balance_mode = on
ssl = off # 关闭了ssl
enable_pool_hba = off
failover_on_backend_error = off
log_min_messages = warning
statement_level_load_balance = on
log_statement = on
log_per_node_statement = on
开始部署
kubectl create secret generic mypostgres-postgres-secret --from-literal=username=postgres --from-literal=password=some_postgres_pass -n demo
kubectl create secret generic pgpool-pcp-secret --from-literal=username=pgtool --from-literal=password=postgres -n demo
kubectl apply -f pgpool-configmap.yml -n demo
kubectl apply -f pgpool-deploy.yml -n demo
查看运行的pgpool
[root@master ~]# kubectl get pods -n demo
NAME READY STATUS RESTARTS AGE
hot-postgres-0 2/2 Running 0 13h
hot-postgres-1 2/2 Running 0 13h
hot-postgres-2 2/2 Running 0 12h
pgpool-54944fb497-9mzfd 1/1 Running 0 3h34m
连接测试
查看service
# 查看service
[root@master ~]# kubectl get service -n demo
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
hot-postgres ClusterIP 10.106.82.137 <none> 5432/TCP,2379/TCP 13h
hot-postgres-pods ClusterIP None <none> 5432/TCP,2380/TCP,2379/TCP 13h
hot-postgres-standby ClusterIP 10.97.213.98 <none> 5432/TCP 13h
pgpool-service NodePort 10.108.10.51 <none> 9999:30000/TCP 3h40m
连接postgres
➜ ~ psql -d postgres -p 30000 -h K8s服务器集群任意IP -U postgres
用户 postgres 的口令：
psql (11.18 (Deepin 11.18-0+deb10u1), 服务器 13.2)
警告：psql 主版本11,服务器主版本为13.
一些psql功能可能无法正常使用.
输入 "help" 来获取帮助信息.
postgres=# \dt
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+---------+--------+----------
public | mytable | 数据表 | postgres
(1 行记录)
postgres=# insert into mytable values(10);
INSERT 0 1
postgres=# select * from mytable;
id
----
10
(1 行记录)
postgres=#
