Replication, standbys, and multi-master¶

5.1 Physical streaming replication (hot standby)¶

The short version: no configuration is needed. It works automatically.

When pg_auto_mv refreshes a materialised view it runs REFRESH MATERIALIZED VIEW, which rewrites the view's underlying data. Like every other table write in PostgreSQL, that change is recorded in the Write-Ahead Log (WAL). Physical streaming replication sends WAL to every standby server, so the refreshed view appears on the standby automatically.

On the standby:

Triggers do not fire. A standby applies WAL changes at the block level; trigger functions are never executed. The watch-table triggers pg_auto_mv installed will never fire on a standby — and they do not need to, because the standby receives the already-refreshed view via WAL.
pg_relay runs idle on a standby. Its queue poll finds no eligible rows. This is expected behaviour.
pg_auto_mv has a built-in guard: _process_pending() checks pg_is_in_recovery() at step 0 and returns immediately if true. No refresh attempt can occur on a standby.

After promotion to primary:

When a standby is promoted, it becomes read-write. New writes start firing watch-table triggers as normal. The already-connected pg_relay binary detects the server is writable and begins dispatching queue rows. No restart of pg_relay or re-registration is required.

fire_on_replica is irrelevant for physical standbys — the standby receives a complete, current view via WAL.

5.2 Multi-write-master (logical replication)¶

The key differences from physical replication:

Materialised views are not replicated logically. Logical replication carries row changes for published tables. A materialised view's data is not published and not replicated. Each node maintains its own independent copy of every materialised view and must refresh it locally.
Each node's refresh work is node-pinned. pg_auto_mv's channels are registered node-restricted (pg_relay v1.1): every queue row is stamped with the pg_relay.node_id of the node that created it, and only that node's pg_relay Processor ever dispatches it — in every pg_relay --mode (single, multi-node, leader). Even when pgrelay.queue is replicated across the cluster (a pg_relay multi-master requirement), a refresh queued on node A always executes on node A, refreshing node A's copy of the materialised view. Pinned rows are never adopted by another node, and pg_relay's duplicate suppression applies per node — node A's pending refresh row can never swallow node B's.
Default triggers do not fire during logical apply. The apply worker runs with session_replication_role = 'replica'. Only ENABLE ALWAYS triggers fire during apply. Statement-level triggers never fire during apply regardless of ENABLE ALWAYS.
Setting fire_on_replica = true changes the watch-table triggers to row-level ENABLE ALWAYS so they fire on each node in response to both local writes and writes arriving via replication. This is required for multi-master.

5.3 Which tables to include and exclude from replication¶

This is the most critical part of the multi-master setup. Getting it wrong produces subtle, hard-to-diagnose staleness.

Why operational tables must be node-local: Each node's operational state — which changes are pending, when the last refresh ran, the cooldown window — is specific to that node's own refresh history. If these tables replicate, node B receives node A's refresh completion record and wrongly believes its own copy was refreshed. Node B then skips its refresh. Node B's materialised view goes stale and stays stale.

Why config tables must be node-local: mv_oid stores the pg_class.oid of the materialised view. OIDs are assigned independently on each PostgreSQL node when DDL executes — the same OID on node A refers to a completely different object (or no object) on node B. Replicating the config table silently corrupts the configuration on every receiving node.

Exclude these tables from every replication set:

-- Example using Spock — adjust the syntax for your multi-master product
SELECT spock.repset_remove_table('default', 'pgauto_mv.materialised_views');
SELECT spock.repset_remove_table('default', 'pgauto_mv.watch_tables');
SELECT spock.repset_remove_table('default', 'pgauto_mv.schedules');
SELECT spock.repset_remove_table('default', 'pgauto_mv.mv_state');
SELECT spock.repset_remove_table('default', 'pgauto_mv.mv_events');
SELECT spock.repset_remove_table('default', 'pgauto_mv.mv_refresh_log');
SELECT spock.repset_remove_table('default', 'pgauto_mv.mv_audit_history');
SELECT spock.repset_remove_table('default', 'pgauto_mv.mv_chains');
SELECT spock.repset_remove_table('default', 'pgauto_mv.sync_log');

Include this one table:

-- This is for pgEdge's Spock, change to whatever multi-master sync-controller of the multi-master solution you are using
SELECT spock.repset_add_table('default', 'pgauto_mv.mv_truncate_signals');

mv_truncate_signals is the sole pg_auto_mv exception and must be replicated. See section 5.5.

pg_relay's own tables: when running pg_relay in --mode=multi-node or --mode=leader, pg_relay v1.1 requires pgrelay.queue and pgrelay.log to be included in the replication set (their ids are collision-free snowflakes as of pg_relay 1.1). This is safe for pg_auto_mv precisely because its channels are node-restricted — a replicated queue row still only ever runs on the node that created it. Follow pg_relay's MULTI_MASTER_DEPLOYMENT.md for the full pg_relay-side setup, including setting a distinct pg_relay.node_id on every node (the Processor refuses to start in multi-master modes without it).

5.4 Registering on each node¶

