postgres / dev test clusters

updated September 03, 2026

I run separate Postgres clusters for development and test. I size both for the machine. The test cluster disables durability to run tests faster. The development cluster holds a restore of production and a copy of it per git worktree.

Setup

I automate cluster setup in cmd / laptop:

PG_VERSION="${PG_VERSION:-18}"

brew install postgresql@$PG_VERSION

export PATH="$BREW/opt/postgresql@$PG_VERSION/bin:$PATH"
if ! command -v initdb >/dev/null || ! command -v pg_ctl >/dev/null; then
  echo "initdb and/or pg_ctl not found in PATH"
  exit 1
fi

postgres_settings_differ() {
  local data_dir="$1"
  local running token wanted wanted_count=0 running_count
  local -a tokens

  running="$(pg_ctl -D "$data_dir" status 2>/dev/null | tr -s '[:space:]' ' ')"
  if [ -z "$running" ]; then
    return 0
  fi

  wanted="$(tr -s '[:space:]' ' ' <<<"$2")"
  read -ra tokens <<<"$wanted"

  for token in "${tokens[@]}"; do
    case "$token" in
    -c | -p | [0-9]*) continue ;;
    esac

    wanted_count=$((wanted_count + 1))

    if [[ "$running" != *"\"$token\""* ]]; then
      return 0
    fi
  done

  running_count="$(awk -F'"-c"' '{ print NF - 1 }' <<<"$running")"
  [ "$wanted_count" -ne "$running_count" ]
}

start_postgres_cluster() {
  local port="$1"
  local data_dir="$2"
  local log_file="$3"
  local opts="$4"

  mkdir -p "$(dirname "$data_dir")"
  mkdir -p "$(dirname "$log_file")"

  if [ ! -f "$data_dir/PG_VERSION" ]; then
    initdb -D "$data_dir" -U postgres

    echo "timezone = 'UTC'" >>"$data_dir/postgresql.conf"
    echo "log_timezone = 'UTC'" >>"$data_dir/postgresql.conf"
  fi

  if pg_ctl -D "$data_dir" status >/dev/null 2>&1; then
    if ! postgres_settings_differ "$data_dir" "-p $port $opts"; then
      echo "Postgres is already running with these settings for $data_dir"
      return
    fi

    echo "Restarting Postgres with changed settings for $data_dir"
    pg_ctl -D "$data_dir" stop
  elif lsof -i "tcp:$port" >/dev/null 2>&1; then
    echo "Postgres port $port is already in use"
    return
  fi

  pg_ctl -D "$data_dir" -l "$log_file" -o "-p $port $opts" start
}

pg_tuning="-c shared_buffers=8GB \
  -c effective_cache_size=96GB \
  -c work_mem=64MB \
  -c maintenance_work_mem=2GB \
  -c random_page_cost=1.1 \
  -c max_wal_size=16GB"

pg_durability="-c fsync=off -c synchronous_commit=off -c full_page_writes=off"

# dev databases
start_postgres_cluster 5432 \
  "$HOME/.local/share/postgres/data_dev" \
  "$HOME/.local/share/postgres/log_dev.log" \
  "$pg_tuning"

# test databases
start_postgres_cluster 5433 \
  "$HOME/.local/share/postgres/data_test" \
  "$HOME/.local/share/postgres/log_test.log" \
  "$pg_tuning $pg_durability"

The clusters start when I run the laptop script. I default to PostgreSQL 18. PG_VERSION=17 overrides it.

initdb takes the timezone from the machine, so the script appends timezone = 'UTC' and log_timezone = 'UTC' to a new postgresql.conf. A timestamp then reads the same on the laptop as in production.

Settings for the machine

Postgres defaults target generic hardware. On a machine with an SSD and ample RAM, two defaults misinform the planner:

• effective_cache_size=4GB: assumes little data fits in memory.
• random_page_cost=4: prices random reads like spinning disk seeks.

The new values favor index scans over sequential scans. shared_buffers=8GB caches the working database in memory. I pass the settings to pg_ctl so the setup script holds them. I omit effective_io_concurrency because macOS lacks posix_fadvise.

Changing a setting

The script checks running settings before it restarts a cluster. pg_ctl status prints the active command line:

/opt/homebrew/Cellar/postgresql@18/18.6/bin/postgres "-D" "..." "-p" "5432" "-c" "shared_buffers=8GB" ...

The script searches for each name=value token in that output. It restarts Postgres only when the running settings differ.

Test speed

The test cluster disables durability:

• fsync=off: no forced disk synchronization
• synchronous_commit=off: transactions commit before disk write
• full_page_writes=off: lower write volume

Each test run creates its own database, so corruption on crash costs nothing. The development cluster keeps the default durability settings.

Restoring production

