Postgres Multi-Location

​

Overview

​

What Gets Created

​

Prerequisites

cpln gvc get GVC_NAME -o yaml

cpln secret create-dictionary --name POSTGRES_CREDENTIALS_SECRET_NAME \
  --entry username=postgres \
  --entry password='YOUR-STRONG-PASSWORD' \
  --entry database=mydb

cpln workload get-deployments RELEASE_NAME-postgres --gvc GVC_NAME -o yaml

The secret POSTGRES_CREDENTIALS_SECRET_NAME no longer exists. Workload updates are paused until the
secret is added or the reference to the secret removed.

​

Installation

# postgres-locations.yaml — every name must already be a location of the GVC
global:
  locations:
    - name: LOCATION_1
      replicas: 1
    - name: LOCATION_2
      replicas: 1
    - name: LOCATION_3
      replicas: 1

cpln helm install RELEASE_NAME oci://ghcr.io/controlplane-com/templates/postgres-multi-location \
  --version 2.0.0 --gvc GVC_NAME -f postgres-locations.yaml \
  --set postgres.credentialsSecretName=POSTGRES_CREDENTIALS_SECRET_NAME

​

Configuration

​

Locations and Quorum

global:
  locations:
    - name: aws-us-east-1
      replicas: 1
    - name: aws-eu-central-1
      replicas: 1
    - name: aws-us-west-2
      replicas: 1

• global.locations[].name — A Control Plane location that must already be one of the locations of the GVC you install into. At least two are required, each listed once.
• global.locations[].replicas — Patroni members in that location (minimum 1). etcd always runs exactly one member per location and ignores this field.

​

Image and Resources

image: controlplanecorporation/patroni-postgres:0.7

resources:
  minCpu: 500m
  minMemory: 1Gi
  maxCpu: 1
  maxMemory: 2Gi

• image — PostgreSQL 17 with Patroni and WAL-G. The same image runs the WAL-G sidecar when backup.mode: wal-g.
• resources.minCpu / resources.minMemory — Reserved CPU and memory per member.
• resources.maxCpu / resources.maxMemory — Limits per member. On a stateful workload the ratio of maxCpu to minCpu may not exceed 4:1; a larger ratio is rejected when the workload is applied.

​

Credentials

postgres:
  # REQUIRED PREREQUISITE SECRET — CREATE IT BEFORE YOU INSTALL.
  # A `dictionary` secret holding exactly three keys: `username`, `password` and
  # `database`. If it does not exist at install time the deployment WEDGES
  # waiting on it and looks broken. See Prerequisites in the README for the
  # exact `cpln secret create-dictionary` command.
  credentialsSecretName: my-postgres-credentials

​

Preferred Primary Location

primaryLocation: ""

• On a fresh install it decides where the primary starts. Members outside the preferred location wait up to 90 seconds for that location to initialize the cluster before bootstrapping themselves. If the preferred location is down, slow or misconfigured they bootstrap anyway and log WARNING: no leader in LOCATION after 90s — bootstrapping here instead; the cluster is then correct but the primary is elsewhere, and you move it with patronictl switchover (see Managing the Cluster With patronictl). The wait cannot split the cluster: the consensus store is the arbiter, so a member that gives up early finds the cluster already initialized and joins it as a replica.
• It biases failover elections toward that location through Patroni’s failover_priority tag, a tiebreaker between candidates that have replayed the same amount of WAL. It does not fail back automatically after an outage: a restored preferred location keeps the current leader until the next election.
• Changing it on a live cluster moves the leader. The value is baked into the startup script, so editing it restarts every member, and the election that follows picks the preferred location. That is a full restart with a write outage — to move a primary without one, use patronictl switchover instead.

​

Storage

volumeset:
  capacity: 10 # initial capacity in GiB (minimum is 10)
  autoscaling:
    enabled: false
    maxCapacity: 100 # GiB, when autoscaling is enabled
    minFreePercentage: 10 # free-space trigger
    scalingFactor: 1.2 # growth multiplier

