Skip to content

Replication Architecture

9.1 Physical Streaming Replication

REFRESH MATERIALIZED VIEW is a heap rewrite. That write is WAL-logged and carried to all physical standbys automatically. No local refresh action on a standby is needed or permitted.

Triggers do not fire during WAL replay. pg_relay on a standby sits idle (its queue poll finds no eligible rows — logical replication is the only way data would reach the queue on a standby, which is read-only anyway). After promotion the node becomes writable, local DML begins firing triggers, and the already-connected pg_relay binary starts dispatching without any restart.

The pg_is_in_recovery() guard at step 0 of _process_pending() is the belt-and-suspenders safety: if a queue row somehow existed on a standby (manually inserted, or replicated despite the node-local design), the function returns immediately before attempting any write.

9.2 Multi-Write-Master (Logical Replication / pgEdge / Spock)

Why all operational tables must be node-local:

  • mv_state contains last_refresh_completed, which drives cooldown_until and refresh_due_at. If node A's refresh state replicates to node B, node B will see a false "already refreshed recently" condition and suppress its own refresh. Each node's matview heap is independent — node A's refresh does not update node B's matview.
  • mv_events and mv_refresh_log use bigserial primary keys which diverge across nodes, producing conflicts on merge.
  • materialised_views contains mv_oid, which is assigned independently by each PostgreSQL instance at DDL execution time. An OID from node A is meaningless (or refers to a different object) on node B.

Replication exclusion list: materialised_views, watch_tables, mv_state, mv_events, mv_refresh_log, mv_audit_history, sync_log, mv_chains — all must be excluded from every replication set.

mv_truncate_signals must be included.

fire_on_replica = true mode:

Row-level ENABLE ALWAYS triggers fire during logical apply (the apply worker runs with session_replication_role = 'replica'; only ENABLE ALWAYS triggers fire). This means every node refreshes its matview in response to both local writes and replicated writes.

The _on_truncate_signal() trigger guards against the origin-node double-fire with: 

IF current_setting('session_replication_role') <> 'replica' THEN RETURN NULL; END IF;
On origin nodes, session_replication_role is 'origin' — the trigger is a no-op. On replica nodes it is 'replica' — the trigger fires.

pg_relay v1.1 multi-master modes and node-restricted channels:

pg_relay v1.1 runs its Processor in one of three modes: --mode=single (one writable primary; the default), --mode=multi-node (Spock active-active, each node dispatches its own rows, a dead node's rows are adopted), and --mode=leader (Spock active-active, the lowest live write-master dispatches all unrestricted rows). In multi-node/leader the pgrelay.queue and pgrelay.log tables are replicated and every node must set a distinct pg_relay.node_id GUC (enforced at Processor startup).

Location-independent dispatch is wrong for pg_auto_mv: the same (channel, payload) on two nodes means two different pieces of physical work (each node's own matview heap). pg_auto_mv 1.1 therefore registers both of its channels with node_restricted = true, and pg_relay guarantees:

  1. Every pg_auto_mv queue row is stamped with the creating node's pg_relay.node_id (pgrelay.queue.run_in_node) by a BEFORE INSERT trigger — covering pgrelay.notify() calls, retry rows, and pg_auto_mv's direct scheduled-refresh inserts alike.
  2. Only that node's Processor ever dispatches the row, in every mode. In leader mode non-leader nodes keep draining their own pinned refresh rows while the leader handles everything else.
  3. The row is never adopted or reassigned — a dead node's scheduled-refresh chain rows wait for it to return, keeping mv_state.scheduled_queue_id valid.
  4. p_deduplicate suppresses only same-node pending rows, so node A's replicated pending row can never swallow node B's deferred or retry notify.

In --mode=single (pg_relay.node_id unset ⇒ node 0) every restricted row is stamped run_in_node = 0 and behaviour is identical to pg_auto_mv 1.0. On a physical standby nothing changes either — the pg_is_in_recovery() guard still applies.

Multi-master deployment procedure:

  1. Install pg_relay >= 1.1 on every node; set a distinct pg_relay.node_id per node (ALTER SYSTEM SET pg_relay.node_id = '<n>'; SELECT pg_reload_conf();)
  2. CREATE EXTENSION pg_auto_mv on every node (after pg_relay — Spock does not replicate DDL)
  3. SELECT pgauto_mv.register_mv(... p_fire_on_replica => true, p_auto_mv_id => '<shared-uuid>') on every node — the same UUID pinned across all nodes
  4. Replicate pgrelay.queue and pgrelay.log (a pg_relay v1.1 multi-master requirement); exclude all pgauto_mv operational tables from the replication set; include pgauto_mv.mv_truncate_signals
  5. One pg_relay binary per node, connected to its local database only (Unix socket or localhost), started with --mode=multi-node or --mode=leader

Decommissioning a node: a node-restricted queue row pinned to a node that never returns will never run and never expire. After permanently retiring a node, discard its pending pg_auto_mv rows manually — see "Decommissioned nodes" in pg_relay's MULTI_MASTER_DEPLOYMENT.md.