Skip to main content

Postgres pg_dump & pg_restore

Beta

Lakebase Postgres (Autoscaling Beta) is the next version of Lakebase, available for evaluation only. For production workloads, use Lakebase Public Preview. See choosing between versions to understand which version is right for you.

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.

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 database 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:

Bash
brew install postgresql

Linux (Ubuntu/Debian):

Bash
sudo apt-get install postgresql-client

Docker: Run pg_dump using a Postgres Docker container:

Bash
docker run --rm postgres:15 pg_dump --version

Create a backup with pg_dump​

To create a database backup locally:

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

  2. Select your database 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. Deselect the Connection pooling option. You need a direct connection string, not a pooled one.

    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:

    Bash
    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:

    Bash
    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 apps switcher in the top right corner of your workspace.

  4. Select your database 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. Deselect the Connection pooling option. You need a direct connection string, not a pooled one.

    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:

    Bash
    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:

    Bash
    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:

Bash
# 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
Last updated on