pgEdge

pgEdge is a distributed PostgreSQL distribution for edge scenarios, built on Spock multi-master logical replication.

Overview

Pigsty integrates pgEdge through pg_mode: pgedge and delivers it through the standard PostgreSQL cluster workflow:

• pgedge: a PG17-compatible kernel
• spock: Active-active multi-master logical replication
• snowflake: Distributed unique sequences
• lolor: Large object logical replication compatibility layer

In the current Pigsty repository, pgEdge is shipped as 17.9, together with spock 5.0.5, snowflake 2.4, and lolor 1.2.2. From the client side, pgEdge is still PostgreSQL wire compatible, so psql, JDBC/ODBC, DBeaver, and similar tools work as usual.

The delivery model in Pigsty is: validate the kernel on a single node first, then expand to a multi-node replication topology. The template handles the kernel, extensions, monitoring, backup, and access control out of the box, but the actual multi-master topology still needs to be designed around your workload consistency and conflict strategy.

Installation

Use the built-in Pigsty template:

./configure -c pgedge
./deploy.yml

The template pre-installs spock, snowflake, and lolor in the meta database. After deployment, verify the kernel and extensions:

psql -d meta -c "SELECT version();"
psql -d meta -c "SELECT extname, extversion FROM pg_extension WHERE extname IN ('spock','snowflake','lolor') ORDER BY 1;"

For the full template and parameters, see: pgedge config template.

Configuration

Key parameters in the pgedge template (matching conf/pgedge.yml):

pg_mode: pgedge
pg_version: 17
pg_packages: [ pgedge, pgsql-common ]
pg_extensions: [ spock, snowflake, lolor ]
pg_libs: 'spock, lolor, pg_stat_statements, auto_explain'
pg_databases:
  - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [spock, snowflake, lolor] }

If you plan to grow into a multi-node multi-master topology, it is better to configure logical replication capacity and snowflake.node explicitly:

pg_parameters:
  wal_level: logical
  max_replication_slots: 16
  max_wal_senders: 16
  'snowflake.node': 1

snowflake.node must be unique on every writable node, otherwise distributed IDs will collide.

Usage

The common workflow in Pigsty is still: validate the kernel on a single node first, then expand into a multi-node Spock replication topology.

If you need these capabilities in a business database as well, create the extensions first:

CREATE EXTENSION IF NOT EXISTS spock;
CREATE EXTENSION IF NOT EXISTS snowflake;
CREATE EXTENSION IF NOT EXISTS lolor;

Then use the Spock SQL API or the pgEdge CLI to create nodes, replication sets, and subscriptions. If your schema already uses serial or identity, plan the snowflake sequence migration before enabling multi-master writes, otherwise cross-node primary key collisions are likely.

Notes

• Replication in pgEdge is organized per database, not as an instance-wide “turn everything into multi-master” switch.
• Replicated tables should have a PRIMARY KEY or an appropriate REPLICA IDENTITY.
• UNLOGGED and TEMPORARY tables do not participate in Spock logical replication.
• Spock configuration and operations typically require superuser privileges, so production deployments should define privilege boundaries clearly.
• If your workload depends on large object replication, use lolor explicitly rather than assuming native large objects will replicate correctly.
• Cross-region multi-master is not a checkbox feature. Network latency, conflict handling, and the write model all need to be evaluated first.

Related Docs

• PGSQL Kernel Overview
• pgedge config template
• PGSQL kernel mode config
• pgEdge official docs
• Spock Limitations
• Snowflake Sequences