postgres / dev test clusters

updated October 13, 2026

I run separate Postgres clusters for development and test, both sized for the machine. 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"

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

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 to run tests faster:

• 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 --jobs N, for planner statistics.
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.

Scrub

The restored database contains 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 (
    '[email protected]',
    '[email protected]'
  );

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.

Every worktree uses a copy of app_dev_main, named for its directory: app_dev_app_42 for the APP-42 worktree.

go run ./cmd/db newdb

The command clones the main checkout'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. The main checkout runs no server and stays idle, so I use its database as the template.

Stale data

A worktree database applies missing migrations, but keeps the data from its last restore. 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 checkout 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 (see below), which makes a cached test result valid across machines.

Schema in the test binary

Go caches a passing test result by the inputs of the test binary. A database is not one of them. So after a migration, a package whose Go source did not change reports a cached pass against the old schema.

A small package embeds the schema file:

// Package dbschema carries db/schema.sql into the Go build graph.
package dbschema

import _ "embed"

//go:embed schema.sql
var SQL string

The package of test helpers imports it. A migration rewrites db/schema.sql, which changes the build hash of each package that imports the test helpers. So go test runs each of those packages again. The embed lives in db/, beside the file, because a go:embed pattern cannot contain ...

The test binary computes the database name from the embedded copy:

func DBName() string {
	sum := md5.Sum([]byte(dbschema.SQL))
	return "app_test_" + hex.EncodeToString(sum[:])
}

Schema stamp

A test cannot trust a database name, because a database can carry any name. So the tool that builds a test database also writes the md5 of the schema on it:

COMMENT ON DATABASE app_test_<md5> IS 'app schema <md5>';

When a test binary opens its pool, it reads the comment and compares it with the md5 of its embedded schema. On a mismatch, each database test fails with one message, and the message names the rebuild command. Without the check, the same mistake shows as many failures on a column that does not exist.

The check runs only because of the embed. A package with a cached result does not run, so it never checks.

The CI test database

A shell script builds the CI database in two steps before go test starts:

hash=$(md5sum db/schema.sql | awk '{print $1}')
schema_db="app_schema_$hash"
test_db="app_test_$hash"

(
  flock -w 30 9
  if createdb "$schema_db" 2>/dev/null; then
    if ! psql -v ON_ERROR_STOP=1 --single-transaction -d "$schema_db" -f db/schema.sql; then
      dropdb --if-exists "$schema_db"
      exit 1
    fi
  fi
) 9>"/tmp/app-schema-$hash.lock"

(
  flock -w 30 9
  createdb -T "$schema_db" "$test_db" 2>/dev/null || true
  psql -v ON_ERROR_STOP=1 -d "$test_db" \
    -c "COMMENT ON DATABASE $test_db IS 'app schema $hash'"
) 9>"/tmp/app-test-$hash.lock"

createdb is the existence test, because it fails when the database exists. The schema loads in one transaction, and a failed load drops the template. A template with half the schema would otherwise survive under the right name, and every later run would copy it.

The worker slots on one CI box share one Postgres. A flock on the hash stops two slots from building the same database at once. A database from an earlier run on the same schema is safe to reuse, because each test rolls back its transaction.

The script writes the comment on every run, not only when it creates the database. So a database that an older script built also passes the check.

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.

psql connections

Homebrew builds libpq with GSSAPI, so gssencmode defaults to prefer. Every psql connection then opens with a GSSENCRequest packet that my local cluster refuses. I set PGGSSENCMODE=disable in my zshrc to skip that round trip. See the libpq parameter.

The variable reaches libpq clients only: psql, pg_dump, and the rest of the Postgres client tools. My Go programs connect through pgx, which speaks the wire protocol itself, never sends the packet, and ignores the variable.

Usage

A test binary connects to the test database for the schema compiled into it:

postgres://postgres:postgres@localhost:5433/app_test_<md5(db/schema.sql)>

The binary computes the name from that schema, so no environment variable names the database. CI runs one Postgres on the default port, 5432.

See go / postgres.

← All articles