postgres-on-gke

Deploy, manage, and scale a highly-available PostgreSQL cluster on Google Kubernetes Engine, using the CloudNativePG operator. Modeled after elasticsearch-cn/elastic-on-gke: the operator does the hard stateful lifecycle work, this repo wraps it with GKE provisioning, topology presets, backups, and load balancing.

Stack (pinned in config.sh):

Component	Version
CloudNativePG operator	1.30.0
Barman Cloud plugin (backups)	0.14.0
cert-manager (plugin prerequisite)	v1.21.1
PostgreSQL image	ghcr.io/cloudnative-pg/postgresql:18.6

Architecture

• Demo: zonal GKE cluster, 1 Postgres instance, no backups. For kicking tires.
• Production: regional GKE cluster in us-central1 across 3 zones on c4-highmem-4 nodes (5th Gen Intel Xeon Emerald Rapids + Titanium I/O engine) with hyperdisk-balanced storage, 1 primary + 2 streaming replicas (one per node; with the default 1 node per zone that is one per zone), automatic failover, continuous WAL archiving + daily base backups to GCS (Workload Identity, no key files), internal TCP load balancers for read-write and read-only traffic, optional PgBouncer. GKE node upgrades are confined to a daily maintenance window (default 02:00–06:00 UTC, MAINTENANCE_START / MAINTENANCE_END in config.sh).

The operator creates three ClusterIP services automatically: <cluster>-rw (primary), <cluster>-ro (replicas), <cluster>-r (any).

Prerequisites

• gcloud, kubectl, envsubst (part of gettext), make
• A GCP project with the GKE API enabled and quota for two e2-standard-2 VMs (demo) or three c4-highmem-4 VMs plus Hyperdisk Balanced (production)

Quickstart (demo)

gcloud config set project <your-project>
make init_demo            # ≈ ./bin/demo.sh up  (takes ~10 min)
./bin/pg.sh psql          # interactive psql on the primary
./bin/demo.sh clean       # tear down

Production walkthrough

Production nodes boot from BOOT_DISK_TYPE (default hyperdisk-balanced; C4 cannot boot from PD). If you change MACHINE_TYPE to a family without Hyperdisk (N2, E2), set BOOT_DISK_TYPE=pd-balanced and change storageClass in templates/pg.prod.yml / pg.restore.yml (e.g. to standard-rwo): the data and WAL disks are Hyperdisk too.

Edit config.sh (or export overrides), then:

make init_prod

which is equivalent to:

./bin/gke.sh create           # regional GKE cluster across 3 zones
./bin/cnpg.sh install         # cert-manager + CNPG operator + Barman plugin
./bin/gcs_backup.sh setup     # GCS bucket + GSA + Workload Identity binding
./bin/pg.sh deploy prod       # ObjectStore + 3-instance Cluster
./bin/lb.sh deploy            # internal LBs for VPC-internal access
./bin/monitoring.sh setup     # Managed Prometheus scraping + Cloud Monitoring alerts

Get credentials for the auto-created app database:

./bin/pg.sh password

Day-2 operations

Scale reads (horizontal)

./bin/pg.sh scale 5           # adds streaming replicas; -ro service spreads reads

Note: templates/pg.prod.yml enforces host anti-affinity (kubernetes.io/hostname: required) to guarantee 1 pod per node, plus a best-effort (ScheduleAnyway) spread across zones. To scale beyond 3 instances, scale the GKE node pool first (./bin/gke.sh scale 2 = 6 nodes) so new instances find dedicated nodes; pg.sh scale refuses otherwise. With more than one node per zone, two instances may share a zone.

Scale up (vertical)

Edit resources/storage in templates/pg.prod.yml and re-apply (./bin/pg.sh deploy prod). The operator restarts replicas first, then switches over to an updated replica (primaryUpdateMethod: switchover) — writes pause for a few seconds. Don't change PG_IMAGE and postgresql.parameters in the same apply; the operator rejects that. The rollout starts after deploy returns; follow it with ./bin/pg.sh status. Storage can only grow, and online only if the storage class allows volume expansion (hyperdisk-balanced does).

Scale GKE nodes

./bin/gke.sh scale 2          # nodes per zone

Connection pooling

