Replicate data from Lakebase

This guide shows you how to publish changes from a Lakebase database to an external Postgres subscriber using logical replication. Lakebase acts as the publisher: it defines a publication, and the external subscriber connects in to receive the changes.

Prerequisites

  • A Lakebase project with the database you want to publish from, containing the tables you want to publish.
  • A Lakebase Postgres role that is a member of the databricks_superuser role, to create the publication. This is the role you connect as, not the role the subscriber uses. You create the subscriber's replication role in the first step.
  • Password connections enabled on the project. The subscriber connects with a native Postgres password role, and password connections are disabled by default for new projects. See Manage password connections.
  • An external Postgres database to act as the subscriber.

Logical replication is enabled for your workspace by Databricks. Lakebase runs at wal_level = logical by default, so there is no per-project step to turn on publishing.

Create a replication role

Create a dedicated role for the external subscriber to connect as. The subscriber connects with a Postgres username and password, so create a native Postgres password role, not an OAuth role. OAuth roles authenticate with short-lived Azure Databricks tokens, which the password-based subscription connection string cannot use. The role must have the REPLICATION attribute. Roles you create in the Lakebase App, CLI, or API have this attribute, whether or not you grant them databricks_superuser. In the Lakebase App Add role > Password tab, enter a role name. You do not need to select databricks_superuser. See Create a native Postgres password role.

To set the password yourself, create the role with SQL instead. Include the REPLICATION attribute, because roles created with SQL do not receive it automatically:

CREATE ROLE replication_user WITH LOGIN REPLICATION PASSWORD '<password>';

If the role does not own the tables you replicate, grant it read access:

GRANT USAGE ON SCHEMA public TO replication_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replication_user;

Create a publication

Connect to the Lakebase database as a member of the databricks_superuser role, then create a publication for the tables you want to replicate. Each table must have a PRIMARY KEY or a replica identity set. See Replica identity in the Postgres documentation.

CREATE PUBLICATION my_publication FOR TABLE orders, customers;

Allow the subscriber to connect

The external subscriber connects in to Lakebase through the Azure Databricks edge proxy, which enforces the workspace IP access list. Allow the subscriber's IP address in the workspace IP access list so it can reach your Lakebase database. See Manage the IP access list.

Private Link is not supported for the subscriber connection. The subscriber connects over the public Lakebase endpoint.

Subscribe from the external database

On the external Postgres database, create the target tables with the same names and columns as the tables you publish. Create them empty: the initial copy fails on duplicate keys or constraint violations if a target table already contains rows. You can create the tables manually, or dump the schema from Lakebase and load it into the subscriber. See Dump the schema only.

Then, as a role that can create subscriptions on the external database, create a subscription that connects to Lakebase:

CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=<lakebase-host> port=5432 dbname=<lakebase-db> user=replication_user password=<password> sslmode=verify-full sslrootcert=system'
PUBLICATION my_publication;

Use a direct connection for the subscriber. Logical replication requires a persistent connection, so <lakebase-host> must be your direct endpoint hostname, not the -pooler connection pooler hostname. See Connection pooling.

Note

Lakebase enforces TLS on all connections, so traffic is always encrypted with no action needed. For the strongest protection, connect with sslmode=verify-full, which also verifies the Lakebase server certificate against your system's root store. Lakebase uses a publicly trusted certificate, so no extra certificate is needed. The weaker sslmode=require encrypts but does not verify the server.

Verify replication

On the subscriber, confirm rows arrive and check lag:

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

To confirm the initial copy has finished, run the per-table sync status query in Monitor replication on the subscriber and wait until every table reads Ready (fully live).

The subscriber's connection maintains a replication slot on the Lakebase publisher. While a subscriber is actively replicating, the Lakebase compute does not scale to zero. See Logical replication and scale to zero. If the subscriber stops consuming changes, the slot becomes inactive and Lakebase removes it after about 40 hours. See Automatic removal of inactive slots.

Next steps

  • อัปเดตล่าสุดเมื่อ