Running logical replication safely on a shared Postgres platform

Logical replication is a generous feature to give a customer. It hands them a stream of every change in a database, and in the other direction it lets a server they name push changes into ours. On a shared platform, each of those is also a way to hurt someone else if the defaults are wrong. This is what we did about it on the Canner dedicated Postgres instance, and one thing we only learned because we tested it.

Physical replication is closed

A login that is allowed to replicate is not only allowed to replicate logically. Given the right connection parameters, the same permission can open a physical replication connection, which streams the whole instance, every database in it. Customers reach Postgres through a connection pooler, and during testing we found that a replication login could open a whole-instance copy through PgBouncer. We found it by trying to break our own setup, which is the point of testing.

The fix is in pg_hba: physical replication is not allowed for any customer login. What is left is logical replication, which is scoped to one database by design. The docs say it plainly: the login cannot open a whole-server copy.

A replication login that can do almost nothing

The replication login we hand out is limited to one database. It can read that database and open replication connections, and that is all. It cannot write, cannot create objects, and cannot reach your other databases. You create it with:

canner db roles <name> create feed --replication

The subscriber connects through the same address and IP allowlist as external access, which has to be on for that database, so the endpoint is TLS-only and restricted to the addresses you list.

A cap on WAL per slot

A replication slot makes Postgres keep WAL until the subscriber has read it. If a subscriber goes away, stops, or is simply slow, that WAL piles up on our disk. A stalled subscriber would eventually fill the disk of a server that belongs to a customer, and it also counts against their storage pool.

So each slot has a limit on how much WAL it may hold back: 2 GB by default, and you can change it with canner db replication <name> cap 4096. Past the cap Postgres invalidates the slot instead of letting the disk fill. That is the right failure, but it has a cost: the subscriber must be set up again from scratch.

That is why the cap has to be larger than your biggest transaction. WAL for an open transaction cannot be released until it finishes, so a single large batch job can push a healthy slot past a cap that was sized for average traffic. Each slot shows what it holds back and its health, and you get an alert when a slot is falling behind, has been inactive for a day, or is invalidated.

Inbound: applying someone else’s changes

The other direction is riskier, because a remote server is telling us what to write. A database on the dedicated instance can follow an external Postgres server, which is how you migrate with no downtime. Three decisions matter.

The apply runs without superuser rights.Changes are applied by a per-database, non-superuser role with your table owner’s rights. The reason is triggers. If the subscription were owned by a superuser, every trigger on your tables would fire as a superuser while replicated rows are applied, and a trigger is arbitrary code that customers write. With table-owner rights, a trigger runs with your rights and nothing more.

The remote address is checked. Canner connects out to a host you name, which is a classic server-side request forgery shape. The address must be a public one; private, loopback and cloud-metadata addresses are refused. The connection is encrypted unless you say otherwise with sslmode. The password is typed into a masked field and handed to Postgres, and Canner does not keep it.

The apply worker has an empty search_path. Replicated changes run with an empty search_path, so nothing gets resolved by accident. The catch is on your side, and it surprises people: trigger functions must use schema-qualified names. public.audit works; audit does not resolve, and the subscription shows Failing until you fix the function.

What you actually run

canner db subscriptions <name> add legacy --from-env SRC_URL --publication app_pub

Create the tables with the same names and columns first, and a publication on the other server. The existing rows are copied, then changes stream in. If a change cannot be applied (a missing table, a conflicting row) you get an alert and it retries by itself once the cause is fixed. Up to five subscriptions per database.

Limits worth knowing

• Logical replication is part of the dedicated instance (CA$9 a month per project), and enabling it restarts the instance.
• The source server has to be reachable at a public address. A source on a private network will not work.
• Sequences and schema changes are not carried by logical replication itself; the migration guide covers what to do at cutover.
• Canner has no read replicas and no automatic failover. Replication out is for your own subscribers, not a managed standby.

The reference for all of this is in the Postgres docs, and the migration guide walks through a cutover. If you think we have missed an attack shape, we would like to hear it.