> ## Documentation Index > Fetch the complete documentation index at: https://docs.controlplane.com/llms.txt > Use this file to discover all available pages before exploring further. # 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](/reference/location) to deploy into. * For backup: an AWS or GCP [cloud account](/guides/create-cloud-account) and a storage bucket. ### Installation To install, follow the instructions for your preferred method: Browse, install, and manage templates visually Manage templates from your terminal }> Declare templates in your Terraform configurations Pulumi Icon Streamline Icon: https://streamlinehq.com } > Declare templates in your Pulumi programs ## Configuration The default `values.yaml` for this template: ```yaml theme={null} 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: ```yaml theme={null} volumeset: capacity: 10 autoscaling: enabled: true maxCapacity: 100 minFreePercentage: 10 scalingFactor: 1.2 ``` ### Internal Access Control which workloads can connect to pgEdge and pgcat: ```yaml theme={null} 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: ```sql theme={null} -- 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 ```sql theme={null} 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: ```sql theme={null} -- 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 1. Create your S3 bucket and note its name and region. 2. If you do not have a Cloud Account set up, refer to the docs to [Create a Cloud Account](/guides/create-cloud-account). Note the cloud account name. 3. Create a new AWS IAM policy with the following JSON (replace `YOUR_BUCKET_NAME`): ```json theme={null} { "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.cloudAccountName` to your cloud account name. 5. Set `backup.aws.policyName` to the policy created in step 3. ### GCS Prerequisites 1. Create your GCS bucket and note its name. 2. If you do not have a Cloud Account set up, refer to the docs to [Create a Cloud Account](/guides/create-cloud-account). Note the cloud account name. 3. Add the `Storage Admin` role to the GCP service account created by the cloud account. 4. Set `backup.gcp.cloudAccountName` to your cloud account name. ### Restoring a Backup **AWS S3:** ```sh theme={null} 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:** ```sh theme={null} 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 Official pgEdge documentation Spock multi-master replication extension pgcat connection pooler View the source files, default values, and chart definition