Skip to main content
Last updated on

Quickstart: Replicate between Lakebase databases

This quickstart replicates a single table from one Lakebase database to another so you can see logical replication work end to end. It does not require an external database.

Before you begin​

  • Two Lakebase databases in different projects: a source and a target.
  • A Postgres role in each database that is a member of the databricks_superuser role, such as the project owner's role. See Create Postgres roles.
  • Password connections enabled on the source project. The target connects to the source with a native Postgres password role, and password connections are disabled by default for new projects. See Manage password connections.
note

The target subscribes across projects and connects to the source through the source workspace's IP access list. Allow the target's outbound IP address in the source workspace's IP access list before you create the subscription, or the connection is rejected. See Replicate data from Lakebase.

Publish a table on the source​

Connect to the source database and create a table with sample data:

PostgreSQL
CREATE TABLE playing_with_lakebase (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name TEXT NOT NULL, value REAL);
INSERT INTO playing_with_lakebase (name, value)
SELECT LEFT(md5(i::TEXT), 10), random() FROM generate_series(1, 10) s(i);

Create a publication that includes the table:

PostgreSQL
CREATE PUBLICATION my_publication FOR TABLE playing_with_lakebase;

Create a password role with the REPLICATION attribute for the target to connect as, and grant it read access to the table:

PostgreSQL
CREATE ROLE replication_user WITH LOGIN REPLICATION PASSWORD '<password>';
GRANT SELECT ON playing_with_lakebase TO replication_user;

Subscribe on the target​

Connect to the target database and create the same table. Logical replication copies data, not schema, so the table must exist first:

PostgreSQL
CREATE TABLE playing_with_lakebase (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name TEXT NOT NULL, value REAL);

Create a subscription that connects to the source database as replication_user. Use the source's direct endpoint hostname, not the -pooler hostname:

PostgreSQL
CREATE SUBSCRIPTION my_subscription
CONNECTION 'postgresql://replication_user:<password>@<source-host>/databricks_postgres?sslmode=verify-full&sslrootcert=system'
PUBLICATION my_publication;

The subscription copies the existing rows and then streams new changes.

sslmode=verify-full with sslrootcert=system encrypts the connection and verifies the source's certificate. Lakebase endpoints use a publicly trusted certificate authority, so no certificate download is needed.

Verify replication​

On the target, confirm the initial rows arrived:

PostgreSQL
SELECT COUNT(*) FROM public.playing_with_lakebase;

Insert another row on the source:

PostgreSQL
INSERT INTO playing_with_lakebase (name, value) VALUES ('new-row', 1.0);

Query the target again. The new row appears. Check subscription status and lag at any time:

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

Next steps​

On this page