postgres / dev test clusters
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" ]
}
postgres_program_differs() {
local running
running="$(pg_ctl -D "$1" status 2>/dev/null | sed -n '2s/ .*//p')"
[ -n "$running" ] && [ "$running" != "$(pg_config --bindir)/postgres" ]
}
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_program_differs "$data_dir"; then
echo "Restarting Postgres on the upgraded program for $data_dir"
elif postgres_settings_differ "$data_dir" "-p $port $opts"; then
echo "Restarting Postgres with changed settings for $data_dir"
else
echo "Postgres is already running with these settings for $data_dir"
return
fi
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"
pg_clone="-c file_copy_method=clone"
pg_test_connections="-c max_connections=300"
# dev databases
start_postgres_cluster 5432 \
"$HOME/.local/share/postgres/data_dev" \
"$HOME/.local/share/postgres/log_dev.log" \
"$pg_tuning $pg_clone"
# test databases
start_postgres_cluster 5433 \
"$HOME/.local/share/postgres/data_test" \
"$HOME/.local/share/postgres/log_test.log" \
"$pg_tuning $pg_durability $pg_clone $pg_test_connections"
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 when the running settings differ.
Upgrading Postgres
A brew upgrade of postgresql@18 replaces the server program and its
libraries. A cluster that is already up keeps the old program. The old
server then loads the new plpgsql.dylib and fails, so a restore that
creates a plpgsql function stops with "Symbol not found".
The program path is the first word of the command line that
pg_ctl status prints. The script compares it with postgres in
pg_config --bindir. Homebrew deletes the old Cellar directory on an
upgrade, so the two paths differ until the cluster restarts. The script
then restarts the cluster, and the restart closes its open sessions.
Test speed
The test cluster disables durability to run tests faster:
fsync=off: no forced disk synchronizationsynchronous_commit=off: transactions commit before disk writefull_page_writes=off: lower write volume
The test databases contain only the schema and test fixtures, so I can rebuild them after a crash. Runs on the same schema reuse one database. Most tests use a transaction that rolls back at cleanup (see go / test). The development cluster keeps the default durability settings.
The test cluster also sets max_connections=300. go test ./... runs
one test binary per CPU. An app that sizes each binary's pool to
GOMAXPROCS can open up to 256 connections on 16 CPUs, which exceeds
the initdb default of 100 and fails tests before they run. The
development cluster serves few connections and keeps the default.
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.
cb uri prints a URI with no TLS parameters.
With that URI, libpq uses sslmode=prefer.
It checks no server certificate, and it can fall back to plaintext.
So download sets the TLS parameters on the URI before it calls pg_dump:
out, err := exec.CommandContext(ctx, "cb", "uri", "app-prod", "--role", "application").Output()
if err != nil {
return fmt.Errorf("cb uri app-prod: %w", err)
}
u, err := url.Parse(strings.TrimSpace(string(out)))
if err != nil {
return errors.New("cannot parse the cb uri") // the uri holds the password
}
q := u.Query()
q.Set("sslmode", "verify-full")
q.Set("sslrootcert", filepath.Join(root, "cmd/db/crunchy-bridge-team.pem"))
q.Set("channel_binding", "require")
u.RawQuery = q.Encode()
These values replace any TLS value in the cb output.
A parameter in the URI also wins over PGSSLMODE and PGCHANNELBINDING.
The Crunchy Bridge team root certificate is public,
so the repository holds a copy.
libpq refuses to connect when that file is missing.
postgres / connection explains each parameter.
pg_dump writes into tmp/latest_backup_dir.next under the main checkout:
password, _ := u.User.Password()
if u.User != nil {
u.User = url.User(u.User.Username())
}
next := dump + ".next"
jobs := max(runtime.NumCPU()-1, 1)
if err := runWithEnv([]string{"PGPASSWORD=" + password}, "pg_dump", dumpArgs(u.String(), jobs, next)...); err != nil {
os.RemoveAll(next)
return err
}
if err := os.RemoveAll(dump); err != nil {
return err
}
return os.Rename(next, dump)
The rename replaces the last dump only after pg_dump succeeds.
A refused certificate or a dropped connection keeps the last dump.
A worktree's tmp/ goes when the worktree does.
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:
- Refuse while anything is connected to it, naming what is.
dropdb,createdb, and the extensions production uses.pg_restorein parallel, from a filtered table of contents.vacuumdb --analyze --jobs N, for planner statistics and the visibility map.- Stamp the database with the time.
- Migrate it to
main's schema. - Fill the derived tables (below), all five at the same time.
- Name each worktree whose copy is now a restore behind.
pg_restore --list prints the dump's table of contents.
The restore writes a copy without the pgaudit entries
and passes it back with -L.
The extension is not on a laptop, so those entries fail.
Derived tables
Five tables hold cache and search rows
that a background job rebuilds from source views every 15 minutes.
Their rows are about a third of the dump,
and the restore can build them again from the tables the dump holds.
So dumpArgs keeps their schema and indexes but leaves out their rows:
func dumpArgs(uri string, jobs int, dir string) []string {
args := []string{"-Fd", uri, "-j", fmt.Sprint(jobs), "-f", dir}
for _, d := range derivedTables {
args = append(args, "--exclude-table-data=public."+d.name)
}
return append(args, "--exclude-table-data=public.cache_refresh_signals")
}
cache_refresh_signals holds the source fingerprint of each table's last refresh.
With production's fingerprints,
the first refresh reads its source as unchanged
and leaves its empty table empty.
So the dump leaves out those rows too.
The download is smaller and faster, and the production primary copies fewer rows.
Fill the derived tables
After the migration, the restore calls the refresh function
that each table's background job calls.
Each table then fills from main's source view.
A refresh inserts one row at a time into every index,
and each table has many GIN indexes.
So, in one transaction per table,
the fill drops the indexes, refreshes,
builds each index again from the full table,
and runs ANALYZE:
err := db.Transaction(ctx, func(tx *pgdb.DB) error {
// read the table's indexes from pg_indexes, then DROP INDEX each
if status, err = d.refresh(ctx, tx); err != nil {
return err
}
for _, idx := range indexes {
if err := tx.Exec(ctx, idx.Def); err != nil {
return err
}
}
return tx.Exec(ctx, "ANALYZE "+pgx.Identifier{"public", d.name}.Sanitize())
})
pg_restore does the same: it loads the rows, then builds the indexes.
The vacuumdb step saw the tables empty,
and a filter page that reads an empty estimate picks a bad plan.
So the fill analyzes each table again.
No source view reads another of the five tables, and each refresh takes an advisory lock keyed on its own table. So the restore opens a pool with one connection for each table and runs the five fills at the same time. The whole fill takes about as long as the slowest table, about 30 seconds on my laptop.
Scrub
The restored database contains production background jobs and users.
The restore does not scrub it.
A checkout that only serves web pages needs no scrub.
A checkout that runs the job workers runs
go run ./cmd/db sanitize on its own copy first.
It runs db/sanitize.sql, which starts:
-- Avoid re-running incomplete jobs
DELETE FROM jobs
WHERE status IN ('pending', 'started');
The rest of the file stops mail and third-party calls
for every user except the developers.
sanitize records the scrub on the database.
Outside production, the workers refuse to start
until they find that record.
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. The fill calls the same refresh functions as the background job, so the restore cannot drift from what the job builds.
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.
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.
Clone, not copy
By default, FILE_COPY copies the template's files byte for byte.
It reads and writes every block,
so its time and its disk use grow with the template.
On my 5 GB template, that copy takes 5.6 seconds and 5 GB of disk.
Both clusters start with file_copy_method=clone, a Postgres 18 setting.
Postgres then asks the file system for a copy-on-write clone of each file
(clonefile on APFS).
The file system writes new metadata that points at the template's blocks,
and it copies no data.
A block gets its own copy only when the template or the clone writes it.
The same template clones in 0.4 seconds and takes no extra disk at first.
A worktree changes a small part of its data,
so most of its blocks stay shared with the template.
The setting needs a file system that can clone a file: APFS on macOS, or XFS with reflink, btrfs, or ZFS on Linux.
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 bashrc 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.