Skip to main content

Database — RDS PostgreSQL Setup

Scope

This page covers the SPWHI Platform RDS PostgreSQL 15 shared instance on the spwhi-platform account — instance configuration, backups, PgBouncer connection pooling, read replica, and secrets management. WardMitra and other applications connect to dedicated schemas within this shared instance.

Architecture Overview​

spwhi-platform VPC — DB Subnet (private, no internet access)
│
└── spwhi-platform-db        RDS PostgreSQL 15 · Multi-AZ · db.t3.large
    ├── wardmitra schema     WardMitra application tables
    ├── fieldassist schema   FieldAssist application tables
    ├── PgBouncer           Connection pooler (sidecar pod in EKS)
    └── Read Replica        For analytics / reporting queries
ConcernRDS gives you
High availabilityMulti-AZ automatic failover — standby in separate AZ, ~60s RTO
BackupsAutomated daily snapshots + PITR to any second within retention window
PatchingMinor version auto-patching during maintenance window
MonitoringEnhanced Monitoring, Performance Insights out of the box
EncryptionEncryption at rest (KMS) and in transit (SSL enforced)
Ops burdenNo managing pg_hba.conf, WAL archiving, or base backups manually

RDS Instance Setup​

Terraform — Shared SPWHI Platform RDS​

# modules/rds/main.tf

resource "aws_db_subnet_group" "spwhi" {
  name       = "spwhi-db-subnet-group"
  subnet_ids = var.db_subnet_ids   # Private DB subnets from VPC module

  tags = {
    Name    = "spwhi-db-subnet-group"
    Project = "spwhi-platform"
  }
}

# Create schemas for each application
resource "postgresql_schema" "wardmitra" {
  name  = "wardmitra"
  owner = "wardmitra_admin"
}

resource "postgresql_schema" "fieldassist" {
  name  = "fieldassist"
  owner = "fieldassist_admin"
}

resource "aws_db_parameter_group" "postgres15" {
  name   = "spwhi-postgres15"
  family = "postgres15"

  # Force SSL connections — no unencrypted traffic
  parameter {
    name  = "rds.force_ssl"
    value = "1"
  }

  # Log slow queries > 1 second
  parameter {
    name  = "log_min_duration_statement"
    value = "1000"
  }

  # Log connections (useful for PgBouncer pool audit)
  parameter {
    name  = "log_connections"
    value = "1"
  }

  tags = { Project = "spwhi-platform", ManagedBy = "terraform" }
}

resource "aws_db_instance" "spwhi_platform" {
  identifier = "spwhi-platform-db"

  # Engine
  engine               = "postgres"
  engine_version       = "15.5"
  parameter_group_name = aws_db_parameter_group.postgres15.name

  # Instance sizing — start here, scale up as DAU grows
  # t3.large: 2 vCPU, 8GB RAM — handles up to ~30k DAU comfortably
  # Upgrade path: t3.large → m5.large → m5.xlarge at 50k+ DAU
  instance_class = var.ward_mitra_instance_class   # "db.t3.large"

  # Storage
  allocated_storage     = 100    # GB — start conservative
  max_allocated_storage = 500    # GB — autoscaling ceiling
  storage_type          = "gp3"
  storage_encrypted     = true
  kms_key_id            = var.kms_key_arn

  # Credentials — managed by Secrets Manager (auto-rotation)
  # Do NOT set username/password here — use manage_master_user_password
  manage_master_user_password = true   # AWS Secrets Manager native rotation
  username                    = "wardmitra_admin"

  # High Availability
  multi_az = true   # Standby in separate AZ — automatic failover ~60s

  # Network
  db_subnet_group_name   = aws_db_subnet_group.spwhi.name
  vpc_security_group_ids = [aws_security_group.rds_spwhi_platform.id]
  publicly_accessible    = false   # Never — DB subnet has no internet route

  # Backups
  backup_retention_period   = 7        # days — PITR window
  backup_window             = "18:30-19:30"   # UTC = 00:00-01:00 IST (low traffic)
  maintenance_window        = "sun:19:30-sun-20:30"   # UTC Sunday night

  # PITR — point-in-time recovery (enabled by default when backup_retention > 0)
  # Allows restore to any second within the retention window

  # Protection
  deletion_protection      = true    # Cannot delete via console or Terraform without unsetting
  skip_final_snapshot      = false
  final_snapshot_identifier = "spwhi-platform-db-final-snapshot"

  # Monitoring
  monitoring_interval             = 60   # Enhanced Monitoring — granular OS metrics
  monitoring_role_arn             = var.rds_monitoring_role_arn
  performance_insights_enabled    = true
  performance_insights_retention_period = 7   # days (free tier)
  enabled_cloudwatch_logs_exports = ["postgresql", "upgrade"]

  tags = {
    Name        = "spwhi-platform-db"
    App         = "spwhi-platform"
    Shared      = "true"
    Project     = "spwhi-platform"
    ManagedBy   = "terraform"
    Environment = var.environment
  }
}

