Postgres pg_dump & pg_restore

This topic describes how to create a backup of your Lakebase database using the Postgres pg_dump utility and how to restore a backup using pg_restore.

When to use pg_dump & pg_restore

Use pg_dump and pg_restore for disaster recovery and business continuity requirements, or when you need to store full backups outside of Lakebase. This method provides complete database exports that can be stored independently and restored to any Postgres-compatible system.

For a migration that keeps the target current until you cut over, use logical replication instead. To compare the ways to move data in and out of Lakebase, see Move data in and out of Lakebase.

Prerequisites

Before you begin, ensure that:

  • pg_dump and pg_restore are installed. You can verify by running pg_dump -V.
  • You're using the latest versions of pg_dump and pg_restore.
  • The client version matches your Lakebase project's Postgres version.
  • A Lakebase project with a Postgres role configured

Note

We recommend using native Postgres password authentication when using pg_dump and pg_restore. Native Postgres passwords don't expire hourly like OAuth tokens, making them better suited for backup and restore operations. See Authentication overview for details about authentication methods.

Install pg_dump and pg_restore

If you don't have the pg_dump and pg_restore utilities installed locally, install them on your preferred platform.

Windows: Install PostgreSQL using the official installer from postgresql.org. The pg_dump and pg_restore utilities are installed by default and can be found in the PostgreSQL bin directory.

macOS: Install PostgreSQL using Homebrew:

brew install postgresql

Linux (Ubuntu/Debian):

sudo apt-get install postgresql-client

Docker: Run pg_dump using a Postgres Docker container:

docker run --rm postgres:17 pg_dump --version

Create a backup with pg_dump

To create a database backup locally:

  1. Navigate to the Lakebase App by clicking the app switcher in the top right corner of your workspace.

  2. Select your project.

  3. Click Connect to open the database connection modal.

  4. Select a Postgres role from the dropdown (we recommend using a native Postgres role with password authentication).

  5. Copy the connection string.

    Your connection string looks similar to this:

    postgresql://role_name:password@ep-source-abc-123.databricks.com/databricks_postgres?sslmode=require

  6. Create a backup of your Lakebase database by running the following pg_dump command:

    pg_dump -Fc -v -d "<lakebase_database_connection_string>" -f <dump_file_name>

    After adding your Lakebase database connection string and a dump file name, your command looks like this:

    pg_dump -Fc -v -d "postgresql://role_name:password@ep-source-abc-123.databricks.com/databricks_postgres?sslmode=require" -f mydatabase.bak

pg_dump command arguments

The pg_dump command includes these arguments:

  • -Fc: Sends the output to a custom-format archive suitable for input into pg_restore.
  • -v: Runs pg_dump in verbose mode, allowing you to monitor what happens during the dump operation.
  • -d: Specifies the connection string for your Lakebase database.
  • -f <dump_file_name>: The dump file name. It can be any name you choose (for example, mydumpfile.bak).

Restore a backup with pg_restore

To restore a database using the pg_restore utility from a backup file created using pg_dump:

  1. Create a new Lakebase project.

  2. Create a database with the same name as the one you backed up.

  3. Navigate to the Lakebase App by clicking the app switcher in the top right corner of your workspace.

  4. Select your project and click Connect to open the database connection modal.

  5. Select a Postgres role from the dropdown (we recommend using a native Postgres role with password authentication).

  6. Copy the connection string.

    Your connection string looks similar to this:

    postgresql://role_name:password@ep-destination-xyz-456.databricks.com/databricks_postgres?sslmode=require

  7. Restore your data to the target database in Lakebase with pg_restore:

    pg_restore -v -d "<lakebase_database_connection_string>" <dump_file_name>

    After adding your Lakebase database connection string and the dump file name, your command looks like this:

    pg_restore -v -d "postgresql://role_name:password@ep-destination-xyz-456.databricks.com/databricks_postgres?sslmode=require" mydatabase.bak

pg_restore command arguments

The pg_restore command includes these arguments:

  • -v: Runs pg_restore in verbose mode, allowing you to monitor what happens during the restore operation.
  • -d: Specifies the Lakebase database to connect to. The value is a Lakebase database connection string.
  • <dump_file_name>: The name of the dump file you created with pg_dump.

Complete example

The following example shows how data is dumped from a source database named databricks_postgres in one Lakebase project and restored to a databricks_postgres database in another Lakebase project.

Before performing this procedure:

  • A new Lakebase project was created for the destination database (which includes the default databricks_postgres database)
  • Connection strings for the source and destination databases were collected:
    • Source: postgresql://role_name:password@ep-source-abc-123.databricks.com/databricks_postgres?sslmode=require
    • Destination: postgresql://role_name:password@ep-destination-xyz-456.databricks.com/databricks_postgres?sslmode=require

Backup and restore procedure:

# Create a directory for the dump
mkdir mydump && cd mydump

# Create the backup
pg_dump -Fc -v -d "postgresql://role_name:password@ep-source-abc-123.databricks.com/databricks_postgres?sslmode=require" -f mydatabase.bak

# Verify the backup file was created
ls
mydatabase.bak

# Restore the backup to the destination database
pg_restore -v -d "postgresql://role_name:password@ep-destination-xyz-456.databricks.com/databricks_postgres?sslmode=require" mydatabase.bak

Dump the schema only

A schema-only dump copies table definitions, indexes, and constraints without any row data. Use it when you need the structure of a database in a new target but not its contents.

The most common reason is to prepare a target database for logical replication. Logical replication copies data, not schema: the tables in a publication must already exist on the subscriber with the same names and columns before replication starts, and each target table must be empty so the initial copy does not fail on duplicate keys. A schema-only dump and reload is usually faster and less error-prone than recreating the tables by hand.

  1. Dump only the schema from the source database. The --schema-only option excludes row data, --no-owner skips ownership statements for roles that might not exist on the target, and --no-privileges skips grants that might not apply on the target:

    pg_dump --schema-only --no-owner --no-privileges "<source_connection_string>" > schema_dump.sql

  2. Review the dump file and comment out any statements the target does not support, such as provider-specific parameters or extensions. Comment out a line by adding -- at the start.

    If you are replicating a large dataset, also consider removing the CREATE INDEX statements before you load the schema. Building indexes during the initial replication copy slows it down. Save the statements you remove, and add the indexes back after the initial copy completes.

  3. Load the schema into the target database with psql:

    psql "<target_connection_string>" < schema_dump.sql

    Confirm the tables were created:

    psql "<target_connection_string>" -c "\dt"
  • Sist oppdatert