postgres / dump prod restore dev

updated August 21, 2026

I frequently dump my production Postgres database and restore to my development machine using scripts stored in my project's Git repo.

The current version depends on Unix, Postgres, and Crunchy Bridge CLIs.

The db-download-prod script downloads the backup to tmp/latest_backup_dir:

#!/bin/bash
set -euo pipefail

# Delete/create target directory
backup_dir="tmp/latest_backup_dir"
rm -rf "$backup_dir"
mkdir -p "$backup_dir"

# Detect the number of CPU cores
case "$(uname -s)" in
    Linux*)     cores=$(nproc);;
    Darwin*)    cores=$(sysctl -n hw.ncpu);;
    *)          cores=1;;
esac

# Use one less than the total number of cores, but ensure at least 1 is used
(( jobs = cores - 1 ))
if (( jobs < 1 )); then
    jobs=1
fi

echo "Downloading with $jobs parallel job(s)"

# Use the directory format and specify the number of jobs for parallel dumping
pg_dump -Fd "$(cb uri app-prod --role application)" -j "$jobs" -f "$backup_dir"

The db-restore-dev script restores from backup files and post-processes:

#!/bin/bash
set -euo pipefail

db="app_dev"

dropdb --if-exists "$db"
createdb "$db"
psql "$db" -v ON_ERROR_STOP=1 <<SQL
  CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  CREATE EXTENSION IF NOT EXISTS pg_trgm;
  CREATE EXTENSION IF NOT EXISTS plpgsql;
SQL

# Same directory defined in `bin/db-download-prod`
backup_dir="tmp/latest_backup_dir"

# Detect the number of CPU cores
case "$(uname -s)" in
    Linux*)     cores=$(nproc);;
    Darwin*)    cores=$(sysctl -n hw.ncpu);;
    *)          cores=1;;
esac

# Use one less than the total number of cores, but ensure at least 1 is used
(( jobs = cores - 1 ))
if (( jobs < 1 )); then
    jobs=1
fi

echo "Restoring with $jobs parallel job(s)"

# Generate a TOC without entries this machine cannot or should not run:
# the matview refreshes, deferred to below, and pgaudit, which production
# runs and a laptop has no copy of
toc="$backup_dir/filtered.list"
pg_restore --list "$backup_dir" |
  grep -v -E 'MATERIALIZED VIEW DATA|pgaudit' > "$toc"

# Restore from directory using filtered TOC
pg_restore -d "$db" --verbose --no-acl --no-owner -j "$jobs" -L "$toc" "$backup_dir"
# A dump carries no planner statistics, and the refresh below is a large
# query whose plan depends on them
vacuumdb --analyze-only --jobs "$jobs" --dbname "$db"

# Rebuild the derived caches from the base tables. Run them concurrently:
# they read overlapping tables, so the second reads a warm cache, and
# each locks only its own view
psql "$db" -v ON_ERROR_STOP=1 --quiet \
  -c 'REFRESH MATERIALIZED VIEW cache_companies;' &
companies=$!
psql "$db" -v ON_ERROR_STOP=1 --quiet \
  -c 'REFRESH MATERIALIZED VIEW cache_people;' &
people=$!
wait "$companies"
wait "$people"

# Post-process
psql "$db" <<SQL
  -- Avoid re-running incomplete jobs
  DELETE FROM jobs
  WHERE status IN ('pending', 'started');

  -- Avoid emailing production users
  UPDATE users
  SET active = false;

  -- Turn on flags for developers
  UPDATE
    users
  SET
    active = true,
    admin = true
  WHERE
    email IN (
      '[email protected]',
      '[email protected]'
    );
SQL

I separate the scripts so I can restore a recent backup without re-downloading. See postgres / dev test clusters for the local Postgres cluster setup these scripts restore into.

Neither script runs migrations. The restored database holds the schema production had when the dump was taken, and each per-worktree copy applies what has merged since. That count is how I tell when a restore is due.

Materialized view data

The MATERIALIZED VIEW DATA label describes a refresh statement rather than rows in the dump. pg_dump writes REFRESH MATERIALIZED VIEW in place of the contents, so every restore recomputes each view from the base tables.

pg_restore --list prints a table of contents (TOC) of the backup. Passing a filtered copy back with -L skips the entries I removed. This gives me control over when the recompute happens. Left in, it runs inside the parallel job pool, before the database has planner statistics. Taken out, it runs after ANALYZE, two views at a time, on a database that can plan them.

Converting these caches to ordinary tables would remove the recompute. A table's rows are in the dump as COPY data and load under the parallel restore, at the cost of a larger backup.

Planner statistics

pg_restore loads rows and builds indexes but leaves every table with default estimates. Autovacuum analyzes them over the minutes that follow, which is the same window the refresh runs in. The refresh therefore planned against real statistics some days and against default estimates on others, and took seconds or minutes accordingly.

vacuumdb --analyze-only --jobs "$jobs" costs a few seconds and makes the refresh time predictable.

Restore errors

pg_restore can ignore an error and then exit nonzero to report that it ignored one. Under set -e, that ends the script at the restore, so everything after it never runs.

This happened when production gained pgaudit, which the laptop does not install. Its CREATE EXTENSION and COMMENT both failed, then the script stopped before the refresh and left both caches empty. Filtering those entries out of the TOC keeps a nonzero exit reserved for an unexpected failure.

ON_ERROR_STOP=1 gives the extension setup the same behavior. Without it, psql reports a missing extension and exits zero, which allowed a stale CREATE EXTENSION statement to fail on every restore without stopping the script.

← All articles