• volumeset.capacity — Initial size in GiB of the volume every member gets for PGDATA (minimum 10). The volume set keeps snapshots for 7 days and takes a final snapshot when it is deleted.
• volumeset.autoscaling.* — Grow the volume automatically when free space drops below minFreePercentage, multiplying the capacity by scalingFactor up to maxCapacity. The growth event itself has not been exercised on this template.

​

Internal Access

internalAccess:
  type: same-gvc # options: same-gvc, same-org, workload-list
  # Only used when type is workload-list. This chart's OWN workloads (Patroni,
  # the proxy, PgBouncer, the backup cron) are added automatically — the list
  # also governs Patroni-to-Patroni replication and the proxy's health checks,
  # so a list naming only clients would cut the cluster off from itself. List
  # your clients here; do not list this release's workloads.
  workloads: []
    #- //gvc/GVC_NAME/workload/WORKLOAD_NAME

​

Leader-Routing Proxy

proxy:
  enabled: true # automatically enabled when pgbouncer.enabled is true
  image: haproxy:2.9
  resources:
    cpu: 100m
    memory: 128Mi
  minReplicas: 2
  maxReplicas: 2

• proxy.minReplicas / proxy.maxReplicas — Autoscaling bounds per location; the default 2 means two proxy replicas in each configured location.
• Port 8404 serves /healthz (healthy while a primary backend is reachable) and port 8405 serves /stats, the live HAProxy status of every member.

​

PgBouncer

pgbouncer:
  enabled: false
  image: edoburu/pgbouncer:v1.25.1-p0
  poolMode: transaction # options: session, transaction, statement
  defaultPoolSize: 25 # real Postgres connections PgBouncer keeps per replica
  maxClientConn: 1000 # client connections PgBouncer accepts per replica
  maxDbConnections: 100 # cap on Postgres connections PER PgBouncer pod — multiply by maxReplicas
  minReplicas: 2
  maxReplicas: 4
  resources:
    cpu: 200m
    memory: 128Mi

• pgbouncer.minReplicas / pgbouncer.maxReplicas — Autoscaling bounds per location.
• pgbouncer.maxDbConnections — Enforced per PgBouncer replica, not across the tier. PgBouncer instances do not coordinate, so the real ceiling on server connections is maxReplicas × maxDbConnections — at the shipped values 4 × 100 = 400 against the cluster’s max_connections of 100, three of which are reserved for superusers. Size it so maxReplicas × maxDbConnections stays comfortably under 97, and treat defaultPoolSize the same way; otherwise clients get remaining connection slots are reserved under load instead of being queued.

​

Backups

backup:
  enabled: false
  mode: logical # logical or wal-g
  # `logical` mode ONLY. A cron workload runs in EVERY location of its GVC, so
  # without this the job would fire once per location every night and write N
  # copies into one bucket. Pick the ONE location it runs in — nearest your
  # bucket. `wal-g` mode ignores it: that archives from whichever member is
  # currently the primary, wherever that is.
  location: aws-us-east-1
  resources: # applies to whichever mode is enabled
    cpu: 100m
    memory: 512Mi

  logical:
    image: ghcr.io/controlplane-com/backup-images/postgres-backup:17.1.0 # 17.1.0 = Postgres 17, 18.1.0 = Postgres 18
    schedule: "0 2 * * *" # cron schedule, default is daily at 02:00 UTC

  walg:
    intervalSeconds: 21600 # seconds between base backups, default is every 6 hours

  # storage settings are applied to whichever mode is enabled
  provider: aws # options: aws, gcp, minio

  aws:
    bucket: my-postgres-bucket
    region: us-east-1
    cloudAccountName: my-s3-cloud-account
    policyName: my-postgres-backup-policy # bucket-scoped IAM policy, see README
    prefix: postgres/backups # folder within the bucket

  gcp:
    bucket: my-postgres-bucket
    cloudAccountName: my-gcs-cloud-account
    prefix: postgres/backups # folder within the bucket

  minio: # a self-hosted MinIO workload, or any S3-compatible endpoint
    endpoint: http://my-minio-workload:9000 # e.g. http://WORKLOAD.GVC.cpln.local:9000 in the same GVC
    bucket: my-postgres-bucket
    # REQUIRED PREREQUISITE SECRET when provider is `minio` — a `dictionary`
    # secret holding `accessKey` and `secretKey`. See Storage setup in the README.
    credentialsSecretName: my-postgres-minio-credentials
    prefix: postgres/backups # folder within the bucket

