Logical Replication

Logical replication streams a table’s row-level changes (insert / update / delete / truncate) out of Postgres so an external system can consume them — the Neon-style change-data-capture (CDC) primitive. You define a publication (the source side) naming the tables and operations to publish; a subscriber then consumes that stream.

AnvilBase exposes a per-project publications management API to create and manage publications, and an opt-in switch to turn on logical decoding.

Off by default — opt-in

Logical decoding is disabled by default. AnvilBase ships with wal_level = 'replica' (what PITR and the read replica already require), so a plain docker compose up and any running stack are byte-for-byte unaffected. You opt in explicitly by setting ANVILBASE_WAL_LEVEL=logical and restarting Postgres (below). Publications can be created at any wal_level, but they only decode and stream at wal_level = 'logical'.

How it works

A publication is the source side of logical replication: it names a set of tables (or FOR ALL TABLES) plus the DML operations to publish.
Publications live in the project’s own database (platform_<id> — the project UUID with hyphens removed, 32 hex chars), so they are tenant-isolated by construction — a publication in one project is invisible to every other project.
The control plane manages publications through the management API. Every response carries logical_replication_enabled, which is true only when the server’s wal_level is logical. When it is false, the publication exists but will not stream until you enable logical decoding.
wal_level = 'logical' is a strict superset of replica, so enabling it keeps PITR and the streaming read replica working exactly as before.

Enable logical decoding

wal_level is a restart-only Postgres setting — that is why this is opt-in.

1. Set the env var

In your .env (or compose environment):

ANVILBASE_WAL_LEVEL=logical

Only replica (the default) and logical are honoured. Any other value — notably minimal, which would break PITR and read replicas — falls back to replica.

2. Restart Postgres

docker compose restart postgres

The init scripts apply the value via ALTER SYSTEM SET wal_level = 'logical', which takes effect on the next server start. Confirm it took:

docker compose exec postgres psql -U supabase_admin -d postgres -c "SHOW wal_level;"
# wal_level
# -----------
# logical

AnvilBase also ensures max_replication_slots and max_wal_senders are at least 10 (the Postgres 15 defaults), giving logical replication slots ample headroom.

You can check the live status for any project without restarting anything:

curl -H "Authorization: Bearer $ANVILBASE_ADMIN_TOKEN" \
  https://<host>/api/v1/projects/<project_id>/publications/status
# { "wal_level": "logical", "logical_replication_enabled": true, "note": null }

Publications API

All routes are under /api/v1/projects/{id}/publications and operate on the project’s own database. They are gated by platform auth + RBAC scopes: create / alter / delete require admin; list / get / status require read. Every publication and table name is validated and quoted server-side — only ordinary identifiers (letters, digits, underscores; ≤ 63 chars) are accepted, and named tables must already exist in the project’s public schema.

Create a publication

POST /api/v1/projects/{id}/publications

{
  "name": "orders_cdc",
  "tables": ["orders", "order_items"],
  "operations": ["insert", "update", "delete"]
}

tables — the tables to publish. Omit or pass an empty array for FOR ALL TABLES.
operations — any of insert, update, delete, truncate. Omit or pass an empty array for all four (the Postgres default).

FOR ALL TABLES needs superuser

Postgres requires superuser to create a FOR ALL TABLES publication. The control plane runs project DDL as the project’s non-superuser role (the tenant-isolation security model), so an empty/omitted tables request is rejected with a clear 403. List the specific tables instead — the FOR TABLE form works for the table owner and is the recommended, more granular shape anyway. (You can still add tables later with PATCH.)

Returns 201 with the created publication, including the logical_replication_enabled flag:

{
  "name": "orders_cdc",
  "all_tables": false,
  "operations": ["insert", "update", "delete"],
  "tables": ["order_items", "orders"],
  "logical_replication_enabled": false
}

A 409 is returned if a publication with that name already exists; a 400 if a named table does not exist or an identifier is invalid.

logical_replication_enabled: false means the publication was created but will not stream until you set ANVILBASE_WAL_LEVEL=logical and restart Postgres. This is intentional — you author publications first, enable decoding second.

List / get publications

GET /api/v1/projects/{id}/publications
GET /api/v1/projects/{id}/publications/{name}

Each entry reports name, all_tables, operations, the explicit tables (empty for FOR ALL TABLES), and logical_replication_enabled.

Add or drop tables

PATCH /api/v1/projects/{id}/publications/{name}

{ "add_tables": ["shipments"], "drop_tables": ["order_items"] }

ADD runs before DROP. Each named table is validated; ADD tables must exist. (This applies to publications that name explicit tables, not FOR ALL TABLES ones.)

Drop a publication

DELETE /api/v1/projects/{id}/publications/{name}

Returns 204. A 404 is returned if the publication does not exist.

Decoding status

GET /api/v1/projects/{id}/publications/status

{
  "wal_level": "replica",
  "logical_replication_enabled": false,
  "note": "logical decoding is OFF (wal_level != 'logical'). Publications can be created but will not stream. Set ANVILBASE_WAL_LEVEL=logical and restart Postgres to enable."
}

Subscriptions (consuming the stream)

Subscriptions — the consume side (CREATE SUBSCRIPTION, which connects out to the publisher with a connection string + credentials) — are not part of this release. Today you create the publication on AnvilBase (the source) and point an external logical-replication consumer at the project’s database. A managed subscriptions API is a documented follow-up.

Safety notes

Default off, no regression. With ANVILBASE_WAL_LEVEL unset the stack runs at wal_level = 'replica' exactly as before — PITR and the read replica are unaffected.
Restart required. Enabling logical decoding changes a restart-only setting; publications you created earlier start decoding once Postgres restarts at wal_level = 'logical'.
Tenant-isolated. Publications live in each project’s own database; the API is scoped to a single project and never touches another.