Moving production Postgres from an RDS Multi-AZ instance to a Multi-AZ DB cluster

At Gambchamp we moved a live production PostgreSQL from an RDS Multi-AZ instance to a Multi-AZ DB cluster: a writer plus two readable standbys and a separate reader endpoint. The application had to be repointed, roles, data and grants had to land one to one, and data loss had to be as close to zero as possible.

It was a planned migration, not an incident. Three things still bit.

Trap one: “maintenance” did not stop writes

We put the application into maintenance mode and compared exact per-table row counts between two moments. Five tables were still changing: payment-provider callbacks, notifications and visits kept inserting. A single dump of a live source is always behind.

The real freeze was scaling every writer to zero, then proving it: counts stable, no writing connections, no idle-in-transaction sessions. On top of that, on the source:

ALTER DATABASE app SET default_transaction_read_only = on;

plus terminating the remaining application connections. That blocks any late write and also prevents a split-brain after the cutover.

Trap two: RDS will not give you the passwords

pg_dumpall --globals-only is the usual way to copy roles. On RDS even the master cannot read pg_authid, so neither hashes nor anything else comes out. pg_dumpall -g --no-role-passwords still gives exact attributes, memberships and role settings — that part is worth having.

Passwords had to come from elsewhere. The application role kept its production password, taken from the application’s Kubernetes secret and verified by an actual login on the source. Auxiliary roles got new passwords. A role that was already expired was created expired.

Trap three: the network dominated

The dump over VPN crawled: 110 MB in about 25 minutes. The same pg_dump from an EC2 host in the same VPC finished in 31 seconds. Passwords went in over stdin, never as command arguments.

Restore details that are easy to miss

• On RDS the master is not the owner of the database, so DROP DATABASE fails. A clean target was made by dropping and recreating the public schema as the owning role. CREATE EXTENSION still needs the master.
• Two restore errors are expected. ALTER DEFAULT PRIVILEGES FOR ROLE <app> fails under the master and has to be replayed as that role. publication already exists appears because publications are database-wide and survive DROP SCHEMA.

Verify before opening

• a full per-table count diff equal to zero;
• every sequence compared with the maximum of its column — a lagging sequence means duplicate key on the first insert;
• a real login as each role and a write probe as the application role.

Only then the application’s host moved to the cluster writer endpoint and reads to the reader endpoint. The old instance stayed isolated: read-only and with zero application connections.

The rollback is simple until the first write lands on the cluster: reset default_transaction_read_only on the old instance and repoint the host back. After the first write it is a point of no return.

Takeaways

• Prove a freeze with a count diff. Callbacks and webhooks keep writing after “maintenance”.
• On RDS plan to reissue passwords or take them from application secrets; you cannot copy them from the database.
• Run dump and restore from inside the VPC. Here it was about 50 times faster.
• Check sequences against their columns before opening writes.
• Monitoring does not follow the database by itself — repointing the application is only half of the cutover.