Replicate data into Lakebase

This guide shows you how to replicate data from an external Postgres database into Lakebase using logical replication. Lakebase acts as the subscriber: it connects out to your source database, copies the existing data, and applies ongoing changes until you cut over. Use this to migrate a database from another Postgres provider, such as Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL, or from a self-managed Postgres server into Lakebase.

Prerequisites

  • A source Postgres database with a public endpoint that Lakebase can reach.
  • Superuser access on the source database, or the admin account on a managed Postgres service. Setting wal_level, creating a role with the REPLICATION attribute, and creating a publication all require this level of access.
  • A Lakebase project for the target database.
  • A Lakebase Postgres role to create the subscription: a member of the databricks_superuser role, or a role granted the pg_create_subscription predefined role. See Create Postgres roles.

Logical replication is enabled for your workspace by Databricks. There is no per-project step to turn it on. As the subscriber, Lakebase does not require a wal_level change. Only the source publisher needs wal_level = logical.

Prepare the source database

On your source Postgres database, set the write-ahead log to logical and confirm there is capacity for replication. The following example is for self-managed Postgres. Your managed Postgres provider might use different settings:

ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_replication_slots = 10;

Restart the source database for the wal_level change to take effect. On a managed service, use the provider's parameter group or flags to set wal_level to logical. For example, on Amazon Aurora PostgreSQL, set rds.logical_replication to 1 in the DB cluster parameter group and reboot the cluster. On Amazon RDS for PostgreSQL, set the same parameter in the DB parameter group and reboot the instance.

Create a role for replication and grant it read access to the tables you replicate:

CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD '<password>';
GRANT USAGE ON SCHEMA public TO replication_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;

This CREATE ROLE statement works as written on a self-managed Postgres server. Some managed Postgres providers do not allow the REPLICATION attribute directly and instead grant replication access through a provider-specific role. If your source runs on a managed service, follow the provider's documentation to give the role replication access, then use that role in place of replication_user in the steps that follow. For example, on Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL, create the role without WITH REPLICATION and then run GRANT rds_replication TO replication_user.

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 Lakebase to reach the source

As the subscriber, Lakebase dials out to your source database. This outbound connection is governed by your account's network policy, which controls the internet destinations that serverless workloads can reach. It is not controlled by a Lakebase project setting or an in-database role.

Whether you need to make a change depends on whether your account enforces a network policy:

  • No active network policy: Lakebase can reach any publisher that has a reachable public endpoint. No change is needed.
  • An active network policy: add the source database's host to the allowed internet destinations in the account network policy. See Manage network policies. If the host is not allowed, the subscription is rejected when you create it. See Troubleshooting.

Private Link is not supported for the subscriber connection. The source database must be reachable over its public endpoint.

Create the target tables

Logical replication copies data, not schema. Create the tables in the target Lakebase database with the same names and columns as the source before you start replication. You can create them manually, or dump the schema from the source and load it into Lakebase. See Dump the schema only.

Create the target tables empty. The initial table copy fails on duplicate keys or constraint violations if a target table already contains rows.

Create the subscription

Connect to the target Lakebase database as a role that can create subscriptions (see Prerequisites), then create a subscription that connects to the source publisher:

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

Creating the subscription starts a replication slot on the source publisher, copies the existing rows, and then streams ongoing changes.

Warning

The replication slot makes the source database retain write-ahead log (WAL) until Lakebase confirms it has received the changes. If the subscription stops consuming changes, for example because it is disabled, disconnected, or failing, WAL accumulates on the source without limit. On a production source, this can fill the source's storage and cause an outage.

While replication runs, monitor how much WAL the slot retains on the source:

SELECT slot_name, active,
       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;

To cap the risk on a self-managed source, set max_slot_wal_keep_size to limit how much WAL a slot can retain. If a slot exceeds the limit, Postgres invalidates it and the subscription must be re-created. Drop the subscription as soon as you finish the migration. See Replication slots and storage.

Note

When Lakebase subscribes, it connects out to your source database and sends the replication role's credentials to it. sslmode=verify-full with sslrootcert=system encrypts the connection and verifies the source's identity, which protects the relayed credentials against a man-in-the-middle. It works when your source uses a publicly trusted certificate authority. The weaker sslmode=require encrypts but does not verify the source. Verifying a source that uses a private certificate authority is not supported.

Monitor and cut over

Track the initial copy and ongoing 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).

When the target has caught up, stop writes on the source, confirm the target is fully current, and switch your application to the Lakebase connection details.

Logical replication does not copy sequence values or identity counters. Before you resume writes on Lakebase, reset each target sequence to match the replicated data, or new inserts into SERIAL or identity columns can generate duplicate primary keys:

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('orders', 'id'), (SELECT MAX(id) FROM orders));

Once your application is running against Lakebase, drop the subscription to stop replicating from the source. When the source is reachable, dropping the subscription also removes the replication slot on it. See Remove a subscription or publication.

For monitoring and cleanup, see Manage and monitor logical replication.

Next steps

  • Last updated on