Security Group for RDS​

resource "aws_security_group" "rds_spwhi_platform" {
  name        = "spwhi-platform-rds-sg"
  description = "Allow PostgreSQL access from EKS nodes — shared SPWHI instance"
  vpc_id      = var.vpc_id

  ingress {
    description     = "PostgreSQL from EKS node security group"
    from_port       = 5432
    to_port         = 5432
    protocol        = "tcp"
    security_groups = [var.eks_node_sg_id]   # EKS nodes SG — not open to internet
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags = { Name = "spwhi-platform-rds-sg", Project = "spwhi-platform" }
}

Instance Sizing Reference​

ScaleDAUInstancevCPURAMStorage
Current (POC)< 5kdb.t3.medium24 GB50 GB gp3
Phase 1 target5k–30kdb.t3.large28 GB100 GB gp3
Medium scale30k–100kdb.m5.large28 GB200 GB gp3
Full scale (5L users)100k+db.m5.xlarge416 GB500 GB gp3
Upgrade path

Instance class changes require a reboot (Multi-AZ: ~60s failover, minimal downtime). Always apply via Terraform during the maintenance window. Never resize via console.

Backups & Point-in-Time Recovery (PITR)​

What's automatically backed up​

Backup typeFrequencyRetentionWhat it covers
Automated snapshotDaily7 daysFull DB snapshot at backup window
PITR transaction logsContinuous7 daysRestore to any second within window
Manual snapshotOn-demandUntil deletedPre-migration, pre-major-update checkpoints

How to restore (PITR)​

# Restore to a specific point in time — creates a NEW RDS instance
# Use this for: accidental data deletion, corruption, testing

aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier spwhi-platform-db \
  --target-db-instance-identifier spwhi-platform-db-restored \
  --restore-time 2026-04-01T10:30:00Z \
  --db-instance-class db.t3.large \
  --db-subnet-group-name spwhi-db-subnet-group \
  --no-publicly-accessible \
  --region ap-south-1

# The restored instance will be in a stopped state — start it and verify data
# Then update app connection string to point to restored instance if needed
PITR restores create a new instance

PITR does not restore in-place. It creates a new RDS instance. You must update the application connection string (via Secrets Manager rotation) to point to the new instance after verifying the restore is correct.

Pre-migration manual snapshot​

# Always take a manual snapshot before any major operation
# (migration, schema change, version upgrade)

aws rds create-db-snapshot \
  --db-instance-identifier spwhi-platform-db \
  --db-snapshot-identifier spwhi-platform-pre-migration-$(date +%Y%m%d) \
  --region ap-south-1

Migration — Create SPWHI Instance from POC RDS Data​

Use this runbook when you need to seed the SPWHI environment from the POC account's RDS.

Choose migration path​

PathUse whenDowntimeNotes
Cross-account snapshot restoreYou want to clone the full POC instance into SPWHILowFastest way to copy full DB state
Logical export/import (pg_dump/pg_restore)You want only WardMitra schema/data into shared instanceMediumBest fit for shared-instance, multi-schema model

Option A: Cross-account snapshot restore (full instance copy)​

  1. In POC account, create a manual snapshot:
aws rds create-db-snapshot \
  --db-instance-identifier councillor-db \
  --db-snapshot-identifier councillor-pre-migration-$(date +%Y%m%d) \
  --region ap-south-1

  1. Share snapshot with SPWHI account (and KMS key if encrypted).

  2. In SPWHI account, copy shared snapshot into local account:

aws rds copy-db-snapshot \
  --source-db-snapshot-identifier arn:aws:rds:ap-south-1:<POC_ACCOUNT_ID>:snapshot:poc-wardmitra-pre-migration-YYYYMMDD \
  --target-db-snapshot-identifier spwhi-platform-seed-YYYYMMDD \
  --kms-key-id <SPWHI_KMS_KEY_ARN> \
  --source-region ap-south-1 \
  --region ap-south-1
  1. Restore the copied snapshot as SPWHI instance:
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier spwhi-platform-db \
  --db-snapshot-identifier spwhi-platform-seed-YYYYMMDD \
  --db-instance-class db.t3.large \
  --db-subnet-group-name spwhi-db-subnet-group \
  --vpc-security-group-ids <spwhi-platform-rds-sg-id> \
  --no-publicly-accessible \
  --multi-az \
  --region ap-south-1
  1. Post-restore hardening:
  • Apply parameter group (rds.force_ssl=1, slow query logs)
  • Enable Performance Insights and CloudWatch logs
  • Validate application users and rotate secrets in Secrets Manager

Option B: Logical migration into shared instance (schema-level)​

Use this when SPWHI will host multiple apps in one instance and WardMitra should live in wardmitra schema.

  1. Freeze writes on POC app (maintenance mode) and take final snapshot.

  2. Export WardMitra schema + data from POC:

pg_dump \
  --host <POC_RDS_ENDPOINT> \
  --port 5432 \
  --username <POC_DB_USER> \
  --dbname wardmitra \
  --format custom \
  --no-owner \
  --schema wardmitra \
  --file wardmitra-poc.dump
  1. Prepare target schema in SPWHI instance:
CREATE SCHEMA IF NOT EXISTS wardmitra AUTHORIZATION wardmitra_admin;
GRANT USAGE, CREATE ON SCHEMA wardmitra TO wardmitra_admin;
ALTER ROLE wardmitra_admin IN DATABASE wardmitra SET search_path TO wardmitra,public;
  1. Restore into SPWHI shared instance:
pg_restore \
  --host <SPWHI_PLATFORM_DB_ENDPOINT> \
  --port 5432 \
  --username wardmitra_admin \
  --dbname wardmitra \
  --schema wardmitra \
  --no-owner \
  --clean --if-exists \
  wardmitra-poc.dump
  1. Validate before cutover:
  • Row counts for critical tables (POC vs SPWHI)
  • Spot check recent tickets/workflows
  • Run app smoke tests against SPWHI secrets
  1. Cutover:
  • Update ward-mitra/prod/rds secret to SPWHI endpoint
  • Force ExternalSecret sync and restart PgBouncer
  • Remove maintenance mode and monitor errors/latency
Recommended for current target architecture

Because SPWHI is a shared PostgreSQL instance with per-app schemas, Option B (logical migration) is usually safer for long-term operations than lifting the entire POC instance as-is.

PgBouncer — Connection Pooling​

Why PgBouncer​

PostgreSQL has a hard connection limit. At 500–800 concurrent users, the Node.js API will open many short-lived connections — each one costs memory on the RDS instance. PgBouncer sits between the app pods and RDS, maintaining a small pool of long-lived connections and multiplexing hundreds of app requests through them.

EKS — ward-mitra-prod namespace
│
├── ward-mitra-api pods (many)    ←── each opens connection to PgBouncer
│                                       PgBouncer: 5432
│                                          ↓ (small pool)
└── pgbouncer pod (sidecar)       ←── maintains 20–50 long-lived RDS connections
                                           ↓
                                    RDS spwhi-platform-db: 5432
                                    (wardmitra schema)

PgBouncer Kubernetes Deployment​

# gitops/helm-charts/ward-mitra-api/templates/pgbouncer.yaml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
  namespace: ward-mitra-prod
spec:
  replicas: 2   # Two instances for HA — ALB or DNS round-robin
  selector:
    matchLabels:
      app: pgbouncer
  template:
    metadata:
      labels:
        app: pgbouncer
    spec:
      containers:
        - name: pgbouncer
          image: pgbouncer/pgbouncer:1.22.0
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRESQL_HOST
              valueFrom:
                secretKeyRef:
                  name: ward-mitra-rds-creds   # Synced by ESO from Secrets Manager
                  key: host
            - name: POSTGRESQL_PORT
              value: "5432"
            - name: POSTGRESQL_USERNAME
              valueFrom:
                secretKeyRef:
                  name: ward-mitra-rds-creds
                  key: username
            - name: POSTGRESQL_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: ward-mitra-rds-creds
                  key: password
            - name: PGBOUNCER_DATABASE
              value: wardmitra    # Shared instance; all apps connect to same instance, different schemas
            - name: PGBOUNCER_SCHEMA
              value: wardmitra    # Schema isolation within shared instance
            - name: PGBOUNCER_POOL_MODE
              value: transaction    # Transaction pooling — most efficient for APIs
            - name: PGBOUNCER_MAX_CLIENT_CONN
              value: "500"          # Max connections from app pods
            - name: PGBOUNCER_DEFAULT_POOL_SIZE
              value: "25"           # Connections to RDS per database
            - name: PGBOUNCER_MIN_POOL_SIZE
              value: "5"
            - name: PGBOUNCER_RESERVE_POOL_SIZE
              value: "5"
            - name: PGBOUNCER_RESERVE_POOL_TIMEOUT
              value: "3"
          resources:
            requests:
              cpu: 100m
              memory: 64Mi
            limits:
              cpu: 500m
              memory: 128Mi
          livenessProbe:
            tcpSocket:
              port: 5432
            initialDelaySeconds: 10
            periodSeconds: 30
---
apiVersion: v1
kind: Service
metadata:
  name: pgbouncer
  namespace: ward-mitra-prod
spec:
  selector:
    app: pgbouncer
  ports:
    - port: 5432
      targetPort: 5432
  type: ClusterIP

Pool mode explanation​

ModeHow it worksBest for
sessionOne RDS connection per client sessionLong-running sessions (not APIs)
transactionRDS connection held only during a transaction✅ REST APIs — short transactions
statementRDS connection held only for one statementRarely used — breaks multi-statement txns

Ward Mitra uses transaction mode — Node.js API requests are short, stateless, and use transactions sparingly.

App connection string​

# Node.js API connects to PgBouncer — NOT directly to RDS
# PgBouncer service DNS: pgbouncer.ward-mitra-prod.svc.cluster.local
# All apps connect to the same shared spwhi-platform-db instance, but use different schemas

DATABASE_URL=postgresql://wardmitra_admin:PASSWORD@pgbouncer.ward-mitra-prod.svc.cluster.local:5432/wardmitra?search_path=wardmitra
SSL with PgBouncer

PgBouncer → RDS connection uses SSL (enforced by rds.force_ssl=1 parameter). App → PgBouncer connection is within the cluster (no SSL required, traffic stays in VPC).

Read Replica — Analytics & Reporting​

Why a read replica. Create only when needed​

If Ward Mitra's AI features include predictive analytics, sentiment heatmaps, and ward-level reporting these queries are expensive — full table scans, aggregations, GROUP BYs — and should never run on the primary RDS instance where citizen grievance submissions are landing.

The read replica takes a copy of all writes from the primary (all schemas) and makes them available for read-only queries with ~1–5 second replication lag.

Primary RDS (write + read)           Read Replica (read-only)
All apps  ──write──►  spwhi-platform-db
                           │ async replication (all schemas)
          ◄──read──  spwhi-platform-db-replica
                       ▲
                  Analytics service
                  Admin dashboard
                  Grafana SQL queries

Terraform — Read Replica​

resource "aws_db_instance" "spwhi_platform_replica" {
  identifier             = "spwhi-platform-db-replica"
  replicate_source_db    = aws_db_instance.spwhi_platform.identifier

  instance_class         = "db.t3.micro"   # Smaller — read-only, lighter load; replicates all schemas
  storage_encrypted      = true
  kms_key_id             = var.kms_key_arn

  # Replica does not need Multi-AZ — it IS the redundancy
  multi_az               = false

  # No automated backups on replica — primary handles this
  backup_retention_period = 0
  skip_final_snapshot     = true

  # Performance Insights for analytics query tuning
  # performance_insights_enabled = true

  # Monitoring
  monitoring_interval    = 60
  monitoring_role_arn    = var.rds_monitoring_role_arn

  # Allow separate security group — analytics service gets access, app pods don't need it
  vpc_security_group_ids = [aws_security_group.rds_replica.id]
  publicly_accessible    = false

  tags = {
    Name        = "spwhi-platform-db-replica"
    App         = "spwhi-platform"
    Role        = "read-replica"
    Shared      = "true"
    Project     = "spwhi-platform"
    ManagedBy   = "terraform"
  }
}

What connects to the replica vs primary​

ServiceConnects toWhy
ward-mitra-api (Node.js)PgBouncer → Primary (wardmitra schema)Write operations, citizen grievance submissions
fieldassist-api (Node.js)PgBouncer → Primary (fieldassist schema)Write operations to separate schema
Analytics serviceReplica directlyHeavy aggregation queries — cannot block primary
Admin dashboardReplica via PgBouncerReporting queries — read-only
Grafana SQL panelsReplica directlyDashboard queries — analytics only
Never write to the replica

The read replica is read-only at the PostgreSQL level — any write attempt will fail with an error. Configure your connection strings explicitly:

  • DATABASE_URL → PgBouncer → Primary (spwhi-platform-db)
  • DATABASE_REPLICA_URL → Replica endpoint directly (spwhi-platform-db-replica) Both connect to the same shared instance; schema isolation is handled at the application level.

Secrets Management​

RDS credentials follow the hybrid secrets strategy for the shared instance. The master password is managed by AWS Secrets Manager (native RDS rotation), and all other DB config (host, port, name) is in SSM Parameter Store (free tier). Each application (WardMitra, FieldAssist, etc.) has its own db user credentials that connect to the shared instance but have access only to their own schema.

How credentials flow into pods​

AWS Secrets Manager
  ├── ward-mitra/prod/rds
  │   JSON: { username, password, host, port, dbname, search_path }
  │   └── ward-mitra-admin user (can read/write wardmitra schema only)
  │
  └── fieldassist/prod/rds
      JSON: { username, password, host, port, dbname, search_path }
      └── fieldassist-admin user (can read/write fieldassist schema only)
              ↓ (ESO syncs every 1 hour)
External Secrets Operator
  ├── ExternalSecret: ward-mitra-rds-creds
  └── ExternalSecret: fieldassist-rds-creds
              ↓ (creates/updates)
Kubernetes Secrets
  ├── ward-mitra-rds-creds
  │   ├── host      → spwhi-platform-db-endpoint.ap-south-1.rds.amazonaws.com
  │   ├── port      → 5432
  │   ├── username  → wardmitra_admin
  │   ├── password  → [rotated automatically]
  │   ├── dbname    → wardmitra
  │   └── search_path → wardmitra
  │
  └── fieldassist-rds-creds
      ├── host      → spwhi-platform-db-endpoint.ap-south-1.rds.amazonaws.com
      ├── port      → 5432
      ├── username  → fieldassist_admin
      ├── password  → [rotated automatically]
      ├── dbname    → wardmitra  (same instance)
      └── search_path → fieldassist
              ↓ (mounted as env vars)
PgBouncer pods + application pods (ward-mitra-api, fieldassist-api, etc.)

ExternalSecret manifest​

# gitops/helm-charts/ward-mitra-api/templates/externalsecret-rds.yaml
# Each app has its own ExternalSecret pulling from its own Secrets Manager secret
# but all connect to the shared spwhi-platform-db instance

apiVersion: external-secrets.io/v1beta1
kind: ExternalSecret
metadata:
  name: ward-mitra-rds-creds
  namespace: ward-mitra-prod
spec:
  refreshInterval: 1h   # Aligns with Secrets Manager rotation window

  secretStoreRef:
    name: aws-secretsmanager   # ClusterSecretStore pointing to Secrets Manager
    kind: ClusterSecretStore

  target:
    name: ward-mitra-rds-creds   # K8s Secret name created/updated by ESO
    creationPolicy: Owner

  dataFrom:
    - extract:
        key: ward-mitra/prod/rds   # SM secret name — ward-mitra-specific
        # extract unpacks the JSON blob into individual K8s Secret keys:
        # host, port, username, password, dbname, search_path

SSM parameters for non-sensitive DB config​

# Shared instance parameters — all apps read these
resource "aws_ssm_parameter" "db_host" {
  name  = "/spwhi/platform/db/host"
  type  = "String"
  value = aws_db_instance.spwhi_platform.address
}

resource "aws_ssm_parameter" "db_port" {
  name  = "/spwhi/platform/db/port"
  type  = "String"
  value = "5432"
}

resource "aws_ssm_parameter" "db_replica_host" {
  name  = "/spwhi/platform/db/replica-host"
  type  = "String"
  value = aws_db_instance.spwhi_platform_replica.address
}

# Per-app schema parameters
resource "aws_ssm_parameter" "wardmitra_db_name" {
  name  = "/spwhi/ward-mitra/prod/db/name"
  type  = "String"
  value = "wardmitra"
}

resource "aws_ssm_parameter" "wardmitra_schema" {
  name  = "/spwhi/ward-mitra/prod/db/schema"
  type  = "String"
  value = "wardmitra"
}

resource "aws_ssm_parameter" "fieldassist_db_name" {
  name  = "/spwhi/fieldassist/prod/db/name"
  type  = "String"
  value = "wardmitra"  # Same instance, different schema
}

resource "aws_ssm_parameter" "fieldassist_schema" {
  name  = "/spwhi/fieldassist/prod/db/schema"
  type  = "String"
  value = "fieldassist"
}

Secrets Manager auto-rotation​

When Secrets Manager rotates the RDS master password:

  1. Secrets Manager calls the RDS native rotation Lambda
  2. Lambda updates the password on the RDS instance
  3. Secrets Manager stores the new password in the secret
  4. ESO detects the change within refreshInterval (1h) and updates the K8s Secret
  5. PgBouncer picks up the new password on next connection — no pod restart needed
No manual password rotation

Because manage_master_user_password = true in Terraform, RDS and Secrets Manager handle rotation automatically. You never need to touch the password manually. If you need to force a rotation: AWS Console → Secrets Manager → ward-mitra/prod/rds → Rotate immediately.

Monitoring & Alerts​

Key metrics to watch (Grafana / CloudWatch)​

MetricWarning thresholdCritical thresholdAction
CPUUtilization> 70% for 10min> 85% for 5minScale instance class up
FreeStorageSpace< 20 GB< 10 GBAutoscaling will trigger (max 500GB)
DatabaseConnections> 80% of max> 95% of maxTune PgBouncer pool size
ReplicaLag> 5 seconds> 30 secondsInvestigate write load on primary
ReadLatency> 10ms> 50msCheck slow query log, add indexes
WriteLatency> 10ms> 50msCheck PgBouncer pool exhaustion
FreeableMemory< 2 GB< 512 MBScale instance class up

Slow query log​

Queries taking longer than 1 second are logged to CloudWatch Logs (configured via log_min_duration_statement = 1000 parameter group).

# View slow queries via AWS CLI
aws logs filter-log-events \
  --log-group-name /aws/rds/instance/spwhi-platform-db/postgresql \
  --filter-pattern "duration" \
  --start-time $(date -d '1 hour ago' +%s000) \
  --region ap-south-1 \
  | jq '.events[].message' \
  | grep "duration"

Performance Insights​

AWS RDS Performance Insights is enabled on both primary and replica. Access via: AWS Console → RDS → spwhi-platform-db → Performance Insights

Useful for identifying:

  • Top SQL queries by load
  • Wait events (lock contention, I/O waits)
  • Which application user is generating most load

Common Operations​

Connect to RDS via EKS pod (no bastion needed)​

# Run a temporary psql pod in the ward-mitra-prod namespace
# This uses the existing cluster network access — no bastion host required
# Connects to the shared spwhi-platform-db, wardmitra schema

kubectl run psql-debug \
  --image=postgres:15 \
  --restart=Never \
  --rm -it \
  --namespace=ward-mitra-prod \
  --env="PGPASSWORD=$(kubectl get secret ward-mitra-rds-creds \
    -n ward-mitra-prod -o jsonpath='{.data.password}' | base64 -d)" \
  -- psql \
    -h pgbouncer.ward-mitra-prod.svc.cluster.local \
    -U wardmitra_admin \
    -d wardmitra \
    -c "SET search_path TO wardmitra;"

Check PgBouncer pool status​

# Connect to PgBouncer admin console
kubectl exec -it deploy/pgbouncer -n ward-mitra-prod -- \
  psql -h localhost -p 5432 -U pgbouncer pgbouncer

# Inside psql — PgBouncer admin commands:
SHOW POOLS;     -- connection pool stats
SHOW CLIENTS;   -- connected clients
SHOW SERVERS;   -- RDS server connections
SHOW STATS;     -- request rates and latency

Check replication lag​

# On the replica — check lag in seconds
# All schemas in the shared instance replicate together
kubectl run psql-replica \
  --image=postgres:15 \
  --restart=Never \
  --rm -it \
  --namespace=ward-mitra-prod \
  -- psql \
    -h spwhi-platform-db-replica.ap-south-1.rds.amazonaws.com \
    -U wardmitra_admin \
    -d wardmitra \
    -c "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS replication_lag_seconds;"

Force ESO to refresh DB credentials immediately​

# Annotate the ExternalSecret to trigger immediate re-sync
# Useful after a manual Secrets Manager rotation

kubectl annotate externalsecret ward-mitra-rds-creds \
  -n ward-mitra-prod \
  force-sync=$(date +%s) \
  --overwrite

Troubleshooting​

too many connections error in app logs​

PgBouncer pool may be exhausted on the shared instance. Check:

# See current pool utilisation
kubectl exec -it deploy/pgbouncer -n ward-mitra-prod -- \
  psql -h localhost -p 5432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

# If cl_active is close to PGBOUNCER_MAX_CLIENT_CONN (500):
# → Increase MAX_CLIENT_CONN in PgBouncer deployment env vars

# If sv_active is close to PGBOUNCER_DEFAULT_POOL_SIZE (25):
# → Increase DEFAULT_POOL_SIZE (check RDS max_connections first)
# NOTE: This pool is shared across all apps (WardMitra, FieldAssist, etc.)

Check RDS max_connections on shared instance:

SHOW max_connections;
-- For db.t3.large: typically 171
-- Calculate: (DEFAULT_POOL_SIZE × number_of_PgBouncer_replicas) per app 
--   + admin + overhead < max_connections

password authentication failed after Secrets Manager rotation​

ESO hasn't synced yet. Force refresh. The rotation affects the user credential in Secrets Manager for that specific app:

kubectl annotate externalsecret ward-mitra-rds-creds \
  -n ward-mitra-prod \
  force-sync=$(date +%s) --overwrite

# Wait 30 seconds, check the K8s Secret was updated
kubectl get secret ward-mitra-rds-creds -n ward-mitra-prod -o jsonpath='{.data.password}' | base64 -d

# Then restart PgBouncer to pick up new creds
kubectl rollout restart deploy/pgbouncer -n ward-mitra-prod

High replication lag on replica​

# Check write load on the shared primary — is it unusually high?
# This replicates ALL schemas (wardmitra, fieldassist, etc.)
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name WriteIOPS \
  --dimensions Name=DBInstanceIdentifier,Value=spwhi-platform-db \
  --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --period 300 \
  --statistics Average \
  --region ap-south-1

If lag is persistent (> 30s), upgrade the replica instance class. Note: All schemas replicate as a single unit; lag affects all apps equally.

SparkOps Advisory Services · Sanket Pethkar · March 2026 · Confidential — SPW Healthcare Innovations Pvt. Ltd.