pgEdge Distributed PostgreSQL

Overview

pgEdge is an active-active distributed PostgreSQL cluster using Spock multi-master replication. Every node accepts both reads and writes simultaneously, and data written to any node replicates to all others automatically. The cluster spans multiple geographic locations with configurable replicas per location, providing a globally distributed, fault-tolerant database with no single point of failure.

Architecture

pgEdge — Stateful workload running PostgreSQL 17 with the Spock extension. All nodes are active writers connected in a full-mesh replication ring. Each replica gets its own persistent volume.
pgcat — Connection pooler providing a single virtual endpoint for applications. Routes writes to the designated primary and distributes reads across all nodes.
Spock — Multi-master logical replication extension included in the pgEdge image. Handles cross-node replication with last-update-wins conflict resolution.

What Gets Created

Stateful pgEdge Workload — PostgreSQL 17 with the Spock extension. One set of replicas per configured location, each with its own persistent volume.
Standard pgcat Workload — Connection pooler that routes application traffic to pgEdge nodes. Autoscales on RPS.
Cron Backup Workload (optional) — Runs pg_dump on a schedule and uploads the result to AWS S3 or GCS. Only active in the first configured location.
Volume Set — ext4 general-purpose SSD volumes with 7-day snapshots. One volume per replica.
Identity & Policy — An identity bound to the workloads with reveal permissions on all secrets. When backup is enabled, the identity is also configured with the appropriate cloud account and IAM policy.
Secrets — A dictionary secret holding database credentials, an opaque secret containing the pgcat TOML configuration, and an opaque secret containing the node startup script.
GVC — A GVC spanning all configured locations.

Prerequisites

At least one Control Plane location to deploy into.
For backup: an AWS or GCP cloud account and a storage bucket.

Installation

To install, follow the instructions for your preferred method:

UI

Browse, install, and manage templates visually

CLI

Manage templates from your terminal

Terraform

Declare templates in your Terraform configurations

Pulumi

Declare templates in your Pulumi programs

Configuration

The default values.yaml for this template:

gvc:
  name: pgedge-gvc
  locations: # For replicas: use 1 for dev/testing, 3 for production
    - name: aws-us-west-2
      replicas: 3
    - name: aws-us-east-2
      replicas: 3
    - name: aws-eu-central-1
      replicas: 3

image: ghcr.io/pgedge/pgedge-postgres:17-spock5-standard

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

postgres:
  username: postgres
  password: password
  database: mydb

multiZone: false  # Set to true to spread replicas across availability zones within each location

volumeset:
  capacity: 10  # Initial capacity in GiB (minimum is 10)
  autoscaling:
    enabled: false  # Set to true to enable autoscaling
    maxCapacity: 100  # Maximum capacity in GiB when autoscaling is enabled
    minFreePercentage: 10  # Minimum free percentage to trigger scaling
    scalingFactor: 1.2  # How much to scale up when triggered

pgcat:
  image: ghcr.io/postgresml/pgcat:latest
  poolMode: transaction  # options: session, transaction, statement
  defaultPoolSize: 25    # Real Postgres connections pgcat maintains per pool
  maxClientConn: 1000    # Maximum client connections pgcat accepts
  resources:
    cpu: 500m
    memory: 256Mi
  minReplicas: 2
  maxReplicas: 4

internal_access: # Sets both pgedge and pgcat workloads
  type: same-gvc  # options: same-gvc, same-org, workload-list
  workloads:  # Only used when type is workload-list
    #- //gvc/GVC_NAME/workload/WORKLOAD_NAME

backup:
  enabled: false
  image: controlplanecorporation/pg-backup:17.1.0
  schedule: "0 2 * * *"   # daily at 2am UTC

  resources:
    cpu: 100m
    memory: 128Mi

  provider: aws  # Options: aws or gcp

  aws:
    bucket: my-backup-bucket
    region: us-east-1
    cloudAccountName: my-backup-cloudaccount
    policyName: my-backup-policy
    prefix: pgedge/backups  # folder where backups will be stored

  gcp:
    bucket: my-backup-bucket
    cloudAccountName: my-backup-cloudaccount
    prefix: pgedge/backups  # folder where backups will be stored

GVC & Locations

Each independent pgEdge deployment must use a unique gvc.name. Configure one or more locations, each with a replica count:

Environment	Replicas per location
Dev / testing	1
Production	3

Set multiZone: true to spread replicas across availability zones within each location. Verify your selected locations support multiple availability zones before enabling.

Volume Set

Set the initial storage capacity (minimum 10 GiB). Optionally enable autoscaling to expand as data grows:

volumeset:
  capacity: 10
  autoscaling:
    enabled: true
    maxCapacity: 100
    minFreePercentage: 10
    scalingFactor: 1.2

Internal Access

Control which workloads can connect to pgEdge and pgcat:

internal_access:
  type: same-gvc  # Options: same-gvc, same-org, workload-list
  workloads:
    # Only used when type is workload-list:
    #- //gvc/GVC_NAME/workload/WORKLOAD_NAME

same-gvc — Allow access from all workloads in the same GVC.
same-org — Allow access from all workloads in the org.
workload-list — Allow access only from the specified workloads.

pgcat Pool Modes

pgcat multiplexes application connections into a smaller pool of real database connections, reducing overhead and protecting Postgres from connection exhaustion.

