The Spock Extension

The Spock extension provides multi-master (active-active) replication for PostgreSQL versions 15 and 16. The extension leverages both the pgLogical and BDR2 Open Source projects as a solid foundation to build upon for this enterprise-class extension.

You build, install, and create the extension like any other PostgreSQL extension. For more information about using PostgreSQL extensions, visit the website (opens in a new tab).

For more information about the Spock extension, visit the pgEdge Github repo (opens in a new tab).

Conflict-Free Delta-Apply Columns (Conflict Avoidance)

Logical multi-master replication can get into trouble when running sums (such as a YTD balance). Unlike other solutions, we do NOT have a special data type for this; any numeric data type works (including numeric, float, double precision, int4, int8, etc).

This feature is powerful and simple in its implementation:

• When a conflicting update occurs on a log_old_value column:
  • First, the old value for that column is captured to the WAL files.
  • Second, the new value comes in the transaction to be applied to a subscriber.
  • Before the new value overwrites the old value, a delta value is created from the above two steps and it is correctly applied.

Note that on a conflicting transaction, the delta column will be correctly applied. This conflict resolution strategy applies to non-delta columns (normally last-update-wins). As a special safety-valve feature, if you ever need to re-set a log_old_value column, you can temporarily alter the column to log_old_value is false.

Conflict Configuration options

Some Spock behaviors can be configured using configuration options that can be either set in postgresql.conf or via ALTER SYSTEM SET.

• spock.conflict_resolution Sets the resolution method for any detected conflicts between local data and incoming changes. Accepted values are:

  • error - replication will stop on error if conflict is detected and manual intervention is needed to resolve the issue.
  • apply_remote - always apply the change that's conflicting with local data.
  • keep_local - keep the local version of the data and ignore the conflicting change that is coming from the remote node.
  • last_update_wins - the version of data with newest commit timestamp will be kept (this can be either the local or remote version).
  • first_update_wins - the version of the data with oldest timestamp will be kept (this can be either local or remote version).

  The keep_local, last_update_wins and first_update_wins settings require the track_commit_timestamp PostgreSQL parameter to be enabled.

• spock.conflict_log_level Sets the log level for reporting detected conflicts when spock.conflict_resolution is set to anything other than error. The default is LOG. If the parameter is set to a value lower than log_min_messages, resolved conflicts are not written to the server log.

  The primary use for this setting is to suppress logging of conflicts. The possible values (opens in a new tab) are the same as for the log_min_messages PostgreSQL setting.

• spock.batch_inserts Tells Spock to use a batch insert mechanism if possible. The batch mechanism uses PostgreSQL internal batch insert mode which is also used by the COPY command.