• backup.location — logical mode only. A cron workload runs in every location of its GVC, so without this selector a three-location cluster would dump the same database three times a night into one bucket. The WAL-G sidecar needs no selector: only the member holding the leader lock pushes, so the archive follows the primary across a failover.
• backup.resources.memory — Do not lower it below the shipped 512Mi. The Google Cloud Storage path fails at 128Mi with no error line anywhere — a logical job simply reports failed, and the wal-g sidecar is killed in a loop while WAL accumulates with no base backup to restore against.
• backup.provider and the aws / gcp / minio blocks apply to whichever mode is enabled. Each provider’s bucket, cloud account, policy and secret steps are under Backing Up.

​

etcd Consensus Store

etcd:
  image: controlplanecorporation/etcd:0.1
  resources:
    cpu: 500m
    memory: 512Mi
  tuning:
    heartbeatIntervalMs: 250
    electionTimeoutMs: 5000
    # History compaction. Without it etcd's backend grows with time alone —
    # Patroni renews its lease every ~10s and every renewal is a revision —
    # until it hits the quota and etcd goes READ-ONLY.
    autoCompactionMode: periodic # periodic (retention is a duration) or revision (a revision count)
    autoCompactionRetention: 1h # periodic needs an explicit unit (1h, 30m, 24h)
    quotaBackendBytes: 0 # backend size limit in bytes; 0 = etcd's own default of 2 GiB
  volumeset:
    capacity: 10
  internalAccess:
    type: same-gvc
    # etcd adds its OWN workload automatically, but not this chart's Patroni
    # workload. If you set type to workload-list here you MUST add
    # //gvc/YOUR_GVC/workload/RELEASE-postgres yourself, or Patroni loses its DCS.
    workloads: []
  recovery:
    # EMERGENCY ONLY — see "Recovering from a lost location" in the README.
    forceNewClusterInLocation: ""

• etcd.tuning.autoCompactionMode / autoCompactionRetention / quotaBackendBytes — Leave at the defaults. Patroni renews its leader lease every few seconds and every renewal is an etcd revision, so an uncompacted backend grows with time alone until etcd goes read-only and takes the database’s failover with it. Compaction cannot be switched off, only retuned; see Compaction and Backend Growth for the mechanism and the accepted value formats.
• etcd.tuning.heartbeatIntervalMs / electionTimeoutMs — The Raft timers, tuned for cross-region round trips; raise both in proportion if your locations are more than about 250 ms apart.
• etcd.recovery.forceNewClusterInLocation — Leave empty in normal operation. It is the manual recovery path for a two-location cluster that has lost a location permanently; see Recovering From a Lost Location.

​

Connecting

cpln port-forward RELEASE_NAME-postgres-proxy 5432:5432 --gvc GVC_NAME

psql "postgresql://USERNAME:PASSWORD@127.0.0.1:5432/DATABASE" -c 'select pg_is_in_recovery()'

cpln workload exec RELEASE_NAME-postgres --gvc GVC_NAME --location LOCATION_NAME --container patroni-postgres \
  -- patronictl -c /tmp/patroni_config.yml list

​

Operations

​

Backing Up

• logical — On every run of backup.logical.schedule the cron workload connects through the proxy to the current primary, runs pg_dumpall, and uploads postgres-TIMESTAMP.sql.gz under backup.PROVIDER.prefix. The dump includes every database and role.
• wal-g — Every member archives WAL segments with wal-g wal-push and, on the member holding the leader lock, the wal-g-backup sidecar pushes a base backup every backup.walg.intervalSeconds (retrying every minute until the first one succeeds, so a newly promoted leader backs up promptly). Base backups and WAL land under backup.PROVIDER.prefix, compressed with zstd.

