Skip to main content

PostgreSQL Operator (PGO) Deployment Guide

Why PGO over Bitnami?​

FeatureBitnamiPGO (Crunchy Data)
High Availability❌ Manual failover✅ Automatic failover (<10s)
Connection Pooling❌ Not included✅ Built-in PgBouncer
Backups❌ Manual✅ Automated (full, diff, incremental)
Point-in-time Recovery❌✅
TLS❌ Manual✅ Auto-generated certificates
Monitoring❌✅ Prometheus metrics
LicenseApache 2.0Apache 2.0

Step 1: Initialize Terraform​

cd /path/to/terraform-hcloud-kube-hetzner/aimsgo

terraform init -upgrade

Step 2: Deploy PGO Operator​

terraform plan -target=module.cluster-bootstrap.helm_release.pgo
terraform apply -target=module.cluster-bootstrap.helm_release.pgo

With GITHUB_TOKEN (full plan):​

export GITHUB_TOKEN=ghp_xxxxxxxxxxxxxx
terraform plan
terraform apply

Step 3: Deploy PostgresCluster​

terraform plan -target=module.cluster-bootstrap.kubectl_manifest.aimsgo_postgres_cluster
terraform apply -target=module.cluster-bootstrap.kubectl_manifest.aimsgo_postgres_cluster

What happens:

  • Namespace database is created
  • PostgreSQL 16 pod (primary) starts
  • 2 PgBouncer pods start
  • Database aimsgo_core is created
  • User aimsgo is created with CREATEDB privilege
  • Automated backups are configured

Step 4: Verify Deployment​

# PGO operator
kubectl get pods -n postgres-operator

# PostgresCluster status
kubectl get postgrescluster -n database

# All pods in database namespace
kubectl get pods -n database

# Expected pods:
# aimsgo-db-instance1-xxxxx          4/4  Running
# aimsgo-db-pgbouncer-xxxxx-xxxxx    2/2  Running
# aimsgo-db-pgbouncer-xxxxx-yyyyy    2/2  Running
# aimsgo-db-repo-host-0              2/2  Running

# PVCs
kubectl get pvc -n database

# Services
kubectl get svc -n database
# - aimsgo-db-primary    (port 5432)
# - aimsgo-db-replicas   (port 5432)
# - aimsgo-db-pgbouncer  (port 5432) ← use this for connections

Step 5: Get Credentials​

# Password
kubectl get secret aimsgo-db-pguser-aimsgo -n database \
  -o jsonpath='{.data.password}' | base64 -d

# Full connection URI
kubectl get secret aimsgo-db-pguser-aimsgo -n database \
  -o jsonpath='{.data.uri}' | base64 -d

# Individual parameters
kubectl get secret aimsgo-db-pguser-aimsgo -n database \
  -o jsonpath='{.data.host}' | base64 -d

Step 6: Connection Details​

Via PgBouncer (recommended for applications):

host: aimsgo-db-pgbouncer.database.svc.cluster.local
port: 5432
database: aimsgo_core
username: aimsgo
password: <from secret aimsgo-db-pguser-aimsgo>

Direct to Primary (admin tasks only):

host: aimsgo-db-primary.database.svc.cluster.local
port: 5432
database: aimsgo_core
username: aimsgo

Step 7: Test Connection​

kubectl run -it --rm postgresql-client \
  --image=postgres:16 \
  --restart=Never \
  --namespace=database \
  --env="PGPASSWORD=$(kubectl get secret aimsgo-db-pguser-aimsgo -n database -o jsonpath='{.data.password}' | base64 -d)" \
  -- psql -h aimsgo-db-pgbouncer -U aimsgo -d aimsgo_core

# Inside psql:
# \l          — list databases
# \dt         — list tables
# SELECT version();
# \q          — exit

Troubleshooting​

PGO Operator not starting​

kubectl describe pod -n postgres-operator -l app.kubernetes.io/name=pgo
kubectl logs -n postgres-operator -l app.kubernetes.io/name=pgo

PostgresCluster not creating​

kubectl describe postgrescluster aimsgo-db -n database
kubectl logs -n postgres-operator -l app.kubernetes.io/name=pgo --tail=100

PostgreSQL pod not starting​

kubectl describe pod -n database \
  -l postgres-operator.crunchydata.com/cluster=aimsgo-db
kubectl logs -n database \
  -l postgres-operator.crunchydata.com/cluster=aimsgo-db -c database

PVC issues​

kubectl get pvc -n database
kubectl describe pvc -n database

Scaling to HA (3 replicas)​

Edit pgo.tf:

instances:
  - name: instance1
    replicas: 3  # was 1

Then apply:

terraform apply -target=module.cluster-bootstrap.kubectl_manifest.aimsgo_postgres_cluster

Backups​

# Check backup schedule
kubectl get postgrescluster aimsgo-db -n database \
  -o jsonpath='{.spec.backups.pgbackrest.repos[0].schedules}'

# Trigger manual backup
kubectl annotate postgrescluster aimsgo-db -n database \
  postgres-operator.crunchydata.com/pgbackrest-backup="$(date +%Y-%m-%d_%H:%M:%S)"

# Check backup status
kubectl exec -n database -it aimsgo-db-repo-host-0 \
  -c pgbackrest -- pgbackrest info

Important Notes​

  1. Backups are stored on local PVC. For production, consider S3/GCS.
  2. HA starts with 1 replica for resource efficiency. Scale to 3 when needed.
  3. TLS certificates are auto-generated by PGO for all connections.
  4. Credentials are stored in Kubernetes Secrets, automatically rotated.
  5. Monitoring — Prometheus ServiceMonitor is created automatically.

References​