AWS RDS for PostgreSQL

Constraints

• Custom extensions cannot be installed on standard RDS. pg_auto_mv must be deployed using the manual SQL approach described in Section 2.
• The postgres user on RDS has the rds_superuser role, not true superuser. It has sufficient privileges for all pg_auto_mv setup steps.
• Use the same instance endpoint and credentials you used when setting up pg_relay.

Step 1 — Create the pgauto_mv Schema

The schema is normally created by the extension mechanism. When deploying manually, create it first:

psql -h your-instance.region.rds.amazonaws.com \
     -U postgres \
     -d your_database \
     -c "CREATE SCHEMA pgauto_mv;"

Step 2 — Deploy the pg_auto_mv Objects

Run the SQL file to create all pg_auto_mv types, tables, views, and functions, and to register its channels in pgrelay.actions:

psql -h your-instance.region.rds.amazonaws.com \
     -U postgres \
     -d your_database \
     -f sql/pg_auto_mv--1.0.sql \
     -f sql/pg_auto_mv--1.0--1.1.sql

If you see errors, check that pg_relay is installed (pgrelay schema exists) and that you connected with a role that has CREATE privileges on the database.

Step 3 — Verify the Installation

Confirm that the action registrations were inserted into pg_relay and that the core tables exist:

-- pg_relay action registrations
SELECT channel, action
FROM pgrelay.actions
WHERE channel LIKE 'pg_auto_mv.%'
ORDER BY channel;

-- Core pg_auto_mv tables
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'pgauto_mv'
ORDER BY table_name;

The first query must return two rows: one for pg_auto_mv.refresh_materialised_view and one for pg_auto_mv.sync_mv. The second query must return the full table list: materialised_views, mv_audit_history, mv_events, mv_refresh_log, mv_state, mv_truncate_signals, watch_tables.

Step 4 — Register a Materialised View

No additional configuration is needed. The pg_relay Processor that is already running will pick up pg_auto_mv refresh events automatically. Register an existing materialised view to begin automatic refresh:

SELECT pgauto_mv.register(
    p_mv_name      => 'your_mv_name',
    p_mv_schema    => 'your_schema',
    p_watch_tables => ARRAY[
        pgauto_mv.watch('your_schema.your_table')
    ],
    p_refresh_lag  => 5.0,
    p_cooldown     => 10.0
);

Within one second of the next DML event on the watch table, the pg_relay Processor will call pgauto_mv._process_pending() and refresh the materialised view.

---