I dump my production database and restore it to the development cluster. Two subcommands of one Go program in the project's repository do it:

go run ./cmd/db download
go run ./cmd/db restore

They depend on the Postgres client tools and the Crunchy Bridge CLI. I separate the two so I can restore a recent backup without a second download.

download runs pg_dump in directory format, with one job per core but one, into tmp/latest_backup_dir under the main checkout:

uri, err := exec.CommandContext(ctx, "cb", "uri", "app-prod", "--role", "application").Output()
if err != nil {
	return fmt.Errorf("cb uri app-prod: %w", err)
}
jobs := max(runtime.NumCPU()-1, 1)
return run("pg_dump", "-Fd", strings.TrimSpace(string(uri)),
	"-j", fmt.Sprint(jobs), "-f", dump)

A worktree's tmp/ goes when the worktree does, and a dump takes long enough to fetch that it should outlive the branch. Both commands find the main checkout through git worktree list, which lists it first.

restore rebuilds the main checkout's database, app_dev_main, which is the template every worktree copies (below). In order:

1. Refuse while anything is connected to it, naming what is.
2. dropdb, createdb, and the extensions production uses.
3. pg_restore in parallel, from a filtered table of contents.
4. vacuumdb --analyze-only.
5. REFRESH MATERIALIZED VIEW, two views concurrently.
6. Scrub the data.
7. Stamp the database with the time.
8. Migrate it to main's schema.
9. Name each worktree whose copy is now a restore behind.

Materialized view data

pg_dump writes REFRESH MATERIALIZED VIEW in place of a view's rows, so every restore recomputes each view from the base tables.

pg_restore --list prints the dump's table of contents. The restore writes a copy without two kinds of entry and passes it back with -L:

for line := range strings.SplitSeq(buf.String(), "\n") {
	if strings.Contains(line, "MATERIALIZED VIEW DATA") || strings.Contains(line, "pgaudit") {
		continue
	}
	kept.WriteString(line)
	kept.WriteString("\n")
}

Removing the view entries delays each refresh until after ANALYZE. Removing pgaudit prevents restore errors on a laptop.

Planner statistics

vacuumdb --analyze-only --jobs N populates table statistics after the restore.

Scrub

The restored database carries production background jobs and users. db/sanitize.sql scrubs sensitive data before the stamp:

-- 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 (
    'dev1@example.com',
    'dev2@example.com'
  );

The scrub runs once on the template database, so every copy inherits clean data.

Migrate the template

The restore applies the migrations merged into main since the dump. A worktree copy then applies only its branch's migrations.

Why Go and not bash

The Go CLI calls the migration and restore routines in-process, with shared constants and error handling.

A database per worktree

I work in several git worktrees at once. If they shared one development database, no two of them could hold a schema change.

The main checkout's database, app_dev_main, is the template. The restore above fills it and migrates it to main's schema. Every worktree uses a copy, named for its directory: app_dev_app_42 for the APP-42 worktree.

go run ./cmd/db newdb

The command clones main's database, writes the two files the checkout needs, applies pending migrations, and builds the test database. It is idempotent.

The clone uses CREATE DATABASE ... STRATEGY = FILE_COPY, which copies a 5GB database in about five seconds. FILE_COPY requires that no session connect to the template during the copy.

Why the template is the main checkout's database

The main checkout runs no server and stays idle. I restore and migrate its database once, and each new worktree copies it.

Stale data

A worktree database applies missing migrations, but keeps the snapshot data. The restore records a timestamp as a comment on app_dev_main:

stamp := "restored " + time.Now().UTC().Format("2006-01-02T15:04:05Z")
if err := stampDatabase(ctx, devDBPort, mainDB, stamp); err != nil {
	return err
}

newdb copies the stamp to each worktree database. To list databases with old data:

go run ./cmd/db stale

The two generated files

.env.local holds what belongs to this checkout:

DATABASE_URL="postgres://postgres@localhost:5432/app_dev_app_42"
PORT=3001

The environment reads .env.local before the shared .env, so these two values win. Each checkout runs its own web server. The main clone keeps port 3000 and a worktree takes the lowest free port above it.

.db holds the database name, which Vim uses to run a SQL file. Git does not track either file, and deleting the worktree removes both.

Test database names

A test database on the 5433 cluster is named for a hash of the schema file: app_test_<md5(db/schema.sql)>. Two worktrees on the same schema share one database. CI names the database the same way from the same file, which makes a cached test result valid across machines.

Pruning

prune reads DATABASE_URL from each checkout's .env.local and drops the worktree databases that no checkout names:

go run ./cmd/db prune

deletetree and newdb run it. It never drops the template.

Usage

Test suites use the test cluster:

postgres://postgres@localhost:5433/app_test

See go / postgres and ruby / test framework.

← All articles