The Postgres agent owns the full lifecycle of a PostgreSQL cluster: initial deploy, schema migrations, failover, PITR restore, connection pooling, autovacuum tuning, and storage growth — on AWS, GCP, and Azure.
What it deploys
The agent selects the right topology based on your environment and cloud:
| Topology | Best for | Clouds |
|---|---|---|
| CloudNativePG on EKS / GKE / AKS | K8s-native, full operator control | AWS, GCP, Azure |
| Patroni on EC2 / GCE / VM | VM-based HA with etcd | AWS, GCP, Azure |
| AWS RDS Multi-AZ | Managed, minimal ops | AWS |
| AWS Aurora PostgreSQL | Managed + auto-scaling storage | AWS |
| GCP Cloud SQL | Managed on GCP | GCP |
| Azure Flexible Server | Managed on Azure with ZRS HA | Azure |
All topologies include:
- Encrypted storage at rest and in transit (SSL enforced)
- Automated nightly backups with configurable retention (default 7 days)
- Prometheus metrics via
postgres_exporteror cloud-native monitoring - pgBouncer connection pooler for CNPG and Patroni deployments
pg_stat_statementsenabled by default
The agent refuses to deploy a single-instance topology for production. It will push back and propose a HA topology instead.
Quickstart
A minimal happy-path for a staging Postgres on GCP Cloud SQL:
- Start the agent: “Deploy a managed Postgres for our staging environment on GCP.”
- Gate 1 — name: Agent asks for a cluster name; reply
staging-pg. - Gate 2 — cloud target: Agent shows your connected GCP projects; pick one from the dropdown.
- Gate 3 — topology: Agent proposes Cloud SQL with
REGIONALHA for staging; confirm or ask for CNPG on GKE instead. - Gate 4 — sizing: Agent proposes
db-custom-2-8192(2 vCPU / 8 GB, 200 GB PD-SSD). For staging you can accept or downsize. - Gate 5 — review: Agent renders the Terraform plan (Cloud SQL instance, private VPC peering, backup config, Query Insights). An admin approves if your org has approval gates enabled.
- Gate 6 — deploy:
terraform applyruns. Typical deploy time ~5 minutes for Cloud SQL, ~12 minutes for CNPG on a fresh GKE cluster.
After deploy the agent outputs:
- Private IP / connection name (Cloud SQL) or service DNS (CNPG)
- Secrets Manager path for the
appuser password - Connection string template
Configuration options
| Option | Default | Description |
|---|---|---|
pg_version | 16 | PostgreSQL major version (14, 15, 16) |
instance_count | 3 | Number of CNPG / Patroni replicas |
storage_size_gb | 200 (staging) / 1024 (prod) | Disk per replica; plan 3× projected data |
instance_class | m5.2xlarge / db-custom-4-16384 | Compute size — see sizing table |
backup_retention | 7 | Days of automated backups (1–35) |
pgbouncer | true | Deploy pgBouncer connection pooler alongside (CNPG/Patroni only) |
pool_mode | transaction | pgBouncer pool mode: session, transaction, statement |
max_connections | 200 | Hard cap on Postgres backends; remainder served by pgBouncer |
Sizing reference
| Workload | AWS RDS | GCP Cloud SQL | Azure Flexible | CNPG / Patroni |
|---|---|---|---|---|
| Dev / test | db.t4g.medium | db-custom-2-4096 | Burstable B2s | m5.large |
| Small prod | db.r6g.large | db-custom-2-8192 | GP D2s_v3 | m5.large |
| Standard prod | db.r6g.xlarge | db-custom-4-16384 | GP D4s_v3 | m5.2xlarge |
| Heavy prod | db.r6g.2xlarge | db-custom-8-32768 | GP D8s_v3 | m5.4xlarge |
Memory tuning formula: shared_buffers = 25% RAM, effective_cache_size = 75% RAM, work_mem = 16 MB (OLTP).
Common patterns
Scaling up
Tell the agent: “Scale our production Postgres to the next tier.”
The agent will:
- Identify the current instance class
- Propose the next tier (e.g.
db.r6g.xlarge→db.r6g.2xlarge) and estimate cost delta - For RDS/Cloud SQL/Azure: generate a
modify-db-instanceorpatchcall (brief downtime on RDS Multi-AZ failover ~60 s; zero downtime on Aurora / Cloud SQL Regional) - For CNPG: rolling replacement of pods — zero downtime
Planned failover
Tell the agent: “Promote a standby to primary for maintenance.”
The agent runs the correct command per topology:
- CNPG:
kubectl cnpg promote <cluster> <instance> -n <ns> - Patroni:
patronictl switchover --master <primary> --candidate <standby> - RDS:
aws rds reboot-db-instance --force-failover - Aurora:
aws rds failover-db-cluster
Replication lag is verified to be < 1 MB before promoting.
Point-in-time restore (PITR)
Tell the agent: “Restore to 5 minutes before 14:00 UTC today without touching the live cluster.”
The agent creates a new cluster from the backup archive at the target time, validates row counts on the recovered object, and guides you through extracting and importing specific tables back into production.
Adding a read replica
Tell the agent: “Add a read replica for our analytics queries.”
- CNPG: patches
spec.instancesup by 1 - RDS:
aws rds create-db-instance-read-replica - Aurora: adds a reader instance to the cluster
- Patroni: extends the ASG desired count
Connecting from another service
Connection string pattern for CNPG:
postgresql://app:<secret>@<cluster>-rw.<namespace>.svc.cluster.local:5432/app?sslmode=requireFor RDS / Cloud SQL / Azure, use the managed endpoint with IAM auth or Secrets Manager rotation.
Operational tasks
The agent monitors and handles:
| Signal | Automated response |
|---|---|
| Replication lag > 30s | Alerts; investigates ISR / network issues |
| Disk > 80% | Proposes online storage grow (irreversible; no shrink path) |
Connection saturation > 80% of max_connections | Proposes pgBouncer pool size increase or max_connections tune |
| Cache hit ratio < 95% | Diagnoses missing indexes or insufficient shared_buffers |
| Autovacuum stall / XID wraparound warning | Runs VACUUM FREEZE VERBOSE ANALYZE on the worst offenders |
| Long-running queries > 10 min | Offers pg_cancel_backend (graceful) then pg_terminate_backend |
Key Prometheus metrics exposed:
| Metric | Alert threshold |
|---|---|
pg_stat_replication_lag_seconds | > 30 s for 5 min |
pg_stat_activity_count / pg_settings_max_connections | > 80% for 5 min |
node_filesystem_avail_bytes | disk > 80% used |
pg_stat_database_blks_hit / blks_read | cache hit ratio < 95% |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
FATAL: too many connections | max_connections exhausted | Ask agent to increase pgBouncer pool or add replicas |
| Replication lag climbing steadily | Long-running query on primary blocking WAL apply | Agent runs pg_cancel_backend on offending PID |
database must be vacuumed within N transactions | Autovacuum fell behind on a hot table | Agent runs VACUUM FREEZE VERBOSE ANALYZE |
| Disk usage spike | WAL accumulation from an idle replication slot | SELECT * FROM pg_replication_slots; drop unused slots |
| Backup failing on CNPG | IRSA role missing S3 write permission | Agent shows the missing IAM policy and patches the role |
pg_restore errors after PITR | Target time before the oldest base backup | Choose a later target time or widen backup retention |
| Cloud SQL connection refused | Private Service Connect not peered | Check VPC peering; agent can re-apply the Terraform peering block |