Skip to content

Case Study: Stateful High-Availability PostgreSQL Operator on Bare-Metal

Engineering Dimension Production Specification
Architecture Pattern Declarative Cloud-Native Database Operator & Continuous WAL Archiving
Core Technologies CloudNativePG Operator, PostgreSQL 16, Barman Object Store, NVMe Local Storage
Primary Code Paths kubernetes/platform/postgres-operator/, ADR-010
Relevant Decisions ADR-010, ADR-013
Operational Status Production Verified (Self-Healing Failover, WAL Streaming, Sub-ms NVMe I/O)

1. Executive Summary

Running production stateful workloads in Kubernetes is notoriously challenging. Traditional approaches rely on static, single-instance database pods with manual failover, risking data corruption during hypervisor reboots and lacking automated point-in-time recovery.

This project deployed the enterprise-grade CloudNativePG Operator to manage stateful relational data on bare-metal hardware. The implementation features self-healing database instance recovery, automated leader election, continuous Write-Ahead Log (WAL) streaming, and physical storage pinning on high-IOPS NVMe flash storage, providing robust database infrastructure for mission-critical platform applications (n8n, Paperless-ngx, Miniflux).

2. The Problem: The Fragility of Static Database Pods

  1. Manual Failover & Service Downtime: When running a standard PostgreSQL container, any pod eviction, node reboot, or memory pressure results in immediate service downtime until an engineer manually intervenes to restart the pod and verify table integrity.
  2. I/O Contention & Storage Starvation: Placing transactional database writes on slow, shared mechanical drives causes severe disk I/O bottlenecks, locked tables, and potential WAL corruption during heavy background tasks (such as Paperless OCR or media indexing).
  3. Backup Drift & Lack of PITR: Managing database backups via custom bash cronjobs risks silent failure. Periodic dumps provide only coarse 24-hour Recovery Point Objectives (RPO), losing all transactions executed between backup intervals.

3. Stateful Operator Architecture & Storage Pinning

Database Architecture:
[ CloudNativePG Operator (Platform Namespace) ]
        │ • Continuous Health Probes & Quorum Monitoring
        │ • Automated Leader Election & Rolling Updates
        │ • Synchronous Streaming Replication Coordination
        ▼
[ PostgreSQL High-Availability Cluster: postgres-ha ]
        ├── Primary Instance (Master: Read-Write)
        │     └── Pinned to NVMe SSD (High-IOPS local-path)
        ├── Standby Instance (Replica: Read-Only Hot Standby)
        │     └── Pinned to NVMe SSD (Synchronous Streaming)
        └── Continuous WAL Archiving
              └── Compressed WAL streaming to in-cluster S3 / HDD

Sovereign Bare-Metal & Cluster Architecture

4. Key Architectural Implementations

1. Declarative Database Custom Resource (kind: Cluster)

Database instances are defined as native Kubernetes Custom Resources, managed declaratively through GitOps without manual SQL initialization:

# Declarative HA Cluster (kubernetes/platform/postgres-operator/cluster.yaml)
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-ha
  namespace: database
spec:
  instances: 2
  primaryUpdateStrategy: unsupervised
  storage:
    size: 20Gi
    storageClass: local-path # Pinned to NVMe Flash Tier
  backup:
    barmanObjectStore:
      destinationPath: s3://postgres-backups/
      endpointURL: http://minio.database.svc.cluster.local:9000
      s3Credentials:
        accessKeyId:
          name: minio-creds
          key: ACCESS_KEY
        secretAccessKey:
          name: minio-creds
          key: SECRET_KEY
      wal:
        compression: gzip

2. High-IOPS NVMe Flash Storage Pinning

To prevent I/O starvation from background document ingestion or media transcoding on the mechanical drive, database PersistentVolumeClaims are explicitly pinned to Tier 1 NVMe storage via the local-path provisioner. This guarantees sub-millisecond write latency for transactional commits and WAL flushes.

3. Continuous WAL Archiving & Point-in-Time Recovery (PITR)

Rather than relying on coarse nightly database dumps:

  • CloudNativePG continuously streams PostgreSQL Write-Ahead Logs (WAL) to an in-cluster S3 object store.
  • If a table is accidentally dropped or corrupted, the database can be deterministically rolled back to any specific timestamp within the retention window.
  • Recovery Point Objective (RPO) is reduced from 24 hours to < 15 minutes.

4. Zero-Downtime Rolling Maintenance

When applying PostgreSQL minor updates or configuration changes: 1. The operator updates and synchronizes the standby replica. 2. Once streaming parity is reached, the operator performs a coordinated, graceful switchover. 3. The standby is promoted to primary with zero data loss. 4. The former primary is updated and rejoins the cluster as the new standby.

5. Verification & Operational Health

1. Cluster Status & Replication Parity Audit

kubectl get cluster -n database postgres-ha
Actual Result: 
NAME          INSTANCES   READY   STATUS    PRIMARY
postgres-ha   2           2       Cluster in healthy state   postgres-ha-1

2. Live Failover Simulation

Simulated sudden master failure by deleting the primary pod: 

kubectl delete pod -n database postgres-ha-1
Actual Result: Within 8 seconds, CloudNativePG promoted postgres-ha-2 to primary, re-routed service endpoints (postgres-ha-rw), and spawned a replacement standby without human intervention. Zero transactional errors recorded by n8n or Miniflux.

6. Quantified Engineering Impact

Capability Legacy Standalone Pod (v2) CloudNativePG Operator (Current) Engineering Yield
Failover Automation Manual human restart Automated Leader Election (<10s) Self-Healing Relational Data
Transaction Latency Variable (HDD contention) < 1ms Commit Latency (NVMe SSD) Guaranteed High Write Throughput
Recovery Point Objective (RPO) 24 Hours (Nightly cron dump) < 15 Minutes (Continuous WAL) ~99% Reduction in Potential Data Loss
Configuration Drift Manual psql alterations 100% Declarative Kubernetes YAML Immutable GitOps Database Lifecycle
Zero-Downtime Updates Planned downtime required Automated Rolling Switchovers Continuous Service Availability