Skip to content

Before You Start

This book walks through a complete, working pg_relay setup from scratch. It assumes pg_relay is already installed (see the User Guide if not) and that you are logged in as a regular database user named app_user — not a superuser.

The example builds three tables (records, records_json, records_audit), wires them together through pg_relay channels, and then runs through insert, update, bulk, and scheduled-purge scenarios.

Start the Processor

Open a terminal and start pg_relay with debug logging, so you can watch what it does:

pg_relay --debug

If it connects successfully you will see something like this:

% pg_relay --debug
{"ts":"2026-04-22T19:49:45.439987+10:00","level":"debug","msg":"running preflight self-check","instance":1}
{"ts":"2026-04-22T19:49:45.445433+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"grant:queue_has_work","detail":"EXECUTE on pgrelay.queue_has_work()"}
{"ts":"2026-04-22T19:49:45.445454+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"grant:queue_pending_ids","detail":"EXECUTE on pgrelay.queue_pending_ids()"}
{"ts":"2026-04-22T19:49:45.445459+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"grant:process_one","detail":"EXECUTE on pgrelay.process_one(bigint)"}
{"ts":"2026-04-22T19:49:45.445464+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"grant:_queue_claim","detail":"EXECUTE on pgrelay._queue_claim(bigint)"}
{"ts":"2026-04-22T19:49:45.445469+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"grant:_fetch_action","detail":"EXECUTE on pgrelay._fetch_action(text)"}
{"ts":"2026-04-22T19:49:45.445478+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"grant:_write_log","detail":"EXECUTE on pgrelay._write_log(text,text,text,text,bigint,bigint)"}
{"ts":"2026-04-22T19:49:45.445482+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"grant:_queue_mark_done","detail":"EXECUTE on pgrelay._queue_mark_done(bigint)"}
{"ts":"2026-04-22T19:49:45.445486+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"grant:_queue_delete","detail":"EXECUTE on pgrelay._queue_delete(bigint)"}
{"ts":"2026-04-22T19:49:45.44549+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"grant:_queue_insert_retry","detail":"EXECUTE on pgrelay._queue_insert_retry(bigint,text,text,integer)"}
{"ts":"2026-04-22T19:49:45.445494+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"grant:preflight","detail":"EXECUTE on pgrelay.preflight()"}
{"ts":"2026-04-22T19:49:45.445498+10:00","level":"debug","msg":"preflight_ok","instance":1,"check":"replication:queue_not_published","detail":"pgrelay.queue is not in any PostgreSQL publication"}
{"ts":"2026-04-22T19:49:45.445506+10:00","level":"info","msg":"pg_relay started","instance":1,"workers":1}

Leave this terminal open — you will see events logged here as you work through the rest of this book.

If pg_relay fails to connect

The most common cause is that the pgrelay role has no login password set. Connect as a superuser and enable login:

-- As superuser (postgres):
ALTER ROLE pgrelay WITH LOGIN PASSWORD 'choose-a-strong-password';

Then create a .pgpass file so the Processor can read the password without it appearing on the command line or in environment variables.

# ~/.pgpass  (chmod 600)
localhost:5432:your_database:pgrelay:choose-a-strong-password

chmod 600 ~/.pgpass
pg_relay --verbose

macOS — allowing pg_relay as an unidentified developer

If macOS blocks the program the first time you run it, you will see a pop-up saying "pg_relay cannot be opened because it is from an unidentified developer."

Method 1 — from the terminal:

xattr -d com.apple.quarantine /usr/local/bin/pg_relay

Method 2 — from System Settings:

  1. Open System Settings → Privacy & Security.
  2. Scroll down to the Security section.
  3. You will see: "pg_relay was blocked from use because it is not from an identified developer."
  4. Click Allow Anyway, then confirm in the dialog that appears.
  5. Re-run pg_relay --verbose in the terminal.

With the Processor running, continue to One-Time Superuser Setup.