Schema Migration with psql and Version Control

Intermediate12 min

Manage database schema changes safely using numbered migration files, psql, and version control for reproducible deployments.

Prerequisites

-PostgreSQL database access
-psql available
-Git repository for migration files

Steps

Create a migrations tracking table

Set up a table to track which migrations have been applied.

$ psql -U postgres -d mydb -c "CREATE TABLE IF NOT EXISTS schema_migrations (version VARCHAR(14) PRIMARY KEY, applied_at TIMESTAMPTZ DEFAULT NOW(), description TEXT);"

Create a numbered migration file

Write a SQL migration file with a timestamp-based version number.

$ cat > migrations/$(date +%Y%m%d%H%M%S)_create_users.sql << 'SQL' -- Up migration CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(100) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_users_email ON users (email); SQL

Always use timestamps (YYYYMMDDHHMMSS) as version prefixes to avoid ordering conflicts between team members.

Apply a migration within a transaction

Execute the migration inside a transaction so it rolls back entirely on failure.

$ psql -U postgres -d mydb -v ON_ERROR_STOP=1 --single-transaction -f migrations/20260311120000_create_users.sql

Some DDL statements (like CREATE INDEX CONCURRENTLY) cannot run inside a transaction. Handle those separately.

Record the migration as applied

Insert a row into the tracking table after a successful migration.

$ psql -U postgres -d mydb -c "INSERT INTO schema_migrations (version, description) VALUES ('20260311120000', 'create_users');"

Check migration status

List all applied migrations and compare with migration files on disk.

$ psql -U postgres -d mydb -c "SELECT version, description, applied_at FROM schema_migrations ORDER BY version;"

Full Script

full-script.sh

#!/bin/bash
# PostgreSQL Migration Runner
set -euo pipefail

DB="mydb"
USER="postgres"
MIGRATION_DIR="./migrations"

echo "=== Ensuring migrations table exists ==="
psql -U "$USER" -d "$DB" -c "
CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(14) PRIMARY KEY,
  applied_at TIMESTAMPTZ DEFAULT NOW(),
  description TEXT
);
"

echo ""
echo "=== Checking pending migrations ==="
for FILE in "$MIGRATION_DIR"/*.sql; do
  [ -f "$FILE" ] || continue
  VERSION=$(basename "$FILE" | cut -d_ -f1)
  DESCRIPTION=$(basename "$FILE" .sql | sed 's/^[0-9]*_//')

  APPLIED=$(psql -U "$USER" -d "$DB" -tAc "SELECT COUNT(*) FROM schema_migrations WHERE version = '$VERSION';")

  if [ "$APPLIED" = "0" ]; then
    echo "Applying: $FILE"
    psql -U "$USER" -d "$DB" -v ON_ERROR_STOP=1 --single-transaction -f "$FILE"
    psql -U "$USER" -d "$DB" -c "INSERT INTO schema_migrations (version, description) VALUES ('$VERSION', '$DESCRIPTION');"
    echo "Applied: $VERSION ($DESCRIPTION)"
  else
    echo "Skipping (already applied): $VERSION"
  fi
done

echo ""
echo "=== Migration history ==="
psql -U "$USER" -d "$DB" -c "SELECT version, description, applied_at FROM schema_migrations ORDER BY version;"

FAQ

Discussion

Loading comments...

#!/bin/bash # PostgreSQL Migration Runner set -euo pipefail DB="mydb" USER="postgres" MIGRATION_DIR="./migrations" echo "=== Ensuring migrations table exists ===" psql -U "$USER" -d "$DB" -c " CREATE TABLE IF NOT EXISTS schema_migrations ( version VARCHAR(14) PRIMARY KEY, applied_at TIMESTAMPTZ DEFAULT NOW(), description TEXT ); " echo "" echo "=== Checking pending migrations ===" for FILE in "$MIGRATION_DIR"/*.sql; do [ -f "$FILE" ] || continue VERSION=$(basename "$FILE" | cut -d_ -f1) DESCRIPTION=$(basename "$FILE" .sql | sed 's/^[0-9]*_//') APPLIED=$(psql -U "$USER" -d "$DB" -tAc "SELECT COUNT(*) FROM schema_migrations WHERE version = '$VERSION';") if [ "$APPLIED" = "0" ]; then echo "Applying: $FILE" psql -U "$USER" -d "$DB" -v ON_ERROR_STOP=1 --single-transaction -f "$FILE" psql -U "$USER" -d "$DB" -c "INSERT INTO schema_migrations (version, description) VALUES ('$VERSION', '$DESCRIPTION');" echo "Applied: $VERSION ($DESCRIPTION)" else echo "Skipping (already applied): $VERSION" fi done echo "" echo "=== Migration history ===" psql -U "$USER" -d "$DB" -c "SELECT version, description, applied_at FROM schema_migrations ORDER BY version;"