Skip to content

Prerequisites

The following must be in place before installing pg_auto_mv:

  1. pg_relay v1.1.2 or later is installed and operational. The pg_relay schema (pgrelay) must exist in the target database at version 1.1.2 or later — pg_auto_mv registers its channels as node-restricted (a pg_relay 1.1.0 feature) and stores schedules as pgrelay.schedule values (a 1.1.2 feature), and its install verifies both. The pg_relay Processor binary must also be running and connected to that database. See the pg_relay Cloud Setup Guide for pg_relay installation instructions.

  2. The pgrelay.schedule type is installed. pg_auto_mv stores refresh schedules as pgrelay.schedule values. This type is not part of the pg_relay extension and is not in the pg_relay release tarball — it ships in the pg_relay repository under schedule/ and is deployed by running its SQL directly, which suits managed services well since that is how pg_relay itself is deployed there:

# From a clone of the pg_relay repository, at the release you deployed
psql "$PGURI" -v ON_ERROR_STOP=1 -f schedule/install.sql

Deploying it needs no filesystem access to the server and no extension mechanism, so it works on every managed platform in this guide. Verify with the check in Confirming pg_relay is ready below.

  1. Admin access to the target database. You need a role equivalent to rds_superuser (AWS), the admin user (Azure), or cloudsql_superuser (GCP) — the same role you used to deploy pg_relay.

  2. psql available on the machine from which you will run the setup commands.

  3. The pg_auto_mv SQL files. v1.2 is deployed as the base script plus the 1.0→1.1 and 1.1→1.2 upgrade scripts, applied in order. Obtain them by cloning the repository:

git clone https://gitlab.com/pebble-it/pg_auto_mv.git
cd pg_auto_mv
# The SQL files are at: sql/pg_auto_mv--1.0.sql and sql/pg_auto_mv--1.0--1.1.sql

Alternatively, download and extract the release archive from the releases page:

curl -LO https://gitlab.com/pebble-it/pg_auto_mv/-/releases/latest/downloads/pg_auto_mv--1.1.sql.tar.gz
tar -xzf pg_auto_mv--1.1.sql.tar.gz

Confirming pg_relay is ready

Before proceeding, verify that the pg_relay schema exists and that its action registry is accessible:

SELECT COUNT(*) FROM pgrelay.actions;

If this query returns a row count (even zero), pg_relay is installed and pg_auto_mv can proceed. If the query fails with schema "pgrelay" does not exist, pg_relay is not yet installed — complete the pg_relay setup first.

Then confirm the pgrelay.schedule type from Prerequisite 2 is present:

-- Confirms the type is present AND is 1.1.2+ (the pgrelay.schedule[] overloads
-- and on_day_times, 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 with function ... does not exist, either the schedule type has not been deployed or it predates 1.1.2 — run schedule/install.sql from pg_relay 1.1.2+ before continuing. It upgrades in place and preserves existing values. pg_auto_mv's SQL file checks for both the type and the 1.1.2 functions up front, and stops with an actionable message naming whichever is missing.

Application registry self-registration

Deploying the pg_auto_mv SQL self-registers pg_auto_mv in pg_relay's application registry (pgrelay.pg_relay_applications; the base script registers 1.0.0 and the 1.0→1.1 script updates it to 1.1.0), the same way it verifies pg_relay's own version there before proceeding.

pgrelay.register_application() has EXECUTE revoked from PUBLIC — only a superuser-equivalent role, the role that deployed pg_relay, or a role explicitly granted access via pgrelay.grant_user() can call it. Because Prerequisite 2 above already has you use the same role that deployed pg_relay, this is automatic on every platform in this guide — no extra grant step is needed. If you deploy pg_auto_mv with a different role than the one that deployed pg_relay, grant it access first:

SELECT pgrelay.grant_user('the_role_deploying_pg_auto_mv');

Without this, running sql/pg_auto_mv--1.0.sql fails with a plain "permission denied for function register_application" error at the self-registration step.