./bin/pg.sh pooler            # PgBouncer (transaction pooling) at <cluster>-pooler-rw:5432

Two PgBouncer pods, spread across nodes when possible. Transaction pooling means session state (SET, session advisory locks, LISTEN) is not reliable: the next transaction may run on a different server connection.

Backups & point-in-time recovery

WAL archiving is continuous; the first base backup is taken right after deploy and then daily at 07:00 UTC, after the maintenance window (templates/backup.yml, 30-day retention). The bucket keeps deleted objects for 35 more days (Object Versioning). On demand:

./bin/pg.sh backup

Restore creates a new 3-instance cluster from GCS (never in-place), next to the original. Each instance needs its own node, and the default 3 nodes are already taken by pg-main, so add nodes first:

./bin/gke.sh scale 2                                    # 6 nodes: room for both clusters
PG_CLUSTER=pg-main-restored ./bin/gcs_backup.sh setup   # WI binding for the new cluster
source ./config.sh && render templates/pg.restore.yml | kubectl apply -f -

Set recoveryTarget.targetTime in templates/pg.restore.yml for PITR.

Upgrades

• Postgres minor (e.g. 18.6 → 18.7): bump the pinned tag in PG_IMAGE (config.sh), re-run ./bin/pg.sh deploy prod. Rolling update, replicas first, then a switchover. Keep the tag pinned to a minor: a floating tag like :18 lets rescheduled pods silently pull a newer minor.
• Postgres major: CloudNativePG supports offline in-place major upgrades (bump the image major version — read the release notes first), or blue/green via a new cluster bootstrapped from a backup.
• Operator / backup plugin: bump versions in config.sh, re-run ./bin/cnpg.sh install. This triggers a rolling restart of every Postgres pod ending in a switchover (seconds of write downtime), so do it in a quiet period.

Monitoring & alerts

./bin/monitoring.sh setup (part of make init_prod) makes Google Managed Prometheus scrape the CloudNativePG exporter on each instance and creates five Cloud Monitoring alert policies (templates/alerts/):

Alert	Severity	Fires when
WAL archiving failing	critical	the last archive attempt to GCS failed, for 5 min
WAL volume over 80%	critical	pg_wal > 16GiB of the 20Gi WAL volume
no base backup in 26h	error	the last successful base backup is older than 26h, or none exists
volume over 80%	error	any data/WAL volume of an instance is > 80% full
replica lagging or not streaming	warning	lag > 5 min, or a replica has no WAL receiver

Set ALERT_EMAIL=you@example.com before setup to get emails; otherwise alerts only open incidents in the Cloud Monitoring console. setup skips policies that already exist; to change one, ./bin/monitoring.sh clean then setup. Metrics show up in Metrics Explorer under the cnpg_ prefix.

Failover

Automatic. Test it:

primary=$(kubectl -n pg get cluster pg-main -o jsonpath='{.status.currentPrimary}')
kubectl -n pg delete pod "$primary"   # a replica is promoted in seconds
./bin/pg.sh status

Before production

The defaults are stable but deliberately simple. Before real traffic:

1. Check CPU fit after the first make init_prod: each node must have 3000m free for its Postgres pod. kubectl describe node | grep -A8 'Allocated resources'. If an instance stays Pending with Insufficient cpu, lower requests.cpu to 2.5 in templates/pg.prod.yml (keep the 3 limit) and re-apply.
2. Check alerting works: run setup with ALERT_EMAIL set, confirm cnpg_collector_up has data in Metrics Explorer, and that the backup alert clears once the first base backup finishes.
3. Decide what this repo leaves to you:
   • Network: nodes get public IPs on the default VPC. Consider private nodes (--enable-private-nodes, plus Cloud NAT for image pulls) on a dedicated VPC.
   • Node identity: nodes use the Compute Engine default service account with the cloud-platform scope. Use a dedicated least-privilege one.
   • Who can connect: any pod in the cluster can reach <cluster>-rw:5432. A NetworkPolicy (needs --enable-network-policy or Dataplane V2) can limit it to your app namespaces; it must still allow the operator (cnpg-system) and the instances themselves.
   • Data-loss window (RPO): replication is asynchronous, so writes not yet streamed can be lost if the primary's zone fails. For RPO 0 set postgresql.synchronous: {method: any, number: 1, dataDurability: required}; every commit then waits for a replica in another zone, and writes block if no replica is healthy (preferred keeps writing, asynchronously, while degraded).
   • Region loss: the backup bucket lives in REGION, like the cluster. Use a dual- or multi-region bucket if you need to survive a region outage.

