Kubernetes/Postgres

How to deploy Postgres on Kubernetes | Refine.dev

How to deploy Postgres on Kubernetes | Refine
https://refine.dev/blog/postgres-on-kubernetes/#additional-commands-for-setting-up-the-container

Versions

docker start example

FROM postgres:latest
# or specific version:
FROM postgres:15

PVC

PV:

    apiVersion: v1
    kind: PersistentVolume
    metadata:
      name: postgres-pv
    spec:
      capacity:
        storage: 5Gi
      accessModes:
        - ReadWriteOnce
      hostPath:
        path: "/mnt/data"

PVC:

    apiVersion: v1
    kind: PersistentVolumeClaim
    metadata:
      name: postgres-pvc
    spec:
      accessModes:
        - ReadWriteOnce
      resources:
        requests:
          storage: 5Gi

kubectl apply -f [your-pv-file].yaml

Config Map

    apiVersion: v1
    kind: ConfigMap
    metadata:
      name: postgres-config
    data:
      postgresql.conf: |
        max_connections = 100
        shared_buffers = 256MB
        log_statement = 'all'

spec:
  containers:
  - name: postgres
    image: postgres:latest
    volumeMounts:
    - name: postgres-config-volume
      mountPath: /etc/postgresql
  volumes:
  - name: postgres-config-volume
    configMap:
      name: postgres-config
      items:
      - key: postgresql.conf
        path: postgresql.conf

Secrets

apiVersion: v1
kind: Secret
metadata:
  name: postgres-secret
type: Opaque
data:
  postgres-password: <base64-encoded-password>

echo -n 'yourpassword' | base64

Use Secret

spec:
  containers:
  - name: postgres
    image: postgres:latest
    env:
    - name: POSTGRES_PASSWORD
      valueFrom:
        secretKeyRef:
          name: postgres-secret
          key: postgres-password

Exec Into Pod

kubectl exec -it <pod-name> -- psql -U <username>

How to Deploy Postgres to Kubernetes Cluster | DigitalOcean

MANY instances version

How to Deploy Postgres to Kubernetes Cluster | DigitalOcean
https://www.digitalocean.com/community/tutorials/how-to-deploy-postgres-to-kubernetes-cluster

PVC

PV:

apiVersion: v1
kind: PersistentVolume
metadata:
  name: postgres-volume
  labels:
    type: local
    app: postgres
spec:
  storageClassName: manual
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteMany
  hostPath:
    path: /data/postgresql

NOTE: accessModes: ReadWriteMany - ReadWriteMany, allowing multiple Pods to read and write to the volume simultaneously.

PVC:

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-volume-claim
  labels:
    app: postgres
spec:
  storageClassName: manual
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 10Gi

kubectl apply -f psql-pv.yaml
kubectl apply -f psql-claim.yaml

Deployment

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  replicas: 3
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: 'postgres:14'
          imagePullPolicy: IfNotPresent
          ports:
            - containerPort: 5432
          envFrom:
            - configMapRef:
                name: postgres-secret
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgresdata
      volumes:
        - name: postgresdata
          persistentVolumeClaim:
            claimName: postgres-volume-claim

kubectl apply -f ps-deployment.yaml

Service

apiVersion: v1
kind: Service
metadata:
  name: postgres
  labels:
    app: postgres
spec:
  type: NodePort
  ports:
    - port: 5432
  selector:
    app: postgres

Exec In

kubectl exec -it postgres-665b7554dc-cddgq -- psql -h localhost -U ps_user --password -p 5432 ps_db

Scale

Increase to 5

kubectl scale deployment --replicas=5 postgres

Backup and Restore

kubectl exec -it postgres-665b7554dc-cddgq -- pg_dump -U ps_user -d ps_db > db_backup.sql

kubectl cp db_backup.sql postgres-665b7554dc-cddgq:/tmp/db_backup.sql

kubectl exec -it postgres-665b7554dc-cddgq -- /bin/bash

psql -U ps_user -d ps_db -f /tmp/db_backup.sql