Postgres Migrations

This is a simple system for performing database schema migrations in SuperStack when self-hosting PostgreSQL.

It includes:

• A migrations directory for your SQL scripts.
• A migrate script to perform migrations and keep track of completed ones.
• A startup.sh which runs migrations it at first startup.

1. Create directory for SQL scripts

mkdir -p db/postgres/migrations

2. Create a migrate script

mkdir -p db/postgres/bin

db/postgres/bin/migrate

#!/bin/bash
set -euo pipefail

MIGRATIONS_DIR=/etc/superstack/migrations
APPLIED_MIGRATIONS_FILE="/var/lib/postgresql/data/.applied_migrations"

touch "$APPLIED_MIGRATIONS_FILE"

# Get sorted lists of all and applied migration filenames (no paths)
all_files=$(printf '%s\n' "$MIGRATIONS_DIR"/*.sql | xargs -n1 basename | sort)
applied_files=$(sort "$APPLIED_MIGRATIONS_FILE")
pending_files=$(comm -23 <(echo "$all_files") <(echo "$applied_files"))

# Apply pending migrations
if [[ -n "$pending_files" ]]; then
  while IFS= read -r filename; do
    echo "-- $filename" >&2
    envsubst < "$MIGRATIONS_DIR/$filename" | \
      psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB"
    echo "$filename" >> "$APPLIED_MIGRATIONS_FILE"
  done <<< "$pending_files"
else
  echo "Nothing to deploy" >&2
fi

3. Run migrations at startup

Add the following script to run migrations at startup:

mkdir -p db/postgres/docker-entrypoint-initdb.d

db/postgres/docker-entrypoint-initdb.d/startup.sh

#!/bin/bash

exec migrate

Add a Dockerfile to customise your Postgres image:

db/postgres/Dockerfile

FROM postgres:17

COPY docker-entrypoint-initdb.d /docker-entrypoint-initdb.d
COPY migrations /etc/superstack/migrations
COPY bin /postgres-bin
ENV PATH="/postgres-bin:$PATH"

# gettext is needed for envsubst
RUN apt-get update && apt-get install -y \
  gettext

# Lastly, set the WORKDIR - This is the default anyway but it may be changed
# above by the user when installing extensions
WORKDIR /var/lib/postgresql

For development, mount some directories into the Postgres service:

db/compose.override.yaml

services:
  postgres:
    volumes:
      - ./postgres/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d:ro
      - ./postgres/migrations:/etc/superstack/migrations:rw
      - ./postgres/bin:/postgres-bin:ro

Lastly, remove the image: postgres from your Postgres service and replace it with a build: section:

services:
  postgres:
    build:
      context: ./postgres

That's it!

✍️ Writing Migrations

Each file should be:

• An .sql file
• Numbered in order (e.g. 00-init.sql, 01-extensions.sql, 02-auth.sql)
• Written in plain SQL
• But can include environment variables.

▶️ Applying Migrations

When the Postgres container starts with no existing data, SuperStack will automatically run migrations once.

After the first startup, migrations will only run if you manually apply them.

To apply your migrations, run:

bin/postgres migrate

This command will:

1. Apply new migrations, in filename order.
2. Record applied migrations in a file named .applied_migrations.

Already-applied scripts are skipped on subsequent runs.

💡 bin/postgres is a small script that effectively aliases docker compose exec postgres

Here's an example migration script:

begin;

create table director (
  id serial primary key,
  name text not null
);

create table movie (
  id serial primary key,
  name text not null,
  director_id integer references director(id)
);

commit;

🔁 Transactions

Use begin; and commit; to wrap statements in a transaction. This ensures that all changes are applied atomically. Any statements outside of transactions will be auto-committed.

Avoid wrapping non-transactional operations in a transaction — these will cause errors if used inside begin ... commit. Examples of non-transactional statements include:

ALTER SYSTEM
CREATE DATABASE
CREATE EXTENSION
CREATE ROLE
CREATE TABLESPACE
DROP DATABASE
DROP EXTENSION
DROP TABLESPACE

Environment Variables

Env vars can be used in migrations:

\set pgrst_authenticator_pass '$PGRST_AUTHENTICATOR_PASS'

Reduce the chance of environment variables being logged by putting them into variables with \set, then use the value later:

create role authenticator noinherit login password :'pgrst_authenticator_pass';

Suggested File Layout

SuperStack doesn’t enforce any particular migration file names or layout, but here’s a simple structure you might adopt during development (before production):

01-extensions.sql
02-auth_schema.sql  (if using PostgREST for auth)
03-api_schema.sql
04-roles.sql
05-grants.sql

While developing, you can reset and rebuild the database from scratch as often as needed:

docker compose down --volumes
docker compose up -d

Once you’ve deployed to production (or another persistent environment), avoid recreating the database. Instead:

• Add new migrations starting from 06-... onwards.
• Apply them with:

bin/postgres migrate

Or in other environments where bin/postgres isn't available:

docker compose exec postgres migrate

This approach keeps early development simple while providing a clear, ordered history once the database must be preserved.

🔄 Nuke Everything

If you want to start fresh, wipe your database and re-run all migrations from scratch:

docker compose down --volumes
docker compose up -d