​

AWS S3

1. Create an S3 bucket. Set backup.aws.bucket to its name and backup.aws.region to its region.
2. If you do not have one yet, create a Control Plane cloud account for the AWS account holding the bucket. Set backup.aws.cloudAccountName to its name.
3. Create an IAM policy scoped to exactly that bucket, replacing YOUR_BUCKET_NAME:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:PutObject",
                "s3:DeleteObject",
                "s3:ListBucket",
                "s3:GetObjectVersion",
                "s3:DeleteObjectVersion"
            ],
            "Resource": [
                "arn:aws:s3:::YOUR_BUCKET_NAME",
                "arn:aws:s3:::YOUR_BUCKET_NAME/*"
            ]
        }
    ]
}

4. Set backup.aws.policyName to that policy’s name. The template attaches it, together with cpln-connector, to the workload identity and nothing else — the bucket in your policy is the only storage the backup can reach.

​

GCS

1. Create a GCS bucket. Set backup.gcp.bucket to its name.
2. Create a Control Plane cloud account for the GCP project holding the bucket, and set backup.gcp.cloudAccountName to its name.
3. Grant the cloud account’s service account the Storage Admin (roles/storage.admin) role on the project. The template additionally binds the identity to roles/storage.objectAdmin on exactly the bucket in backup.gcp.bucket.

​

MinIO

1. Create the bucket in MinIO. Set backup.minio.bucket to its name.
2. Set backup.minio.endpoint to the S3 API address including the port. For the MinIO template deployed in the same GVC that is http://WORKLOAD_NAME.GVC_NAME.cpln.local:9000 — use the fully qualified name, because a bare short name does not resolve for every workload type.
3. Create a dictionary secret holding exactly the keys accessKey and secretKey, and set backup.minio.credentialsSecretName to its name. For the MinIO template these are its admin username and password:

cpln secret create-dictionary --name MINIO_CREDENTIALS_SECRET_NAME \
  --entry accessKey=MINIO_ACCESS_KEY \
  --entry secretKey=MINIO_SECRET_KEY

​

Manual Dump on Demand

cpln workload exec RELEASE_NAME-postgres --gvc GVC_NAME --location LOCATION_NAME --container patroni-postgres --quiet -- \
  sh -c 'PGPASSWORD="$PG_PASSWORD" pg_dump -Fc -h 127.0.0.1 -U "$PG_USERNAME" "$PG_DATABASE" | base64' \
  | base64 -d > DATABASE.pgc

​

Restoring a Backup

​

Logical

export PGPASSWORD="PASSWORD"

aws s3 cp "s3://BUCKET_NAME/PREFIX/postgres-TIMESTAMP.sql.gz" - \
  | gunzip \
  | psql \
      --host=RELEASE_NAME-postgres-proxy.GVC_NAME.cpln.local \
      --port=5432 \
      --username=USERNAME \
      --dbname=postgres

unset PGPASSWORD

export PGPASSWORD="PASSWORD"

gsutil cp "gs://BUCKET_NAME/PREFIX/postgres-TIMESTAMP.sql.gz" - \
  | gunzip \
  | psql \
      --host=RELEASE_NAME-postgres-proxy.GVC_NAME.cpln.local \
      --port=5432 \
      --username=USERNAME \
      --dbname=postgres

unset PGPASSWORD

export PGPASSWORD="PASSWORD"
export AWS_ACCESS_KEY_ID="MINIO_ACCESS_KEY"
export AWS_SECRET_ACCESS_KEY="MINIO_SECRET_KEY"
aws configure set default.s3.addressing_style path

aws s3 cp "s3://BUCKET_NAME/PREFIX/postgres-TIMESTAMP.sql.gz" - --endpoint-url "http://MINIO_ENDPOINT:9000" \
  | gunzip \
  | psql \
      --host=RELEASE_NAME-postgres-proxy.GVC_NAME.cpln.local \
      --port=5432 \
      --username=USERNAME \
      --dbname=postgres

unset PGPASSWORD