Load / stress testing

stress/ holds pgstress, a multi-threaded Go load generator that runs as a Job inside the cluster and produces a self-contained HTML report (client throughput / latency / errors plus pg_stat_* server metrics). Use it to measure the effect of scaling:

cd stress && ./run.sh build && ./run.sh grant
./run.sh run scenarios/tpcb.yaml            # report lands in stress/out/<name>-<ts>/report.html
../bin/pg.sh scale 5 && ./run.sh run scenarios/read-heavy.yaml
./run.sh report out/*/results.json          # overlay runs in one report

See stress/README.md.

Terraform (optional)

terraform/gcp/ creates the same GCP resources as ./bin/gke.sh create + ./bin/gcs_backup.sh setup (cluster, node pool, backup bucket, GSA, Workload Identity binding). Keep its variables in line with config.sh (region, cluster_name, machine_type, boot_disk_type, pg_namespace, pg_cluster_name, maintenance_start/end); the bucket (<project>-pg-backups) and GSA (pg-backup) names are fixed and match the config.sh defaults. Then continue with the scripts:

cd terraform/gcp && terraform init && terraform apply -var project_id=<your-project> && cd ../..
./bin/gke.sh credentials
./bin/cnpg.sh install
./bin/pg.sh deploy prod
./bin/lb.sh deploy
./bin/monitoring.sh setup

The cluster has deletion_protection = true: to tear it down, set it to false and terraform apply before terraform destroy. The bucket has force_destroy = false, so backups survive.

Repo structure

config.sh              # single place for project, region, versions, names
bin/
  demo.sh              # one-command PoC (up / status / clean)
  gke.sh               # GKE cluster create / credentials / scale / maintenance / status / delete
  cnpg.sh              # operator + backup plugin install (idempotent; re-run to upgrade) / status
  pg.sh                # deploy / status / password / psql / scale / backup / pooler / destroy
  gcs_backup.sh        # GCS bucket + Workload Identity setup
  lb.sh                # internal load balancers
  monitoring.sh        # Managed Prometheus scraping + Cloud Monitoring alert policies
templates/
  pg.demo.yml          # 1-instance Cluster
  pg.prod.yml          # 3-instance HA Cluster, C4 + Hyperdisk, host anti-affinity + zone spread, GCS archiving
  storageclass.hyperdisk.yml # Hyperdisk Balanced StorageClass for GKE
  backup.yml           # ObjectStore (GCS) + daily ScheduledBackup
  pg.restore.yml       # PITR: new cluster bootstrapped from GCS
  pooler.yml           # PgBouncer
  lb.yml               # internal TCP LBs (rw + ro)
  monitoring.yml       # PodMonitoring (Managed Prometheus) for the instance pods
  alerts/              # Cloud Monitoring alert policies (PromQL), one per file
terraform/gcp/         # optional IaC path for the GCP-side resources
stress/                # pgstress load generator + HTML report (see stress/README.md)
Makefile               # init_demo / init_prod presets, status, clean_demo / clean_prod

Mapping from elastic-on-gke

elastic-on-gke	here
ECK operator	CloudNativePG operator
Elasticsearch CRD, nodeSets.count	Cluster CRD, instances
GCS snapshot repository	Barman Cloud plugin → GCS (WAL archiving + PITR)
bin/es.sh, bin/kbn.sh	bin/pg.sh
bin/upgrade_ECK.sh	bin/cnpg.sh install (idempotent)
make init_single / init_prod	make init_demo / init_prod
GLB with HTTPS (HTTP protocol)	internal TCP LBs (Postgres wire protocol)
zone-aware shard allocation	required pod anti-affinity per host + zone spread constraints

The key conceptual difference: Elasticsearch scales writes horizontally by adding nodes; PostgreSQL is single-primary. Adding instances here scales reads and availability. If you outgrow a single primary for writes, that's a sharding conversation (e.g. Citus), not more replicas.