Skip to content

Installation

Step 1 — Install pg_relay

pg_auto_mv v1.2 requires pg_relay v1.1.2 or later, for two independent reasons: v1.1 registers pg_auto_mv's channels as node-restricted (a pg_relay 1.1.0 feature), and v1.2 stores schedules as pgrelay.schedule values using the array overloads added in the pg_relay 1.1.2 schedule type. The install fails with a clear error if either is missing. Install pg_relay first and confirm the binary is running before proceeding. See the pg_relay repository for full instructions.

Step 2 — Install the pgrelay.schedule type

pg_auto_mv stores refresh schedules as pgrelay.schedule values — the shared recurrence type that pg_relay publishes for use across all pg_relay-related applications.

This is a required, separate installation step. pgrelay.schedule is deliberately not part of the pg_relay extension: pg_relay's own SQL and its Processor binary never reference it, so it is shipped in the pg_relay source tree under schedule/ and installed manually. It is also not included in the pg_relay release tarball — you need the repository (or the raw file) to get it.

Run it as a superuser, in the same database, after CREATE EXTENSION pg_relay (it adds objects to the existing pgrelay schema):

# From a clone of the pg_relay repository, checked out at the release you installed
psql -d your_database -v ON_ERROR_STOP=1 -f schedule/install.sql

Confirm it worked:

-- Confirms the type is present AND is 1.1.2+ (on_day_times and the
-- pgrelay.schedule[] overloads, both of which pg_auto_mv requires)
SELECT pgrelay.describe(ARRAY[pgrelay.on_day_times('mon=0500', 'tue=1730')]);
-- Mon at 05:00; Tue at 17:30 UTC

If this fails on a database that already has an older schedule type, re-run schedule/install.sql from pg_relay 1.1.2+. It upgrades the type in place — it never drops it — so existing schedule values and any consumer table columns using them are preserved.

Because this type is not an extension object, CREATE EXTENSION pg_auto_mv cannot declare a dependency on it (requires = 'pg_relay' in the control file covers only the extension). pg_auto_mv's install script therefore checks for it explicitly and fails with an actionable message if it is absent.

Two consequences worth knowing:

  • Upgrades. ALTER EXTENSION pg_relay UPDATE does not touch pgrelay.schedule. If a future pg_relay release changes the type, re-run schedule/install.sql yourself.
  • Removal. schedule/uninstall.sql, or anything that drops the domain with CASCADE, will break pg_auto_mv's schedules table. Do not run it on a database where pg_auto_mv is installed.

Full type documentation: https://pg-relay.pebbleit.com.au/latest/schedule-type/01-overview/

Step 3 — Get the pg_auto_mv source

git clone https://gitlab.com/pebble-it/pg_auto_mv.git
cd pg_auto_mv

Step 4 — Install the extension files

pg_auto_mv is a pure SQL extension — no compilation is required. make install copies the control file and SQL script into your PostgreSQL extension directory. Note that this text assumes PostgreSQL 18, for different versions from 15, change any references to 18 below to your version.

make install

If pg_config is not on your PATH (common when multiple PostgreSQL versions are installed), pass it explicitly:

# Change the reference of '18' to your version of PostgreSQL
make PG_CONFIG=/usr/pgsql-18/bin/pg_config install

You will need write access to the PostgreSQL extension directory. On most systems this means running with sudo:

sudo make PG_CONFIG=/usr/pgsql-18/bin/pg_config install

Step 5 — Create the extension in your database

Connect to the target database and run:

CREATE EXTENSION pg_auto_mv;

This creates the pgauto_mv schema and all catalog tables, and inserts the required rows into pgrelay.actions so that pg_relay knows how to dispatch refresh and sync jobs. If pg_relay is not already installed, the command fails — install pg_relay first.

CREATE EXTENSION pg_auto_mv also self-registers pg_auto_mv in pg_relay's application registry, pgrelay.pg_relay_applications (registered as version 1.1.0). This lets other pg_relay-based applications verify a compatible pg_auto_mv is present, the same way pg_auto_mv itself verifies pg_relay's version at install time.

If a different role installs pg_auto_mv than the one that installed pg_relay: pgrelay.register_application() has EXECUTE revoked from PUBLIC in pg_relay — only a superuser, the role that owns the pg_relay extension, or a role explicitly granted access via pgrelay.grant_user() can call it. In the common case where the same admin/superuser role installs both extensions, this is automatic — no action needed. If a different role will run CREATE EXTENSION pg_auto_mv, grant it access first (as a superuser or the pg_relay owner):

SELECT pgrelay.grant_user('the_role_installing_pg_auto_mv');

Without this, CREATE EXTENSION pg_auto_mv fails with a plain "permission denied for function register_application" error during the self-registration step.

Note: the schema is named pgauto_mv, not pg_auto_mv. PostgreSQL 16+ blocks creation of schemas with a pg_ prefix.

Step 6 — Verify

-- Should return two rows, both with node_restricted = true
SELECT channel, action, node_restricted FROM pgrelay.actions
WHERE channel LIKE 'pg_auto_mv%';

Expected output:

channel action node_restricted
pg_auto_mv.refresh_materialised_view SELECT pgauto_mv._process_pending($1::uuid) true
pg_auto_mv.sync_mv CALL pgauto_mv.sync_mv(p_auto_mv_id => NULLIF($1, '')::uuid) true

node_restricted = true (new in v1.1) tells pg_relay that these jobs have node-local side effects: every queued refresh runs on the node that queued it, in every pg_relay --mode. On a single-node install this changes nothing observable; in multi-master it is what keeps every node's materialised views current. See section 5.

Upgrading from pg_auto_mv 1.0

  1. Upgrade pg_relay to v1.1 first (ALTER EXTENSION pg_relay UPDATE; after installing its v1.1 files — see the pg_relay documentation). On a multi-master cluster, bring every node to pg_relay v1.1 before upgrading pg_auto_mv on any of them.
  2. Install the pg_auto_mv v1.1 files (make install as in Step 3).
  3. In each database using pg_auto_mv:
ALTER EXTENSION pg_auto_mv UPDATE;

The upgrade flags both pg_auto_mv channels node_restricted = true, back-fills the node pin (run_in_node) on any still-pending pg_auto_mv queue rows (scheduled refreshes queued before the upgrade), and records version 1.1.0 in pgrelay.pg_relay_applications. No registration, schedule, or trigger changes are needed — existing auto_mvs carry over unchanged, and no pg_relay Processor restart is required. On a multi-master cluster run the upgrade on every node (DDL is not replicated).