Manage and monitor logical replication

This page describes how to monitor logical replication in Lakebase, how replication interacts with scale to zero and branches, and how to resolve common problems. For setup, see Replicate data into Lakebase and Replicate data from Lakebase.

Monitor replication

Any Postgres role can query the replication catalogs on this page, with two exceptions. The subconninfo column of pg_subscription can contain the connection password and is restricted, so select specific columns instead of using SELECT *. In pg_stat_replication, most columns are hidden unless your role has pg_read_all_stats, which members of databricks_superuser have.

On a subscriber, confirm the subscription exists and is enabled:

SELECT subname, subenabled FROM pg_catalog.pg_subscription;

On a publisher, confirm the publication exists:

SELECT pubname FROM pg_catalog.pg_publication;

Check the per-table sync status on the subscriber. When every table reads Ready (fully live), the initial copy is done and the subscription is streaming:

SELECT
    srrelid::regclass AS table_name,
    CASE srsubstate
        WHEN 'i' THEN 'Initializing'
        WHEN 'd' THEN 'Data copying (in progress)'
        WHEN 's' THEN 'Synchronized (catching up)'
        WHEN 'r' THEN 'Ready (fully live)'
    END AS sync_status
FROM pg_catalog.pg_subscription_rel;

Check subscription status and lag:

SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM pg_catalog.pg_stat_subscription;

Compare row counts on the publisher and the subscriber to confirm a table is in sync:

SELECT COUNT(*) FROM public.playing_with_lakebase;

On a publisher, list replication slots and how far each has advanced:

SELECT slot_name, slot_type, active, restart_lsn
FROM pg_catalog.pg_replication_slots;

On Lakebase, the results also include the internal physical slots wal_proposer_slot and wal_retention_slot. Lakebase manages these slots, and they are not part of logical replication.

Logical replication and scale to zero

A Lakebase compute with an active subscriber does not scale to zero, which can increase compute usage.

Check for active subscribers:

SELECT *
FROM pg_catalog.pg_stat_replication
WHERE application_name <> 'walproposer';

Replication slots and storage

A replication slot makes the publisher retain write-ahead log (WAL) until the subscriber confirms it has received the changes. If the subscriber stops consuming changes, for example because it is disabled, disconnected, failing, or falling behind, WAL accumulates on the publisher. This is the most common cause of serious problems with logical replication:

  • The publisher can run out of storage. Retained WAL grows until the publisher's storage is full, which can stop the database from accepting writes and cause an outage.
  • Dropping the slot does not release storage immediately. Postgres removes the retained WAL only when it recycles WAL segments at a later checkpoint. If the publisher has already run out of storage, that recycling might not complete.
  • The risk applies to external publishers too. When Lakebase subscribes to an external database, WAL accumulates on that external source if the subscription stops consuming changes.

Monitor retained WAL

On the publisher, check how much WAL each logical slot retains and whether it is active:

