Back up PostgreSQL

Create a consistent dump of a PostgreSQL database, verify it, and prepare it for upload to Lighthouse.

Prerequisites

• PostgreSQL client tools (pg_dump, pg_restore) — installed with the server, or:
  • macOS: brew install libpq (then add it to your PATH) or brew install postgresql
  • Debian/Ubuntu: sudo apt-get install postgresql-client
• Network access and credentials for the database (a read-capable user is enough).

1. Sign in & create an API key

You need a Lighthouse account, a workspace, and an API key before you can upload a backup.

1. Sign in to the portal and claim your free 5 GB workspace — see Web Portal & Free Workspace.

2. Create an API key scoped backup:write, backup:read, snapshots:read — see API Keys.

3. Export the credentials so the upload step can read them:

   export LH_API_KEY="lh_xxxxxxxxxxxxxxxxxxxxxxxx"
   export LH_WORKSPACE_ID="your-workspace-uuid"

Already have a key? Continue to the dump step below.

2. Create the dump

Create the local dump directory once:

mkdir -p ./db-dumps

Use the custom format (--format=custom). It is compressed, supports selective/parallel restore, and is the recommended format for pg_restore.

export PGPASSWORD='your_password'

pg_dump \
  --host=127.0.0.1 \
  --port=5432 \
  --username=postgres \
  --format=custom \
  --file=./db-dumps/app.dump \
  app_db

Flag	Why
--format=custom	Compressed, restore-friendly archive (use with pg_restore).
--file	Stable path — overwrite the same file each run for dedup-friendly uploads.
--host / --port / --username	Connection details.
app_db	The database to dump (last positional arg).

Avoid passwords on the command line

Prefer a ~/.pgpass file or the PGPASSWORD environment variable over --password so credentials don't leak into shell history or process lists.

Dump everything (all databases)

pg_dumpall --host=127.0.0.1 --username=postgres --file=./db-dumps/all.sql

pg_dumpall produces a plain SQL file and also captures roles/tablespaces. Restore it with psql, not pg_restore.

3. Verify the dump

ls -lh ./db-dumps/app.dump
pg_restore --list ./db-dumps/app.dump | head   # should print the archive's table of contents

A non-empty file and a readable TOC mean the dump is valid.

4. Restore (recovery test)

Restore into a throwaway database to prove the dump works — never test over production.

createdb app_db_restore
pg_restore \
  --host=127.0.0.1 \
  --username=postgres \
  --dbname=app_db_restore \
  --clean --if-exists \
  ./db-dumps/app.dump

5. Upload to Lighthouse

Your dump is now in ./db-dumps. After creating an authenticated SDK client with the API key from step 1, upload the directory as a snapshot:

snapshot, err := client.Backup([]string{"./db-dumps"}, &sdktypes.BackupOptions{})
if err != nil {
	log.Fatal(err)
}
log.Printf("snapshotId=%s", snapshot.SnapshotID)

To run this on a schedule, use Automated backup with scheduling.

The dump command for this database, ready to drop into the scheduled job's make_dump():

export PGPASSWORD="$PGPASSWORD"
pg_dump --host=127.0.0.1 --port=5432 --username=postgres \
  --format=custom --file=./db-dumps/app.dump app_db