repository·main·Indexed 20 days ago
https://github.com/pgedge/spockA multi-master replication extension for PostgreSQL supporting versions 15 through 19. Spock enables data synchronization across multiple nodes, providing features for DDL replication, conflict resolution using a 'last_update_wins' strategy, and configurable exception behavior. It requires a patched PostgreSQL source tree for installation and specific configuration of postgresql.conf, including wal_level set to 'logical' and track_commit_timestamp enabled.
Spock is a multi-master (active-active) replication extension designed for pgEdge Distributed and Enterprise Postgres. It is built upon the pgLogical open-source project to provide enterprise-class replication capabilities.
Supported PostgreSQL Versions:
The Spock extension provides several management features for PostgreSQL to handle distributed data and replication logic. Key capabilities include:
Lolor extension to replicate large objects.When a crash occurs and the resource.dat snapshot becomes stale (i.e., the file LSN is less than the replication origin LSN), Spock executes a routine called recover_progress_timestamps_from_commit_ts().
This routine:
pg_commit_ts table backward from the latest XID.remote_commit_ts and prev_remote_ts fields in shared memory.last_updated_ts to this recovered timestamp to ensure the view provides a conservative (lower bound) estimate of replication lag rather than reporting zero lag.Termination Criteria:
To ensure data integrity even when upstream and downstream table architectures differ (e.g., due to column drops or type changes), spock_output sends metadata (at minimum, column names) before each row that first refers to a specific relation.
Key behaviors for clients/downstreams:
By default, Spock uses a custom binary protocol (proto_format = "native"). If you set the proto_format parameter to json, the output plugin will emit JSON instead. This is intended primarily for debugging and diagnostics purposes. The JSON format supports all the same hooks as the native protocol.
# To enable JSON protocol
proto_format = "json"The spock_output plugin is a reusable component designed for logical decoding. Unlike standard tools like wal2json, spock_output manages more than just format conversion; it handles:
Note that spock_output is intended to be a component of larger solutions and does not have its own extension script. Applications using it are expected to define their own SQL-level catalogs and interact with them via hooks to avoid mixing application data with plugin-specific catalogs.
When a new node (n4) receives a full data sync from a primary node (n1), it implicitly receives data that originally came from other nodes (n2, n3). If you simply enable the direct subscriptions from n2/n3 to n4, n4 will receive duplicate data.
To prevent this, you must advance the replication slots on the original source nodes (n2, n3) to skip the data already present on n4.
Workflow:
lag_tracker table on the new node (n4) to find the commit_timestamp for the last change where origin_name matches the original source (e.g., 'n2') and receiver_name is the new node ('n4').get_lsn_from_commit_ts() with that timestamp to find the corresponding LSN.Spock logs replication exceptions to the spock.exception_log table. Recent improvements have enhanced the quality of these logs:
unavailable placeholder, Spock now records the real cause of a discarded transaction, including the failing command's message and its SQLSTATE.TRANSDISCARD or SUB_DISABLE modes, the original error message is preserved for the failing row, while bystander rows are marked with error_message = NULL (stored as "unavailable") to distinguish them from the root cause.When adding a node manually, you must prepare the replication stream to ensure no data is lost or duplicated. This involves a 'bookmarking' process:
spock.sync_event() on the source node. This returns a Log Sequence Number (LSN) which acts as a bookmark in the replication stream.spock_output plugin. This ensures changes are queued even while the subscription is disabled.spock.sub_create() with enabled := false. This prepares the node to pull data from the source without immediately starting the sync process.-- 1. Trigger sync event on source node (e.g., n2) and get LSN
SELECT *
FROM dblink(
'host=127.0.0.1 dbname=inventory port=5433 user=alice password=1safepassword',
'SELECT spock.sync_event()'
) AS t(sync_lsn pg_lsn);
-- 2. Store LSN in a temp table
CREATE TEMP TABLE IF NOT EXISTS temp_sync_lsns (
origin_node text PRIMARY KEY,
sync_lsn text NOT NULL
);
INSERT INTO temp_sync_lsns (origin_node, sync_lsn) VALUES ('n2', '0/1A7D1E0');
-- 3. Create replication slot on source node
SELECT *
FROM dblink(
'host=127.0.0.1 dbname=inventory port=5433 user=alice password=1safepassword',
'SELECT slot_name, lsn
FROM pg_create_logical_replication_slot(
''spk_inventory_n2_sub_n2_n4'',
''spock_output''
)'
) AS t(slot_name text, lsn pg_lsn);
-- 4. Create disabled subscription on the new node (e.g., n4)
SELECT *
FROM dblink(
'host=127.0.0.1 dbname=inventory port=5435 user=alice password=1safepassword',
'SELECT spock.sub_create(
subscription_name := ''sub_n2_n4'',
provider_dsn := ''host=127.0.0.1 dbname=inventory port=5433 user=alice password=1safepassword'',
replication_sets := ARRAY[''default'', ''default_insert_only'', ''ddl_sql''],
synchronize_structure := false,
synchronize_data := false,
forward_origins := ARRAY[]::text[],
apply_delay := ''0''::interval,
enabled := false,
force_text_transfer := false,
skip_schema := ARRAY[]::text[]
)'
) AS t(subscription_id oid);Spock uses a spock.reserved_object catalog to handle special behaviors for certain schemas or extensions. Reserved objects can be configured for three behaviors:
exclude_from_dump: The object is omitted from structure-sync dumps to prevent restore failures on subscribers that already have the object.block_in_repset: Prevents the object (or its tables) from being added to any replication set.replicate_ddl: If false (for schemas), AutoDDL keeps DDL targeting this schema node-local. It runs where issued but is not shipped to other nodes.Note: Reserved object configurations are node-local and must be applied to every node in the cluster. They are preserved across pg_dump/pg_restore.
Adding a custom reserved object:
Use spock.reserved_object_add to define your own rules for a schema or extension.
Removing a custom reserved object:
Use spock.reserved_object_remove(name, type).
-- Keep a custom schema out of structure sync and replication sets
SELECT spock.reserved_object_add('ace', 'schema');
-- Exclude an extension from the dump but allow its tables in repsets
SELECT spock.reserved_object_add('myext', 'extension', p_block_in_repset := false);
-- Keep a custom schema node-local (AutoDDL will not replicate its DDL)
SELECT spock.reserved_object_add('myschema', 'schema', p_replicate_ddl := false);
-- Remove a custom reservation
SELECT spock.reserved_object_remove('ace', 'schema');Spock supports row and column filters with the following constraints:
oid, xmin) in filters.random() or now() in row filters can cause replication to stop due to inconsistent results.CURRENT_USER will return the replication session's user, not the original user who performed the write.Spock's apply_delay is interval-based and is designed to accommodate time zone shifts, such as Daylight Savings Time (DST). The configured interval remains constant regardless of DST changes.
Important Considerations:
recovery_min_apply_delay can be affected by DST, potentially requiring database service restarts twice a year to correct intervals.