Amazon Aurora for PostgreSQL

Aurora for PostgreSQL is PostgreSQL-compatible, and the setup is nearly identical to Amazon RDS, with one critical difference you need to understand before you begin.

Constraints

All the RDS constraints apply, plus:

The Processor must connect to the writer node. Aurora readers are read-only replicas. The durable queue and the audit log both live on the writer, and processing an event writes to both of them (and usually the action itself writes too). The Processor must connect exclusively to the cluster writer endpoint — never to a reader endpoint or an individual instance endpoint. A Processor pointed at a reader would fail every single write.

Your cluster's endpoints:

Endpoint type	Format	Use
Writer (use this)	my-cluster.cluster-abc123.us-east-1.rds.amazonaws.com	Processor connection
Reader (do not use)	my-cluster.cluster-ro-abc123.us-east-1.rds.amazonaws.com	Application reads only

The writer endpoint's DNS record is automatically updated by Aurora during a failover, to point at the new primary. The Processor's own reconnect loop handles this transparently — you do not need to change the Processor's configuration when a failover happens.

Step 1 — Deploy the pgrelay schema

Connect using the writer endpoint:

psql -h my-cluster.cluster-abc123.us-east-1.rds.amazonaws.com \
     -U postgres \
     -d your_database \
     -c "CREATE SCHEMA pgrelay;"

psql -h my-cluster.cluster-abc123.us-east-1.rds.amazonaws.com \
     -U postgres \
     -d your_database \
     -f sql/pg_relay--1.0.sql

psql -h my-cluster.cluster-abc123.us-east-1.rds.amazonaws.com \
     -U postgres \
     -d your_database \
     -f sql/pg_relay--1.0--1.1.sql

Step 2 — Enable login on the pgrelay role

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

Step 3 — Configure the Processor connection

Create the .pgpass file using the writer endpoint's hostname:

my-cluster.cluster-abc123.us-east-1.rds.amazonaws.com:5432:your_database:pgrelay:choose-a-strong-password

chmod 600 ~/.pgpass

Processor environment variables (note the writer endpoint in PGHOST):

PGHOST=my-cluster.cluster-abc123.us-east-1.rds.amazonaws.com
PGPORT=5432
PGDATABASE=your_database
PGUSER=pgrelay
PGPASSFILE=/path/to/.pgpass
PGSSLMODE=require

Failover behaviour

When an Aurora failover happens, the current primary goes down and Aurora promotes a reader to take over as the new primary. During this transition:

1. The Processor's connection drops.
2. The Processor's reconnect loop activates, logging reconnecting on each attempt.
3. Aurora updates the writer endpoint's DNS record to point at the new primary (typically within 30–60 seconds).
4. The Processor reconnects (picking up the newly-resolved writer endpoint) and resumes polling; its next poll drains any events that queued up during the outage.
5. The Processor logs pg_relay reconnected and returns to normal operation.

No manual intervention is required. You do not need a separate standby Processor instance — a single Processor pointed at the writer endpoint handles every failover scenario automatically.

---

Continue to Azure Database for PostgreSQL.