pal-e-docs

TF: PostgreSQL Strategy

tf-postgres-strategy Doc

architecture terraform roadmap postgres
Project

PostgreSQL Strategy for the Platform

Current State

  • Harbor: Runs its own internal PostgreSQL (Helm chart-managed, internal PVC). Not shared.
  • pal-e-docs: Moving from SQLite+Litestream to PostgreSQL. Motivation: Litestream is fragile (restore is manual, single-writer limitation), and the app crashed with no automated recovery.
  • basketball-api: Unknown current DB strategy.
  • Future services: Most will need a database.

The Question: Shared PG Operator vs Per-Service PG?

Deploy the CloudNativePG operator via Helm in pal-e-platform. Each service gets a Cluster CR (Custom Resource) defining its own PG instance with automated backups.
Pros:
  • Automated backups to MinIO (S3-compatible, already deployed)
  • Automated failover (even on single-node, handles pod restarts gracefully)
  • Point-in-time recovery (WAL archiving to MinIO)
  • Each service gets its own PG instance (isolation) but managed by one operator (consistency)
  • Declarative — the Cluster CR is a k8s manifest, fits our GitOps model
  • Well-supported, CNCF project
Cons:
  • Another operator to manage (memory overhead ~128Mi)
  • Per-service PG instances use more memory than a shared instance
  • Learning curve for CloudNativePG CRDs

Option B: Single Shared PostgreSQL Instance

Deploy one PostgreSQL instance (via Helm or operator), create per-service databases.
Pros:
  • Lower memory footprint (one PG process)
  • Simpler backup (one pg_dumpall)
Cons:
  • Blast radius — one PG crash takes down all services
  • Version coupling — all services share the same PG version
  • Connection management — need pgBouncer or similar
  • Goes against our "service isolation" principle

Option C: Per-Service Helm-Managed PG (Current Harbor Pattern)

Each service that needs PG includes a bitnami/postgresql subchart in its Helm chart.
Pros:
  • Fully isolated, no shared dependency
  • Each service owns its own PG lifecycle
Cons:
  • No centralized backup strategy
  • No WAL archiving (can't do point-in-time recovery)
  • Duplicated backup logic across services
  • This is what Harbor does — and we have no backup for Harbor's PG

Recommendation: CloudNativePG Operator

Deploy operator in pal-e-platform (it's cluster infrastructure, like Prometheus). Services define Cluster CRs in their k8s/ manifests (managed by ArgoCD). Backups go to MinIO.

Where It Lives in Terraform

The operator goes in pal-e-platform. The per-service Cluster CRs go in each service's k8s/ directory, deployed via ArgoCD.

Backup Configuration

Integration with var.services

Consider adding a postgres field to the services type:
If postgres is set, Terraform creates MinIO credentials and a k8s secret in the service namespace with S3 backup config. The actual PG Cluster CR lives in the service's repo (ArgoCD-managed).

Migration Path

  • Deploy CloudNativePG operator (pal-e-platform PR)
  • Create MinIO bucket + credentials for PG backups (already have litestream-backups bucket)
  • pal-e-docs creates its Cluster CR in k8s/ directory
  • Migrate data from SQLite to new PG instance
  • basketball-api follows same pattern

Procedures

  • sop-postgres-restore — recovery procedure for CNPG-managed PostgreSQL instances