Rizqi Mulki

Backend Developer

Deploying PostgreSQL on Kubernetes for Scalable Backends

—

by

Rizqi Mulki
in ,

Kubernetes has become the de facto standard for container orchestration, providing a robust platform for deploying stateful applications like PostgreSQL. This guide explores how to effectively deploy, manage, and scale PostgreSQL on Kubernetes to build resilient, high-performance database backends.

Why PostgreSQL on Kubernetes?

Deploying PostgreSQL on Kubernetes offers several advantages:

  • Infrastructure as code: Define your entire database infrastructure in version-controlled manifests
  • Automated operations: Leverage Kubernetes for automated failover, scaling, and self-healing
  • Resource efficiency: Optimize hardware utilization across your cluster
  • Consistent environments: Maintain identical database configurations across development, staging, and production
  • Simplified scaling: Scale your database infrastructure to meet changing demands

Deployment Options

There are three primary approaches to deploying PostgreSQL on Kubernetes:

1. StatefulSets with Persistent Volumes

The most basic approach uses Kubernetes StatefulSets with persistent volumes to manage PostgreSQL instances.

yamlapiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
spec:
  serviceName: "postgres"
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - name: postgres
        image: postgres:15
        env:
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-secrets
              key: password
        - name: PGDATA
          value: /var/lib/postgresql/data/pgdata
        ports:
        - containerPort: 5432
          name: postgres
        volumeMounts:
        - name: postgres-data
          mountPath: /var/lib/postgresql/data
  volumeClaimTemplates:
  - metadata:
      name: postgres-data
    spec:
      accessModes: ["ReadWriteOnce"]
      storageClassName: "standard"
      resources:
        requests:
          storage: 10Gi

2. PostgreSQL Operators

PostgreSQL operators provide higher-level abstractions for managing PostgreSQL clusters on Kubernetes. Popular options include:

  • Zalando Postgres Operator
  • Crunchy Data PGO
  • CloudNativePG

Example using CloudNativePG:

yamlapiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-cluster
spec:
  instances: 3
  primaryUpdateStrategy: unsupervised
  
  storage:
    size: 10Gi
    storageClass: standard
  
  bootstrap:
    initdb:
      database: app
      owner: app_user
      secret:
        name: app-user-secret
  
  backup:
    barmanObjectStore:
      destinationPath: "s3://my-backup-bucket/postgres"
      s3Credentials:
        accessKeyId:
          name: s3-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: s3-creds
          key: ACCESS_SECRET_KEY
      wal:
        compression: gzip
        maxParallel: 8

3. Database as a Service Integration

For managed PostgreSQL services that integrate with Kubernetes:

  • Azure Database for PostgreSQL with Azure Kubernetes Service (AKS)
  • Amazon RDS for PostgreSQL with EKS
  • Google Cloud SQL for PostgreSQL with GKE

High Availability Architecture

A well-designed high-availability PostgreSQL cluster on Kubernetes includes:

  1. Primary database: Handles both reads and writes
  2. Multiple replicas: Serve read queries and provide failover targets
  3. Connection pooling: Using PgBouncer or Pgpool-II
  4. Service endpoints: Separate read/write and read-only services
  5. Automated failover: Handled by the operator or custom controllers

Implementing Streaming Replication

Using the Zalando operator for a highly available setup:

yamlapiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: postgres-ha-cluster
spec:
  teamId: "data-engineering"
  numberOfInstances: 3
  postgresql:
    version: "15"
    parameters:
      shared_buffers: "1GB"
      max_connections: "500"
      work_mem: "16MB"
  volume:
    size: "20Gi"
    storageClass: "standard"
  patroni:
    initdb:
      encoding: "UTF8"
      locale: "en_US.UTF-8"
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 33554432
  resources:
    requests:
      cpu: 100m
      memory: 2Gi
    limits:
      cpu: 500m
      memory: 4Gi
  users:
    app_user:
      - superuser
      - createdb
  databases:
    app_db: app_user
  allowedSourceRanges:
    - 0.0.0.0/0

Connection Pooling with PgBouncer

Adding a connection pooler improves performance and resource utilization:

yamlapiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
spec:
  replicas: 2
  selector:
    matchLabels:
      app: pgbouncer
  template:
    metadata:
      labels:
        app: pgbouncer
    spec:
      containers:
      - name: pgbouncer
        image: bitnami/pgbouncer:latest
        env:
        - name: POSTGRESQL_HOST
          value: postgres-ha-cluster
        - name: POSTGRESQL_PORT
          value: "5432"
        - name: PGBOUNCER_DATABASE
          value: "*"
        - name: PGBOUNCER_MAX_CLIENT_CONN
          value: "1000"
        - name: PGBOUNCER_DEFAULT_POOL_SIZE
          value: "50"
        - name: PGBOUNCER_POOL_MODE
          value: "transaction"
        ports:
        - containerPort: 6432

Scaling Strategies

Vertical Scaling

Increase resources for your PostgreSQL pods:

yamlresources:
  requests:
    cpu: 2
    memory: 8Gi
  limits:
    cpu: 4
    memory: 16Gi

Horizontal Read Scaling

Increase the number of read replicas:

yaml# For CloudNativePG
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-cluster
spec:
  instances: 5  # Increasing from 3 to 5

Sharding

For extreme scale, implement application-level sharding with multiple PostgreSQL clusters:

yaml# Example of creating multiple sharded clusters
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-shard-1
spec:
  instances: 3
  # ...

---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-shard-2
spec:
  instances: 3
  # ...

Backup and Disaster Recovery

Point-in-Time Recovery with WAL Archiving

Configure WAL archiving to cloud storage:

yamlapiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-cluster
spec:
  # ... other configurations
  backup:
    barmanObjectStore:
      destinationPath: "s3://my-backup-bucket/postgres"
      s3Credentials:
        accessKeyId:
          name: s3-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: s3-creds
          key: ACCESS_SECRET_KEY
      wal:
        compression: gzip
        maxParallel: 8

Scheduled Backups

Create scheduled backups using CronJobs or operator-provided functionality:

yamlapiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: postgres-daily-backup
spec:
  schedule: "0 1 * * *"  # Daily at 1:00 AM
  cluster:
    name: postgres-cluster
  backupOwnerReference: self

Monitoring and Observability

Prometheus and Grafana

Deploy PostgreSQL exporters for metrics:

yamlapiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-exporter
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres-exporter
  template:
    metadata:
      labels:
        app: postgres-exporter
      annotations:
        prometheus.io/scrape: "true"
        prometheus.io/port: "9187"
    spec:
      containers:
      - name: postgres-exporter
        image: prometheuscommunity/postgres-exporter
        env:
        - name: DATA_SOURCE_NAME
          value: "postgresql://postgres:password@postgres-cluster:5432/postgres?sslmode=disable"
        ports:
        - containerPort: 9187
          name: metrics

Key Metrics to Monitor

  • Connection states: Active, idle, and waiting connections
  • Transaction rates: Commits and rollbacks per second
  • Cache hit ratio: Buffer cache effectiveness
  • Replication lag: For replica synchronization
  • Disk usage: Database size and growth rate
  • Query performance: Slow query detection

Performance Tuning

Resource Settings

yamlapiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-cluster
spec:
  instances: 3
  postgresql:
    parameters:
      # Memory settings
      shared_buffers: "25%"  # 25% of container memory
      effective_cache_size: "75%"  # 75% of container memory
      work_mem: "16MB"
      maintenance_work_mem: "256MB"
      
      # Writer settings
      wal_buffers: "16MB"
      max_wal_size: "2GB"
      min_wal_size: "1GB"
      
      # Background writer
      bgwriter_delay: "200ms"
      bgwriter_lru_maxpages: "100"
      
      # Query planner
      random_page_cost: "1.1"  # Assuming SSD storage
      effective_io_concurrency: "200"  # For SSD

Storage Class Selection

Choose the appropriate storage class for your workload:

yamlvolumeClaimTemplates:
- metadata:
    name: postgres-data
  spec:
    accessModes: ["ReadWriteOnce"]
    storageClassName: "fast-ssd"  # Use SSD storage for better performance
    resources:
      requests:
        storage: 20Gi

Security Best Practices

Network Policies

Restrict access to your PostgreSQL cluster:

yamlapiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
  name: postgres-network-policy
spec:
  podSelector:
    matchLabels:
      app: postgres
  ingress:
  - from:
    - podSelector:
        matchLabels:
          role: application
    ports:
    - protocol: TCP
      port: 5432

Secret Management

Store credentials securely using Kubernetes Secrets or external secret management:

yamlapiVersion: v1
kind: Secret
metadata:
  name: postgres-secrets
type: Opaque
data:
  password: cGFzc3dvcmQ=  # base64 encoded
  replication-password: cmVwbC1wYXNzd29yZA==  # base64 encoded

Better yet, integrate with a secrets management solution like HashiCorp Vault:

yamlapiVersion: secrets-store.csi.x-k8s.io/v1
kind: SecretProviderClass
metadata:
  name: postgres-vault-secrets
spec:
  provider: vault
  parameters:
    vaultAddress: "https://vault:8200"
    roleName: "postgres-role"
    objects: |
      - objectName: "postgres-password"
        secretPath: "secret/data/postgres"
        secretKey: "password"

Upgrading PostgreSQL Versions

Blue-Green Deployment Strategy

  1. Deploy new PostgreSQL version alongside existing version
  2. Replicate data from old to new using logical replication
  3. Validate new cluster
  4. Switch application connections to new cluster
  5. Decommission old cluster

Using operators for in-place upgrades:

yaml# Update the PostgreSQL version in the cluster definition
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-cluster
spec:
  instances: 3
  postgresql:
    version: "16"  # Updated from version "15"

Common Challenges and Solutions

Database Initialization

For first-time setup with existing data:

yamlapiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-cluster
spec:
  instances: 3
  bootstrap:
    recovery:
      source: postgres-cluster-source

Handling Schema Migrations

Use Kubernetes Jobs with migration tools:

yamlapiVersion: batch/v1
kind: Job
metadata:
  name: db-migration
spec:
  template:
    spec:
      containers:
      - name: migration
        image: flyway/flyway:latest
        args:
        - migrate
        env:
        - name: FLYWAY_URL
          value: "jdbc:postgresql://postgres-cluster:5432/app_db"
        - name: FLYWAY_USER
          valueFrom:
            secretKeyRef:
              name: postgres-secrets
              key: username
        - name: FLYWAY_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-secrets
              key: password
      restartPolicy: Never
  backoffLimit: 2

Conclusion

Deploying PostgreSQL on Kubernetes enables organizations to build scalable, resilient database backends that can grow with application demands. By leveraging Kubernetes operators and following best practices for high availability, performance tuning, and security, teams can create production-ready PostgreSQL deployments that provide the reliability and scalability modern applications require.

The key to success lies in choosing the right deployment approach based on your specific requirements, implementing proper monitoring and backup strategies, and continuously optimizing your database configuration for performance. With careful planning and the tools outlined in this guide, you can build a PostgreSQL infrastructure on Kubernetes that meets the demands of even the most data-intensive applications.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA ImageChange Image