pgEdge Distributed PostgreSQL

cpln gvc get GVC_NAME -o json

cpln gvc add-location GVC_NAME --location aws-us-east-2

cpln secret create-dictionary --name my-pgedge-credentials \
  --entry username=myuser \
  --entry password='YOUR-STRONG-PASSWORD' \
  --entry database=mydb

ERROR:  library "spock_output" may not be used as an output plugin

psql "host=replica-0.RELEASE_NAME-pgedge.LOCATION.GVC_NAME.cpln.local user=USERNAME dbname=DATABASE" \
  -c "ALTER SYSTEM SET output_plugin_libraries = pgoutput, test_decoding, spock_output;" \
  -c "SELECT pg_reload_conf();"

SELECT subscription_name, status FROM spock.sub_show_status();

# ─── Locations ────────────────────────────────────────────────────────────────
# This chart deploys into the GVC you install into — it does NOT create one.
# Every location listed here MUST already exist in that GVC, or the pgEdge nodes
# refuse to initialise with a named error (see Prerequisites in the README).
# Extra locations in the GVC are fine: nothing pgEdge-related runs in them.
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:
  # 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 silently — `cpln logs` returns nothing at all. See Prerequisites
  # in the README for the exact `cpln secret create-dictionary` command.
  credentialsSecretName: my-pgedge-credentials

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:v1.2.0 # pinned: `latest` makes installs non-reproducible
  poolMode: transaction  # options: session, transaction, statement
  defaultPoolSize: 25    # Real Postgres connections pgcat maintains per pool
  resources:
    cpu: 500m
    memory: 256Mi
  minReplicas: 2         # per location
  maxReplicas: 4         # per location

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; list only your clients --
              # this release's pgedge and pgcat workloads are added automatically
    #- //gvc/GVC_NAME/workload/WORKLOAD_NAME

backup:
  enabled: false
  image: ghcr.io/controlplane-com/backup-images/postgres-backup:17.1.0
  schedule: "0 2 * * *"   # daily at 2am UTC — runs in locations[0] only

  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

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

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

-- Run on EVERY node, connecting to each directly (not through pgcat)
CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  amount numeric,
  created_at timestamptz DEFAULT now()
);

-- Step 1: 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: on every OTHER node -- adds the table to that node's replication set
SELECT spock.repset_add_table('default', 'orders'::regclass);

SELECT * FROM spock.tables WHERE relname = 'orders';

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

-- 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

SELECT subscription_name, status FROM spock.sub_show_status();

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

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

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