Skip to content

Amazon RDS for PostgreSQL

Constraints

  • Custom extensions cannot be installed on RDS. pg_relay must be deployed using the manual SQL approach described in Why the Extension Can't Just Be Installed.
  • The postgres user on RDS has the rds_superuser role, not a true PostgreSQL superuser. It has enough privileges for every pg_relay setup step regardless.
  • Password authentication is required for all network connections — Unix socket authentication is not available on RDS.
  • SSL is enabled by default. PGSSLMODE=require is recommended for the Processor.

Step 1 — Deploy the pgrelay schema

Connect to your RDS instance as the postgres user. First create the schema, then run the SQL files to create every pg_relay object:

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

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

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

Verify the deployment succeeded:

SELECT routine_name
FROM information_schema.routines
WHERE routine_schema = 'pgrelay'
ORDER BY routine_name;

You should see every pgrelay function listed. If any are missing, check the psql output from the previous step for errors.

Step 2 — Enable login on the pgrelay role

The SQL deployment creates the pgrelay database role without the ability to log in. Enable login and set a password:

ALTER ROLE pgrelay WITH LOGIN PASSWORD 'choose-a-strong-password';

Step 3 — Configure the Processor connection

On the machine where the Processor will run, create a .pgpass file so it can authenticate without a password sitting in the environment:

your-instance.region.rds.amazonaws.com:5432:your_database:pgrelay:choose-a-strong-password

chmod 600 ~/.pgpass

Set the Processor's environment variables:

PGHOST=your-instance.region.rds.amazonaws.com
PGPORT=5432
PGDATABASE=your_database
PGUSER=pgrelay
PGPASSFILE=/path/to/.pgpass
PGSSLMODE=require

See Running the Processor for how to run and supervise the Processor binary on AWS infrastructure.

Verification

Start the Processor and confirm it logs pg_relay started. Then register a test channel and fire a notification:

SELECT pgrelay.register('cloud_test', 'SELECT 1', 'RDS connectivity test');
SELECT pgrelay.notify('cloud_test', 'hello');

Within about one second, the Processor runs the action and records the result. Successful events are only logged at debug level, so run with --debug if you want to see an ok: cloud_test, id: … line — the reliable way to confirm success is the audit log itself:

SELECT channel, payload, status, elapsed_ms, actioned_at
FROM pgrelay.log
ORDER BY actioned_at DESC
LIMIT 5;

Clean up the test channel:

SELECT pgrelay.unregister('cloud_test');

Continue to Amazon Aurora, or skip to Running the Processor once your database is ready.