SELECT slot_name, active, wal_status,
       pg_catalog.pg_size_pretty(pg_catalog.pg_wal_lsn_diff(pg_catalog.pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_catalog.pg_replication_slots
WHERE slot_type = 'logical';

Run this query regularly while replication is active. Investigate when retained_wal keeps growing or active is false. A wal_status of extended means the slot is retaining more WAL than the publisher normally keeps. A wal_status of unreserved or lost means the slot exceeded max_slot_wal_keep_size and is losing, or has lost, WAL it needs, so the subscription must be recreated.

Recover from WAL buildup

  1. Find the slot that is retaining WAL with the query in Monitor retained WAL.

  2. If the subscriber can recover, fix the cause, such as a network issue, a disabled subscription, or a failing apply, so the subscriber catches up and the slot advances.

  3. If you no longer need the subscription, drop it on the subscriber. If the subscriber is unreachable, drop the slot directly on the publisher. A slot must be inactive before you can drop it:

    SELECT pg_catalog.pg_drop_replication_slot('<slot_name>');

  4. Wait for the publisher to recycle the retained WAL. On a self-managed publisher that is close to running out of storage, add storage first so recycling can complete.

Automatic removal of inactive slots on Lakebase

When Lakebase is the publisher, it automatically removes a replication slot that has been inactive for approximately 40 hours, to prevent the slot from retaining WAL indefinitely. A slot becomes inactive when its subscriber stops consuming changes and the slot stops advancing. This can happen when a subscriber is deactivated or becomes unavailable, or when a subscriber is configured with a long replication delay.

To keep a slot from being removed, make sure its subscriber stays connected and keeps consuming changes. If the subscriber uses a configurable replication delay or frequency, keep it under 40 hours so the slot keeps advancing. If a slot is removed and you still need it, recreate the subscription on the subscriber.

External publishers do not remove inactive slots automatically. On a self-managed publisher, set max_slot_wal_keep_size to cap the WAL a slot can retain. If a slot exceeds the limit, Postgres invalidates it and you must recreate the subscription.

Branch restore removes replication slots

Restoring a branch deletes all replication slots on that branch. Recreate publications and subscriptions after a branch restore.

Child branches do not inherit subscriptions

Subscriptions defined on a parent branch are not duplicated on its child branches. Lakebase drops them from a child branch before that branch's compute starts, so two computes do not replicate the same subscription. This applies when you create, reset, or restore a child branch. Recreate the subscription on the child branch if you need replication there.

Dropping a database removes its subscriptions

Before dropping a database in response to a DROP DATABASE command or operation, Lakebase drops any logical replication subscriptions defined in that database.

Replication roles

A dedicated replication role must have the REPLICATION attribute. Roles created in the Lakebase App, Azure Databricks CLI, or REST API have this attribute, whether or not they are members of databricks_superuser. Roles created with SQL do not receive the REPLICATION attribute automatically. To create one with SQL, include the attribute, for example CREATE ROLE replication_user WITH LOGIN REPLICATION PASSWORD '<password>';. To add it to an existing role, run ALTER ROLE <role_name> REPLICATION;. Both statements require a role that has the CREATEROLE and REPLICATION attributes, such as the project owner's role.

Check a role's privileges:

SELECT rolname, rolreplication
FROM pg_catalog.pg_roles
WHERE rolname = '<role_name>';

Decoder plugins

Lakebase supports two logical decoding output plugins:

  • pgoutput: the built-in Postgres logical replication plugin, and the default. Use it for standard Postgres-to-Postgres replication.
  • wal2json: outputs WAL changes as JSON, for systems that consume change data in JSON.

Publisher settings

On Lakebase, max_wal_senders and max_replication_slots are both set to 10:

max_wal_senders = 10
max_replication_slots = 10

max_wal_senders caps the concurrent WAL sender processes that stream changes to subscribers, and max_replication_slots caps the replication slots that track subscriber progress. Plan for one of each per subscriber. To request different values, contact Databricks.

Create a dedicated replication slot

Some data services require a replication slot that you create in advance. Create one with the standard Postgres function, naming the decoder plugin:

SELECT pg_catalog.pg_create_logical_replication_slot('my_replication_slot', 'pgoutput');

Remove a subscription or publication

Drop a subscription on the subscriber when you no longer need it. When the publisher is reachable, dropping the subscription disconnects from the publisher and removes the replication slot on the publisher. Replicated data remains in the subscriber database.

DROP SUBSCRIPTION my_subscription;

If the publisher is unreachable, DROP SUBSCRIPTION does not complete because it cannot connect to remove the remote slot. Disassociate the slot first, then drop the subscription:

ALTER SUBSCRIPTION my_subscription DISABLE;
ALTER SUBSCRIPTION my_subscription SET (slot_name = NONE);
DROP SUBSCRIPTION my_subscription;

In this case the replication slot remains on the publisher and continues to retain WAL. Remove it on the publisher to release the WAL:

SELECT pg_catalog.pg_drop_replication_slot('<slot_name>');

Drop a publication on the publisher:

DROP PUBLICATION my_publication;

Troubleshooting

Symptom Cause and resolution
The subscription does not connect. Confirm the publisher is reachable and that the connection string, role, and password are correct. When Lakebase is the subscriber, confirm the publisher endpoint is on the account network policy allowlist. See Replicate data into Lakebase.
CREATE SUBSCRIPTION fails with restricted by Account Network Policies. Your account enforces a network policy and the publisher host is not in the allowed internet destinations. Add the host to the account network policy. See Replicate data into Lakebase.
Storage grows and does not release. A replication slot is retaining WAL because its subscriber is not consuming changes. If left unresolved, the publisher can run out of storage. Check retained WAL, then reconnect the subscriber or drop the slot. Storage is released only after the publisher recycles WAL. See Replication slots and storage.
Rows do not arrive on the subscriber. Confirm the target tables exist with matching names and columns, and that the publication includes the tables you expect.

Next steps

  • Last updated on