base64 DATABASE.pgc | cpln workload exec RELEASE_NAME-postgres --gvc GVC_NAME --location LOCATION_NAME --container patroni-postgres --stdin --quiet -- \
  sh -c 'base64 -d | PGPASSWORD="$PG_PASSWORD" pg_restore -h RELEASE_NAME-postgres-proxy.GVC_NAME.cpln.local -U "$PG_USERNAME" -d "$PG_DATABASE" --clean --if-exists'

​

WAL-G

cpln workload exec RELEASE_NAME-postgres --gvc GVC_NAME --location LOCATION_NAME --container wal-g-backup \
  -- wal-g backup-list

​

Rotating the Password

ALTER ROLE USERNAME WITH PASSWORD 'NEW-STRONG-PASSWORD';
ALTER ROLE repl WITH PASSWORD 'NEW-STRONG-PASSWORD';

cpln secret reveal POSTGRES_CREDENTIALS_SECRET_NAME -o yaml > credentials.yaml
# edit data.password in credentials.yaml, then
cpln apply -f credentials.yaml

cpln workload force-redeployment RELEASE_NAME-postgres --gvc GVC_NAME
cpln workload force-redeployment RELEASE_NAME-postgres-pgbouncer --gvc GVC_NAME

​

Managing the Cluster With patronictl

# Every member, its location, role, timeline and replication lag
cpln workload exec RELEASE_NAME-postgres --gvc GVC_NAME --location LOCATION_NAME --container patroni-postgres \
  -- patronictl -c /tmp/patroni_config.yml list

# Move the primary to another location — a planned handover with a brief write pause
cpln workload exec RELEASE_NAME-postgres --gvc GVC_NAME --location LOCATION_NAME --container patroni-postgres \
  -- patronictl -c /tmp/patroni_config.yml switchover --candidate RELEASE_NAME-postgres-LOCATION-0 --force

# The cluster-level settings this cluster is actually running
cpln workload exec RELEASE_NAME-postgres --gvc GVC_NAME --location LOCATION_NAME --container patroni-postgres \
  -- patronictl -c /tmp/patroni_config.yml show-config

cpln workload exec RELEASE_NAME-postgres --gvc GVC_NAME --location LOCATION_NAME --container patroni-postgres \
  -- patronictl -c /tmp/patroni_config.yml edit-config --force \
     -s ttl=45 -s loop_wait=10 -s retry_timeout=15

​

Upgrading From 1.0.0 or 1.0.1

​

Migrating From Version 1

cpln helm uninstall OLD_RELEASE_NAME --gvc OLD_GVC_NAME

​

Scaling and Availability

• Losing a replica, or a whole non-primary location, is survived automatically. The primary keeps serving writes; a member that comes back with its volume intact rejoins and catches up by streaming. The primary retains up to 10 GB of WAL for an absent member (max_slot_wal_keep_size) — a durability-for-availability trade, so that a location down for hours cannot fill the primary’s volume — and a member absent longer than that re-clones from the primary automatically.
• Losing the primary, or its location, with three or more locations is an automatic failover. Patroni promotes a replica in a surviving location, every location’s proxy re-routes to it, and PgBouncer follows the proxy, so clients need no reconfiguration. The old primary rejoins as a replica when it returns (use_pg_rewind is on). Only replicas within maximum_lag_on_failover (32 MiB) of the primary are eligible — check the lag column of patronictl list.
• Replication is asynchronous, so a failover can lose the most recent transactions — those that had not yet reached the promoted replica. Check lag on the primary with pg_stat_replication, using application_name (the member name) to tell members apart.
• With two locations there is no automatic failover. The survivor holds current data but stays read-only until you promote it by hand — see Recovering From a Lost Location. This manual path has not been exercised on this template.
• A planned move is nearly seamless. patronictl switchover (see Managing the Cluster With patronictl) hands the leader lock over with a brief write pause, and a member that is shut down cleanly — a restart, a redeploy — performs the same switchover first through its pre-stop hook.
• A cpln helm upgrade can restart members in every location together, including an upgrade that changes nothing. The data survives, but expect a brief write outage while a leader is re-elected, so plan every upgrade — and every change to primaryLocation, global.locations or backup.mode — as a short planned outage with application retries in place.
• A restart of the etcd tier briefly interrupts writes and recovers on its own. Patroni’s failsafe mode lets the primary keep serving while the consensus store is unreachable as long as it can still reach every other member; the cluster re-promotes and resumes without operator action.
• Applications must retry transient errors. During a failover expect connection-level failures (server closed the connection unexpectedly, terminating connection due to administrator command) and, briefly, ERROR: cannot execute INSERT in a read-only transaction while the proxy’s health check catches up with a demoted member. Exponential backoff over a few seconds covers a failover; an upgrade needs a window of a couple of minutes.
• To scale, raise global.locations[].replicas for more members in a location (new members clone from the primary), proxy.*Replicas / pgbouncer.*Replicas for more capacity per location, and volumeset.autoscaling for storage. Do not run more than one etcd member per location — replicas affects Patroni only.
• Cost scales with write volume multiplied by the members outside the primary’s location. Each of them receives a full copy of the WAL stream, and cross-region traffic is billed. Read-mostly workloads are inexpensive to stretch; write-heavy ones are not.

