Persistent PostgreSQL with Ceph and Kubernetes
1. Prerequisites
Before deploying PostgreSQL, ensure the following conditions are met:
- A Ceph cluster is fully deployed with
RBD storage pools configured(e.g., kubernetes, postgresql). - The Ceph
CSI pluginis properly deployed in the Kubernetes cluster. - Kubernetes cluster version is ≥
1.20. - A Ceph client user
client.postgresis created and granted access to the required pools. - Network connectivity between Ceph OSD nodes and Kubernetes worker nodes is properly configured.
2. Expected Results for PostgreSQL Deployment with Ceph RBD
2.1 PostgreSQL instance is successfully deployed in the Kubernetes cluster
- Deployed using a Helm chart or custom YAML manifests
- Pod is running with status
Running - The required PersistentVolumeClaim (PVC) is successfully bound to a PersistentVolume (PV) provisioned by Ceph RBD
2.2 PostgreSQL data is persistently stored in the Ceph cluster
- The PVC used by PostgreSQL is dynamically provisioned through the Ceph CSI plugin
- Data is actually stored in the Ceph RBD pool (e.g.,
postgresqlpool) - Data remains intact even if the PostgreSQL Pod is restarted or rescheduled
2.3 The corresponding RBD image is visible in the Ceph cluster
- The command
rbd ls postgresqlshows the RBD image created by the PostgreSQL PVC - The image is in a healthy state, and its size matches the allocated storage
2.4 Ceph user permissions are correctly configured
- The
client.kubernetesuser hasrwx(read-write-execute) permissions on both thepostgresqlandkubernetespools - Unauthorized users cannot access these pools or their corresponding RBD images
2.5 The system supports high availability and disaster recovery
- Ceph RBD is configured with triple replication to ensure data safety
- Kubernetes can automatically reschedule the PostgreSQL Pod to another node if a failure occurs
- The new Pod can access the same data, ensuring minimal or no service interruption
2.6 Performance and stability meet production expectations
- PostgreSQL runs stably without I/O errors
- The Ceph backend provides sufficient performance to support database workloads
- The network is stable and allows reliable communication between the CSI plugin and Ceph
Part 1: Creating Ceph RBD Pools and User Authorization
In this section, we will walk through the process of creating the required RBD pools in Ceph and granting the necessary permissions to a Ceph client user for seamless integration with Kubernetes and PostgreSQL.
1.1 Create RBD Pools
To begin, you need to create the RBD pools that will be used by Ceph for storing data. You can create pools for Kubernetes and PostgreSQL by using the following commands.
# For example, create a Ceph pool dedicated to PostgreSQL data.
root@ceph-admin-120:~# ceph osd pool create postgresql 128 128
root@ceph-admin-120:~# rbd pool init postgresql
1.2 Create and Authorize Ceph Client for PostgreSQL Pool
root@ceph-admin-120:~# ceph auth del client.postgres
root@ceph-admin-120:~# ceph auth get-or-create client.postgres mon 'profile rbd' osd 'profile rbd pool=postgresql'
This command creates or retrieves the client.postgres authentication entry, granting it the mon (monitor) access profile for RBD and osd (object storage daemon) access for the postgresql pool.
Output:
[client.postgres]
key = AQD5BRto284gEhAAZn7GUaiqC6JPzbIKQLXvAw==
This is the secret key generated for client.postgres.
root@ceph-admin-120:~# ceph auth get client.postgres
This command retrieves the details of the client.postgres user.
Output:
[client.postgres]
key = AQD5BRto284gEhAAZn7GUaiqC6JPzbIKQLXvAw==
caps mon = "profile rbd"
caps osd = "profile rbd pool=postgresql"
1.3 Store Kubernetes Access to RBD User Information in a Secret Resource
Create a secret resource to store the authentication user information for the CSI client to connect to RBD. Since the user’s key is stored using stringData, the key does not need to be Base64-encoded.
root@master:~/helm/postgresql/ceph# cat postgres-rbd-secret.yaml
apiVersion: v1
kind: Secret
metadata:
name: postgres-rbd-secret
namespace: postgres
stringData:
userID: postgres
userKey: AQD5BRto284gEhAAZn7GUaiqC6JPzbIKQLXvAw==
1.4. storageclass-rbd.yaml (Define StorageClass)
In this step, you will define a StorageClass in Kubernetes to configure the use of Ceph RBD as a persistent storage provider.
root@master:~/helm/postgresql/ceph# cat rbd-postgresql-storageclass.yaml
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
name: rbd-postgresql-storage
provisioner: rbd.csi.ceph.com # Name of the CSI driver
parameters:
clusterID: 091a4f92-1cc4-11f0-9f6e-27ecaf2da6bd # Ceph cluster ID
pool: postgresql # Pool where the block storage device is located
adminId: postgres # Key addition: specify the use of the client.kubernetes user
csi.storage.k8s.io/provisioner-secret-name: postgres-rbd-secret # The secret containing authentication credentials for the CSI client to connect to Ceph
csi.storage.k8s.io/provisioner-secret-namespace: postgres # The namespace where the secret resource is located
csi.storage.k8s.io/node-stage-secret-name: postgres-rbd-secret # The node also needs authentication info for mounting the block storage
csi.storage.k8s.io/node-stage-secret-namespace: postgres # The namespace where the secret resource is located
imageFormat: "2" # Specifies the feature ID for the RBD block device, default is 1
imageFeatures: "layering" # Specifies the block device feature type
reclaimPolicy: Delete # Reclaim policy set to Delete, meaning the block device file will be deleted when the PVC is deleted
mountOptions:
- discard
Part 2: Deploying PostgreSQL Database
2.1 postgresql-secret.yaml (Create Storage Access Secret)
root@master:~/helm/postgresql/ceph# cat postgresql-secret.yaml
apiVersion: v1
kind: Secret
metadata:
name: postgresql-secret
namespace: database-monitoring
type: Opaque
data:
POSTGRES_USER: cG9zdGdyZXM= # 'postgres' base64
POSTGRES_PASSWORD: cGFzc3dvcmQxMjM= # 'password123' base64
Optional: Local generation method 👇
echo -n 'postgres' | base64 # Username
echo -n 'password123' | base64 # Password
2.2 postgresql-service.yaml (Create External Access Service)
root@master:~/helm/postgresql/ceph# cat postgresql-service.yaml
---
apiVersion: v1
kind: Service
metadata:
name: postgresql
namespace: database-monitoring
labels:
app: postgresql
spec:
ports:
- port: 5432
targetPort: 5432
selector:
app: postgresql
2.3 postgresql-statefulset.yaml (Deploy PostgreSQL High Availability)
root@master:~/helm/postgresql/ceph# cat postgresql-statefulset.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: postgresql
namespace: database-monitoring
spec:
serviceName: "postgresql"
replicas: 3
selector:
matchLabels:
app: postgresql
template:
metadata:
labels:
app: postgresql
spec:
initContainers:
- name: fix-permissions
image: alpine
command: ["sh", "-c", "mkdir -p /var/lib/postgresql/data && chown -R 1001:1001 /var/lib/postgresql/data"]
volumeMounts:
- name: postgresql-storage
mountPath: /var/lib/postgresql/data
subPath: postgres
containers:
- name: postgresql
image: postgres:13
ports:
- containerPort: 5432
env:
- name: POSTGRES_USER
valueFrom:
secretKeyRef:
name: postgresql-secret
key: POSTGRES_USER
- name: POSTGRES_PASSWORD
valueFrom:
secretKeyRef:
name: postgresql-secret
key: POSTGRES_PASSWORD
- name: POSTGRES_DB
value: "postgres"
volumeMounts:
- name: postgresql-storage
mountPath: /var/lib/postgresql/data
subPath: postgres
volumeClaimTemplates:
- metadata:
name: postgresql-storage
spec:
accessModes: ["ReadWriteOnce"]
storageClassName: "rbd-postgresql-storage"
resources:
requests:
storage: 10Gi
Part 3: Verifying Database Functionality
3.1 Verify the PostgreSQL Pods
Check the status of the PostgreSQL StatefulSet pods to ensure that all replicas are running properly.
root@master:~# kubectl get pods -n database-monitoring
NAME READY STATUS RESTARTS AGE
postgresql-0 1/1 Running 0 26m
postgresql-1 1/1 Running 0 26m
postgresql-2 1/1 Running 0 25m
3.2 Check PostgreSQL Service
Verify the service is exposed correctly and can be accessed:
oot@master:~# kubectl -n database-monitoring get svc
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
postgresql ClusterIP 10.103.19.113 <none> 5432/TCP 44m
3.3 Connect to PostgreSQL Pod
Use kubectl exec to connect to one of the PostgreSQL pods and check if PostgreSQL is accepting connections.
root@master:~/helm/postgresql/ceph# kubectl -n database-monitoring exec -it postgresql-0 -- psql -U postgres -d postgres
Defaulted container "postgresql" out of: postgresql, fix-permissions (init)
psql (13.20 (Debian 13.20-1.pgdg120+1))
Type "help" for help.
postgres=#
3.4 Run Basic SQL Queries
Once connected, run a few basic SQL queries to verify the database is working as expected.
postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=#
postgres=#
postgres=# CREATE TABLE hello_world
(
region text,
country text,
year int,
production int,
consumption int
);
CREATE TABLE
postgres=#
postgres=#
postgres=# INSERT INTO hello_world (region, country, year, production, consumption) VALUES ('America', 'USA', 1998, 2014, 12897);
INSERT 0 1
postgres=#
postgres=# select * from hello_world;
region | country | year | production | consumption
---------+---------+------+------------+-------------
America | USA | 1998 | 2014 | 12897
(1 row)
3.5 Check Persistent Storage
root@master:~# kubectl -n database-monitoring logs postgresql-2
Defaulted container "postgresql" out of: postgresql, fix-permissions (init)
PostgreSQL Database directory appears to contain a database; Skipping initialization
2025-05-07 08:03:29.635 UTC [1] LOG: starting PostgreSQL 13.20 (Debian 13.20-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2025-05-07 08:03:29.636 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2025-05-07 08:03:29.637 UTC [1] LOG: listening on IPv6 address "::", port 5432
2025-05-07 08:03:29.657 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2025-05-07 08:03:29.701 UTC [27] LOG: database system was shut down at 2025-05-07 08:02:53 UTC
2025-05-07 08:03:29.745 UTC [1] LOG: database system is ready to accept connections
root@master:~#
Part 4: Verifying Data Persistence
root@ceph-admin-120:~# rdb -p postgresql ls
Command 'rdb' not found, but there are 24 similar ones.
root@ceph-admin-120:~# rbd -p postgresql ls
csi-vol-35a3e303-ecea-45e5-8630-a2f7590d88f8
csi-vol-5740c72a-6ab3-4100-bcec-654ff3384d2a
csi-vol-6e355712-54b7-45e3-aa0b-a75449d09ce0
root@ceph-admin-120:~#
root@ceph-admin-120:~#
root@ceph-admin-120:~# rbd info postgresql/csi-vol-35a3e303-ecea-45e5-8630-a2f7590d88f8
rbd image 'csi-vol-35a3e303-ecea-45e5-8630-a2f7590d88f8':
size 10 GiB in 2560 objects
order 22 (4 MiB objects)
snapshot_count: 0
id: 3c0cf2b09dc3c
block_name_prefix: rbd_data.3c0cf2b09dc3c
format: 2
features: layering
op_features:
flags:
create_timestamp: Wed May 7 07:46:56 2025
access_timestamp: Wed May 7 07:46:56 2025
modify_timestamp: Wed May 7 07:46:56 2025
root@ceph-admin-120:~#
root@ceph-admin-120:~#
root@ceph-admin-120:~# ceph osd map postgresql rbd_data.3c0cf2b09dc3c
osdmap e5391 pool 'postgresql' (37) object 'rbd_data.3c0cf2b09dc3c' -> pg 37.1c97d372 (37.12) -> up ([2,0,4], p2) acting ([2,0,4], p2)
root@ceph-admin-120:~#
root@node2:~# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
loop0 7:0 0 91.9M 1 loop /snap/lxd/32662
loop1 7:1 0 63.8M 1 loop /snap/core20/2496
loop3 7:3 0 91.9M 1 loop /snap/lxd/29619
loop4 7:4 0 44.4M 1 loop /snap/snapd/23545
loop5 7:5 0 50.9M 1 loop /snap/snapd/24505
loop6 7:6 0 63.8M 1 loop /snap/core20/2571
sda 8:0 0 20G 0 disk
├─sda1 8:1 0 1M 0 part
├─sda2 8:2 0 1.8G 0 part /boot
└─sda3 8:3 0 18.2G 0 part
└─ubuntu--vg-ubuntu--lv
253:0 0 18.2G 0 lvm /
rbd0 252:0 0 10G 0 disk /var/lib/kubelet/pods/aaacaf07-e8a3-43a8-bc8d-43c4584f90ab/volumes/kubernetes.io~csi/pvc-d395712c-545c-466c-b746-c50430f0fe1d/
rbd1 252:16 0 10G 0 disk /var/lib/kubelet/pods/697210ff-453c-46e1-aa31-4af43aeaa0cd/volumes/kubernetes.io~csi/pvc-832fc061-76cb-4ea9-a56f-8082f258d4bf/
root@node2:~# mkdir -p /mnt/rbd-postgresql
root@node2:~#
root@node2:~#
root@node2:~# mount /dev/rbd1 /mnt/rbd-postgresql
root@node2:~# ls /mnt/rbd-postgresql
lost+found postgres
root@node2:~# cd /mnt/rbd-postgresql
root@node2:/mnt/rbd-postgresql# ls
lost+found postgres
root@node2:/mnt/rbd-postgresql# cd postgres/
root@node2:/mnt/rbd-postgresql/postgres# ls
base pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.conf
global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_xact postmaster.opts
pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION postgresql.auto.conf postmaster.pid
root@node2:/mnt/rbd-postgresql/postgres# pwd
/mnt/rbd-postgresql/postgres
root@node2:/mnt/rbd-postgresql/postgres#