Mode	Behavior
`transaction`	Connection held only for the duration of a transaction. Best for most web and API workloads. Not compatible with `SET` variables, temporary tables, or advisory locks.
`session`	Connection held for the entire client session. Compatible with all Postgres features but provides less connection reuse.
`statement`	Connection returned after every statement. Transactions are not supported. Rarely used.

Connecting

Connect through pgcat for all application traffic. Each application only needs this single endpoint — pgcat handles routing to the appropriate pgEdge node:

Host:     {release-name}-pgcat.{gvc-name}.cpln.local
Port:     5432
Database: {postgres.database}
Username: {postgres.username}
Password: {postgres.password}

Schema Changes (DDL)

Spock replicates row-level changes (INSERT, UPDATE, DELETE) automatically. DDL (CREATE TABLE, ALTER TABLE, etc.) must be broadcast using spock.replicate_ddl() so it executes on all nodes.

Creating a table

Run on one node only — the DDL replicates to all nodes, then add the table to the replication set:

-- Step 1: Run on ONE node -- creates the table on all nodes
SELECT spock.replicate_ddl('CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  amount numeric,
  created_at timestamptz DEFAULT now()
);');

-- Step 2: Run on ONE node -- adds the table to the replication set on all nodes
SELECT spock.repset_add_table('default', 'orders'::regclass);

Step 2 is required because Spock suppresses event triggers during replication apply to prevent loops. The repset_add_table call itself replicates to all other nodes automatically.

Other DDL

SELECT spock.replicate_ddl('ALTER TABLE orders ADD COLUMN status text DEFAULT ''pending'';');
SELECT spock.replicate_ddl('DROP TABLE orders;');

Primary Keys

Use uuid primary keys instead of serial/bigserial. Each node maintains its own sequence, so auto-increment integers will collide when the same ID is generated on multiple nodes simultaneously:

-- Good: globally unique, no conflicts
id uuid PRIMARY KEY DEFAULT gen_random_uuid()

-- Avoid: causes duplicate key conflicts under concurrent multi-node writes
id serial PRIMARY KEY

Backup

When backup.enabled is true, a cron workload runs pg_dump on the configured schedule. Because every pgEdge node holds a full copy of the data, the backup job connects to replica-0 of the first configured location. Backup runs only in the first location — other locations have the backup workload suspended.

AWS S3 Prerequisites

Create your S3 bucket and note its name and region.
If you do not have a Cloud Account set up, refer to the docs to Create a Cloud Account. Note the cloud account name.
Create a new AWS IAM policy with the following JSON (replace 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/*"
            ]
        }
    ]
}

Set backup.aws.cloudAccountName to your cloud account name.
Set backup.aws.policyName to the policy created in step 3.

GCS Prerequisites

Create your GCS bucket and note its name.
If you do not have a Cloud Account set up, refer to the docs to Create a Cloud Account. Note the cloud account name.
Add the Storage Admin role to the GCP service account created by the cloud account.
Set backup.gcp.cloudAccountName to your cloud account name.

Restoring a Backup

AWS S3:

export PGPASSWORD="PASSWORD"
aws s3 cp "s3://BUCKET_NAME/PREFIX/BACKUP_FILE.sql.gz" - \
  | gunzip \
  | psql \
      --host=RELEASE_NAME-pgcat.GVC_NAME.cpln.local \
      --port=5432 \
      --username=USERNAME \
      --dbname=DATABASE
unset PGPASSWORD

GCS:

export PGPASSWORD="PASSWORD"
gsutil cp "gs://BUCKET_NAME/PREFIX/BACKUP_FILE.sql.gz" - \
  | gunzip \
  | psql \
      --host=RELEASE_NAME-pgcat.GVC_NAME.cpln.local \
      --port=5432 \
      --username=USERNAME \
      --dbname=DATABASE
unset PGPASSWORD

Important Notes

GVC naming — Each independent pgEdge deployment must use a unique GVC name.
Minimum replicas — Use at least 3 replicas per location for production to survive a node loss within a location.
Conflict resolution — Concurrent writes to the same row from different nodes are resolved by last-update-wins based on commit timestamp. For workloads requiring stronger consistency, route writes for a given entity to a single node using application-level logic.
multiZone — Verify your selected locations support multiple availability zones before enabling.

External References

pgEdge Documentation

Official pgEdge documentation

Spock Documentation

Spock multi-master replication extension

pgcat Documentation

pgcat connection pooler

pgEdge Template

View the source files, default values, and chart definition

Documentation Index

​Overview

​Architecture

​What Gets Created

​Prerequisites

​Installation

UI

CLI

Terraform

Pulumi

​Configuration

​GVC & Locations

​Volume Set

​Internal Access

​pgcat Pool Modes

​Connecting

​Schema Changes (DDL)

​Creating a table

​Other DDL

​Primary Keys

​Backup

​AWS S3 Prerequisites

​GCS Prerequisites

​Restoring a Backup

​Important Notes

​External References

pgEdge Documentation

Spock Documentation

pgcat Documentation

pgEdge Template

Overview

Architecture

What Gets Created

Prerequisites

Installation

Configuration

GVC & Locations

Volume Set

Internal Access

pgcat Pool Modes

Connecting

Schema Changes (DDL)

Creating a table

Other DDL

Primary Keys

Backup

AWS S3 Prerequisites

GCS Prerequisites

Restoring a Backup

Important Notes

External References