​

Recovering From a Lost Location

cpln helm upgrade RELEASE_NAME oci://ghcr.io/controlplane-com/templates/postgres-multi-location \
  --version 2.0.0 --gvc GVC_NAME -f postgres-locations.yaml \
  --set postgres.credentialsSecretName=POSTGRES_CREDENTIALS_SECRET_NAME \
  --set etcd.recovery.forceNewClusterInLocation=LOCATION_NAME

​

Troubleshooting

cpln logs '{gvc="GVC_NAME", workload="RELEASE_NAME-postgres"} |= "[patroni]"' --limit 50 --since 10m

cpln workload get-deployments RELEASE_NAME-postgres --gvc GVC_NAME -o yaml

The secret POSTGRES_CREDENTIALS_SECRET_NAME no longer exists. Workload updates are paused until the
secret is added or the reference to the secret removed.

​

Important Notes

• Do not cpln helm upgrade a 1.x release onto 2.0.0. It deletes the GVC the old release created and every volume set in it. Follow Migrating From Version 1: new release, restore, then uninstall the old one.
• Create the credentials secret before installing. A reference to a secret that does not exist wedges the workload in every location with no log output; see Prerequisites for the one command that shows the reason.
• global.locations must match the GVC you install into. A declared location the GVC lacks makes a fresh member refuse to start with a named [patroni] FATAL line; a GVC location you did not list runs nothing. Read the GVC’s list with cpln gvc get GVC_NAME -o yaml before installing.
• Quorum arithmetic decides your failure tolerance. Two locations survive zero losses and promotion is manual; three give automatic failover.
• Set primaryLocation before the first install if you care where the primary is. Changing it later restarts every member to move the leader; use patronictl switchover instead.
• Credentials are read only when the cluster first bootstraps. Rotate with ALTER ROLE on the superuser and repl, then update the secret and redeploy — see Rotating the Password.
• Replication is asynchronous. A failover can lose the most recent transactions, bounded by the replication lag at the moment of failure.
• Every cpln helm upgrade can restart members in every location together, including a no-op one. Expect a brief write outage; the data survives. The same applies to changing primaryLocation, global.locations or backup.mode.
• With internalAccess.type: workload-list, list only your clients — this release’s own tiers are added for you. The bundled etcd tier is separate: etcd.internalAccess.type: workload-list requires adding //gvc/GVC_NAME/workload/RELEASE_NAME-postgres by hand.
• Leave etcd compaction at its defaults. An uncompacted consensus store goes read-only and takes the database’s failover with it.
• A WAL-G backup cannot be restored into the running cluster through this template. The archive is restorable outside the release; see Restoring a Backup.
• cpln helm uninstall deletes the volume sets, and the database with them, after a final snapshot kept 7 days. Your credentials secret is yours and is left alone.
• Cost scales with write volume multiplied by the members outside the primary’s location. Cross-region replication traffic is billed.

​

External References