pglogical 2

pglogical 2 is a PostgreSQL extension that provides logical streaming replication using a publish/subscribe model. It allows for selective replication of tables, rows, or columns, and supports use cases such as major version upgrades, full database replication, and data merging from multiple upstream servers.

Core Concepts

• Nodes: PostgreSQL database instances.
• Providers and Subscribers: The roles taken by Nodes in the replication stream.
• Replication Set: A collection of tables to be replicated.

Architectural Model

• Per-database level: Unlike physical streaming replication which works at the server level, pglogical operates on a per-database basis.
• One-to-Many: One Provider can feed multiple Subscribers without additional disk write overhead.
• Many-to-One: One Subscriber can merge changes from several origins and handle conflicts via configurable resolution.
• Cascading: Implemented via changeset forwarding.

To ensure data integrity when table structures differ, pglogical_output sends relation metadata. The downstream client is responsible for caching this metadata.

Caching Rules for Downstream Clients:

• Mandatory Caching: The downstream must always cache relation metadata when received.
• Replacement: If a new metadata message for an existing relation arrives, it must replace the old entry in the cache.
• Purging: The downstream may only purge metadata from its cache after receiving a specific purge message for that relation from the upstream.
• Syncing: The downstream cache is a 'slave cache' that follows the upstream's management. The downstream informs the upstream of its supported maximum cache size during the startup phase.

Note: Purge messages from the upstream are notifications that the upstream no longer expects the downstream to retain that entry; the downstream does not have to purge immediately.

Unlike simple JSON decoders (like wal2json), pglogical_output is a comprehensive replication component that manages:

• Format Negotiation: Negotiating the communication protocol between client and server.
• Sender-side Filtering: Using pluggable hooks to filter data (e.g., replication origin filtering) before it is sent.
• Binary Protocol: Using a custom binary protocol for speed and compactness, avoiding the overhead of JSON encoding/decoding and allowing efficient raw binary datum transfer.
• Column Metadata: Sending column names (and potentially type metadata) before each row that first refers to a relation. This ensures that even if table attnos (attribute numbers) differ between upstream and downstream due to DDL changes, data is mapped to the correct columns.

pglogical_output is designed as a reusable component for other solutions rather than a standalone extension with its own SQL catalogs.

When using the pglogical_output plugin, be aware of the following technical constraints:

• Transaction Ordering: Logical decoding serializes transactions in commit order. pglogical_output cannot replay interleaved concurrent transactions, which may cause latency if large transactions queue up behind smaller ones.
• Replica Identity: To replicate INSERT or UPDATE operations, the target table must have a PRIMARY KEY or a non-partial, columns-only UNIQUE index. This provides the REPLICA IDENTITY required to identify which tuple to update.
• UNLOGGED Tables: UNLOGGED tables are not written to WAL and therefore cannot be replicated via logical or physical replication. Use trigger-based solutions if these must be replicated.
• Update Payload: Logical decoding does not track 'dirty' vs 'clean' fields. In UPDATE operations, unchanged fields are typically sent in the payload unless they are variable-length TOASTable types large enough to be stored out-of-line.

To use pglogical, ensure your environment meets the following criteria:

Version Requirements

• PostgreSQL: Provider and subscriber nodes must run PostgreSQL 9.4 or newer.
• Conflict Detection/Filtering: PostgreSQL 9.5+ is required for replication origin filtering and conflict detection.
• Postgres-XL: Subscribers can be Postgres-XL 9.5+.

Extension and Schema Requirements

• Installation: The pglogical extension must be installed and initialized on both provider and subscriber using CREATE EXTENSION pglogical;.
• Schema Matching: Tables on the provider and subscriber must have the same names and reside in the same schema.
• Column Matching: Tables must have the same columns and data types. CHECK and NOT NULL constraints on the subscriber must be the same as or more permissive (weaker) than those on the provider.
• Primary Keys: Tables must have identical PRIMARY KEYs. It is not recommended to add additional UNIQUE constraints other than the PRIMARY KEY.

The pglogical output plugin is a logical decoding plugin designed to extract a change stream from a PostgreSQL database and transmit it to a client over a network using an efficient protocol.

Key Characteristics:

• Scope: Replicates a single database (or a subset of tables/origins), not the entire PostgreSQL instance.
• Efficiency: Only changed rows are sent; it excludes index changes, vacuum activity, etc.
• Reliability: Uses PostgreSQL replication slots to ensure the stream is crash-safe. If a client disconnects, the server accumulates changes in a queue, allowing the client to resume from the last processed message upon reconnection.
• Compatibility: Unlike physical streaming replication, the logical change stream is compatible across different PostgreSQL versions and can be consumed by non-PostgreSQL clients.
• Resource Note: Because it uses replication slots, server-side resources (like WAL files) are consumed to maintain the queue even when no client is connected.