Because config tables are node-local, you must call pgauto_mv.register_mv() independently on every node. The materialised view and its unique index must also be created on each node before registering.

Use the same auto_mv_id UUID on every node. This UUID is the payload sent through the refresh queue and appears in event log and refresh log queries. If each node generates a different UUID for the same logical materialised view, cross-node queries and monitoring become difficult.

Generate a UUID once, then supply it on every node:

-- On any one node: generate a UUID to use everywhere
SELECT gen_random_uuid();
-- e.g.: a1b2c3d4-e5f6-7890-abcd-ef1234567890

Then on every node in the cluster:

-- Run this on each node independently
CREATE MATERIALIZED VIEW reporting.daily_revenue AS
SELECT date_trunc('day', created_at) AS day, sum(total_amount) AS revenue
FROM sales.orders WHERE status = 'completed' GROUP BY 1;

CREATE UNIQUE INDEX ON reporting.daily_revenue (day);
REFRESH MATERIALIZED VIEW reporting.daily_revenue;

SELECT pgauto_mv.register_mv(
    p_mv_name         => 'daily_revenue',
    p_mv_schema       => 'reporting',
    p_watch_tables    => ARRAY[
        pgauto_mv.mv_watch('sales.orders', 'INSERT,UPDATE,DELETE')
    ],
    p_refresh_lag     => 10.0,
    p_max_wait        => 60.0,
    p_cooldown        => 30.0,
    p_fire_on_replica => true,    -- REQUIRED for multi-master
    p_auto_mv_id      => 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'::uuid
);

Why p_fire_on_replica = true is required:

Without it, each node only refreshes its materialised view in response to writes that originated on that node. Writes arriving via replication from other nodes are ignored. With p_fire_on_replica = true, every node refreshes in response to all writes — local and replicated — so every node stays current.

Note on high-volume tables with fire_on_replica = true:

The trigger fires once per affected row (not once per statement). On tables with large batch operations this means more trigger executions, but only one refresh job is queued per materialised view per batch — pg_relay deduplication ensures only one refresh runs regardless of how many rows fired the trigger.

Schedules in multi-master: Create schedules independently on each node alongside register_mv(). The schedule_id referenced by materialised_views.schedule_id is node-local. It is not possible yet to share a schedule definition across nodes, this may be something we consider in a future release. Let us know if this is of interest to you.

5.5 TRUNCATE propagation across nodes¶

Due to a PostgreSQL constraint, statement-level triggers never fire during logical apply — even with ENABLE ALWAYS. Since TRUNCATE can only use statement-level triggers, a TRUNCATE on one node cannot directly trigger a refresh on another node via the normal trigger mechanism.

pg_auto_mv bridges this gap with mv_truncate_signals. When fire_on_replica = true and a TRUNCATE occurs on any node, the TRUNCATE trigger on the origin node inserts a signal row into pgauto_mv.mv_truncate_signals. That row replicates to every other node. An ENABLE ALWAYS row-level trigger on mv_truncate_signals fires on each replica when the signal row arrives, updates local state, and queues a local refresh.

The refresh each replica queues this way is node-pinned like every other pg_auto_mv job: pg_relay v1.1's notify() stamps the pin explicitly, which matters here because the signal is processed inside the logical-apply session where pg_relay's queue-stamping trigger (origin-only by design) does not fire. Each node therefore refreshes its own copy in every pg_relay --mode, including leader. This is one of the reasons pg_auto_mv v1.1 requires pg_relay >= 1.1.

What this means for you: mv_truncate_signals must be included in your replication set (see section 5.3). If it is excluded, a TRUNCATE on one node will not refresh the materialised view on the other nodes.

If your application never runs TRUNCATE on a watched table, this is a non-issue — but including the table in replication costs nothing and guards against this limitation, even if you believe it will never be realised.

5.6 One pg_relay binary per node¶

Each node must run its own dedicated pg_relay binary, connected to that node's local database via Unix socket or localhost. You cannot share a single pg_relay instance across nodes, and it will not connect through a load balancer or VIP.

Start the Processor in --mode=multi-node or --mode=leader (see pg_relay's MULTI_MASTER_DEPLOYMENT.md for choosing between them). Because pg_auto_mv's channels are node-restricted, each node's pg_relay only processes refresh jobs created on that node — in both modes; in leader mode the non-leader nodes keep draining their own pinned refresh rows while the leader additionally handles all unrestricted work. If a node has no active pg_relay, that node's refresh jobs wait (they are deliberately never adopted by another node — running node A's refresh on node B would refresh the wrong physical copy of the view). It is possible to have a number of healthy nodes and other nodes that have problems; a growing pending_restricted count in pgrelay.queue_stats() is the alerting hook for stuck nodes.

Node A                        Node B
──────────────────────        ──────────────────────
writes → triggers fire        writes → triggers fire
pgrelay.queue rows pinned A   pgrelay.queue rows pinned B
   (queue replicates both ways; the pins are preserved)
pg_relay binary               pg_relay binary
  connects to node A only       connects to node B only
  refreshes node A's MV         refreshes node B's MV

Decommissioning a node permanently: its pinned, still-pending pg_auto_mv rows will never run and never expire (pg_relay cannot tell a decommissioned node from a long outage). Discard them manually after retiring the node — see the decommissioned-node procedure in pg_relay's MULTI_MASTER_DEPLOYMENT.md.

5.7 Complete multi-master setup checklist¶

Step 1: Install pg_relay (v1.1+) and pg_auto_mv on every node, and give every node a distinct pg_relay node id.

-- On each node:
CREATE EXTENSION pg_relay;
CREATE EXTENSION pg_auto_mv;

-- On each node, a distinct small integer (1, 2, 3, ... matching your Spock
-- node ordering). pg_relay refuses to start in multi-master modes without it.
ALTER SYSTEM SET pg_relay.node_id = '1';
SELECT pg_reload_conf();

Step 2: Create the materialised view and its unique index on every node.

-- On each node:
CREATE MATERIALIZED VIEW reporting.daily_revenue AS
    SELECT date_trunc('day', created_at) AS day, sum(total_amount) AS revenue
    FROM sales.orders WHERE status = 'paid' GROUP BY 1;

CREATE UNIQUE INDEX ON reporting.daily_revenue (day);
REFRESH MATERIALIZED VIEW reporting.daily_revenue;

Step 3: Generate a shared UUID on one node, then register on every node.

-- On one node only — generate the UUID:
SELECT gen_random_uuid();
-- Copy the result; use it in the next step on every node.

-- On EVERY node:
SELECT pgauto_mv.register_mv(
    p_mv_name         => 'daily_revenue',
    p_mv_schema       => 'reporting',
    p_watch_tables    => ARRAY[
        pgauto_mv.mv_watch('sales.orders', 'INSERT,UPDATE,DELETE')
    ],
    p_refresh_lag     => 10.0,
    p_max_wait        => 60.0,
    p_cooldown        => 30.0,
    p_fire_on_replica => true,
    p_auto_mv_id      => '<paste-uuid-here>'::uuid
);

The above demonstrates that pg_auto_mv allows the supply of a unique UUID -- if the UUID is already in use, then the register_mv() will fail. Without the same UUID, pg_auto_mv has no guaranteed method of linking the replicated TRUNCATE signals.

Step 4: Exclude operational and config tables from replication; include mv_truncate_signals.

-- On each node (Spock example — adjust for your multi-master product):
SELECT spock.repset_remove_table('default', 'pgauto_mv.materialised_views');
SELECT spock.repset_remove_table('default', 'pgauto_mv.watch_tables');
SELECT spock.repset_remove_table('default', 'pgauto_mv.schedules');
SELECT spock.repset_remove_table('default', 'pgauto_mv.mv_state');
SELECT spock.repset_remove_table('default', 'pgauto_mv.mv_events');
SELECT spock.repset_remove_table('default', 'pgauto_mv.mv_refresh_log');
SELECT spock.repset_remove_table('default', 'pgauto_mv.mv_audit_history');
SELECT spock.repset_remove_table('default', 'pgauto_mv.mv_chains');
SELECT spock.repset_remove_table('default', 'pgauto_mv.sync_log');
SELECT spock.repset_add_table('default', 'pgauto_mv.mv_truncate_signals');

-- pg_relay v1.1 multi-master requires its queue and log to replicate
-- (safe for pg_auto_mv: its rows are node-pinned via node_restricted):
SELECT spock.repset_add_table('default', 'pgrelay.queue');
SELECT spock.repset_add_table('default', 'pgrelay.log');

Step 5: Start one pg_relay binary per node, in a multi-master mode.

Each binary connects to its own local node only (Unix socket or localhost), started with --mode=multi-node (each node dispatches its own work) or --mode=leader (one node dispatches all unrestricted work; every node still dispatches its own pg_auto_mv refreshes). See pg_relay's MULTI_MASTER_DEPLOYMENT.md for choosing a mode.

Step 6: Verify on each node.

-- Should return rows for this node's registrations only
SELECT mv_schema, mv_name, is_pending, last_refresh_completed
FROM pgauto_mv.get_mv_status();

-- Confirm fire_on_replica is set
SELECT mv_name, fire_on_replica FROM pgauto_mv.materialised_views;

-- Confirm the pg_auto_mv channels are node-restricted (v1.1)
SELECT channel, node_restricted FROM pgrelay.actions
WHERE channel LIKE 'pg_auto_mv.%';   -- both rows must show true

-- Confirm this node's pending refresh rows are pinned to this node
SELECT run_in_node, count(*) FROM pgrelay.queue
WHERE channel LIKE 'pg_auto_mv.%' AND dispatched_at IS NULL
GROUP BY 1;

Known limitation: TRUNCATE across nodes requires mv_truncate_signals in the replication set. If you complete steps 1–5 but exclude mv_truncate_signals from replication, TRUNCATE events on one node will not propagate refreshes to other nodes. This is a silent failure — include the table in step 4 as shown above.