Bijan Biria
Running PostgreSQL Migrations for Multiple Schemas with a Single Script
Bash Script Database Golang Migrations PostgreSQL PostgreSQL Migrations Schema

Running PostgreSQL Migrations for Multiple Schemas with a Single Script

1. Introduction – The Problem When working with PostgreSQL in projects that require multiple schemas, managing migrations can become challenging. By default, mo

Bijan Biria
Tehran, Iran
(Updated: )
9 min read

1. Introduction – The Problem

When working with PostgreSQL in projects that require multiple schemas, managing migrations can become challenging.

By default, most migration tools work with a single schema_migrations table, which makes it harder to track changes for different schemas independently.

If you have separate schemas like:

  • public (core application data)
  • sales (Ecommerce and orders)
  • hr (employee management)
  • analytics (reporting & BI)

…you probably want to run migrations per schema, ensuring each has its own migration history table.

2. Who Needs This Script?

This script is especially useful for:

  • Microservice architectures where each service has its own schema in a shared PostgreSQL database.
  • Multi-tenant applications where each tenant’s data lives in a dedicated schema.
  • Enterprise projects with strict separation between domains (e.g., HR, Sales, Analytics).
  • DevOps engineers who need a single command to run migrations across multiple schemas in the correct order.

3. How the Script Works

The script:

  1. Reads all schema directories inside your migrations folder (db/migrations by default).
  2. Ensures that each schema has its own migrations table (e.g., schema_migrations_sales).
  3. Runs up, down, or force migrations in the right order:
    • For up: public runs first, then other schemas alphabetically.
    • For down: reverse order, with public last.
  4. Allows running for all schemas or a specific schema only.
#!/bin/bash
set -e

# ===== Configurable from ENV =====
MIGRATIONS_BASE_PATH="${MIGRATIONS_BASE_PATH:-db/migrations}" # Default path
DATABASE_URL="${DATABASE_URL:-postgresql://user:password@localhost:5432/my_database?sslmode=disable}"

ACTION="$1"    # up | down | force
SCHEMA="$2"    # optional: specific schema
VERSION="$3"   # optional: for force

# ===== Check for migrate CLI =====
if ! command -v migrate &> /dev/null; then
  echo "❌ 'migrate' CLI is not installed."
  echo "   Install: go install github.com/golang-migrate/migrate/v4/cmd/migrate@latest"
  exit 1
fi

usage() {
  echo "Usage: $0 [up|down|force] [SCHEMA] [VERSION]"
  echo "Examples:"
  echo "  $0 up                   # run all (public first) with per-schema migrations tables"
  echo "  $0 up sales"
  echo "  $0 down hr"
  echo "  $0 force analytics 202508141200"
}

# ===== Append migrations table to DB URL =====
with_migrations_table() {
  local base="$1"
  local table="$2"
  if [[ "$base" == *\?* ]]; then
    echo "${base}&x-migrations-table=${table}"
  else
    echo "${base}?x-migrations-table=${table}"
  fi
}

# ===== Run migration for a specific schema =====
run_migration_for_schema() {
  local schema_name="$1"
  local action="$2"
  local version="$3"

  local path="$MIGRATIONS_BASE_PATH/$schema_name"
  if [ ! -d "$path" ]; then
    echo "⚠️  No migration directory for schema: $schema_name"
    return
  fi

  local mig_table="schema_migrations_${schema_name}"
  local dburl=$(with_migrations_table "$DATABASE_URL" "$mig_table")

  case "$action" in
    up)
      echo "🚀 Applying migrations for schema: $schema_name"
      migrate -path="$path" -database "$dburl" up
      ;;
    down)
      echo "⏪ Rolling back last migration for schema: $schema_name"
      migrate -path="$path" -database "$dburl" down 1
      ;;
    force)
      if [ -z "$version" ]; then
        echo "❌ Version is required for 'force'"
        usage; exit 1
      fi
      echo "⚠️  Forcing version=$version for schema: $schema_name"
      migrate -path="$path" -database "$dburl" force "$version"
      ;;
    *)
      usage; exit 1;;
  esac
}

# ===== Main logic =====
if [ -z "$ACTION" ]; then usage; exit 1; fi

if [ -n "$SCHEMA" ]; then
  if [ "$ACTION" = "up" ] && [ "$SCHEMA" != "public" ]; then
    run_migration_for_schema "public" "up"
  fi
  run_migration_for_schema "$SCHEMA" "$ACTION" "$VERSION"
  exit 0
fi

ALL_SCHEMAS=()
for d in "$MIGRATIONS_BASE_PATH"/*; do
  [ -d "$d" ] || continue
  ALL_SCHEMAS+=("$(basename "$d")")
done

PUBLIC_PRESENT=false
OTHERS=()
for s in "${ALL_SCHEMAS[@]}"; do
  if [ "$s" = "public" ]; then PUBLIC_PRESENT=true; else OTHERS+=("$s"); fi
done

case "$ACTION" in
  up)
    if [ "$PUBLIC_PRESENT" = true ]; then run_migration_for_schema "public" "up"; fi
    IFS=$'\n' OTHERS_SORTED=($(sort <<<"${OTHERS[*]}")); unset IFS
    for s in "${OTHERS_SORTED[@]}"; do run_migration_for_schema "$s" "up"; done
    ;;
  down)
    IFS=$'\n' OTHERS_SORTED=($(sort -r <<<"${OTHERS[*]}")); unset IFS
    for s in "${OTHERS_SORTED[@]}"; do run_migration_for_schema "$s" "down"; done
    if [ "$PUBLIC_PRESENT" = true ]; then run_migration_for_schema "public" "down"; fi
    ;;
  force)
    echo "❌ 'force' requires SCHEMA and VERSION"
    usage; exit 1
    ;;
  *)
    usage; exit 1;;
esac

4. Environment Variables

You can customize:

export MIGRATIONS_BASE_PATH="db/migrations"
export DATABASE_URL="postgresql://user:pass@localhost:5432/mydb?sslmode=disable"

5. Example Commands

# Run all migrations (public first)
./migrate-multi.sh up

# Run only sales schema
./migrate-multi.sh up sales

# Rollback last migration for analytics
./migrate-multi.sh down analytics

# Force a specific version for hr
./migrate-multi.sh force hr 202508141200

6. Creating and Setting Up the Script

Follow these steps to create and prepare the migration script in your project:

1. Create the Script File

Inside your project root (or in a dedicated scripts folder), create a file named:

scripts/migrate-multi.sh
💡 Recommendation:

Keeping it inside a scripts folder helps keep your project organized, especially if you have other automation scripts.

2. Paste the Script Code

Open the file in your favourite editor and paste the full migration script from this blog post.

3. Make the Script Executable

By default, newly created files are not executable.

Run the following command to give execution permissions:

chmod +x scripts/migrate-multi.sh

4. Run the Script

You can now run the script directly from your terminal:

./scripts/migrate-multi.sh up

Or, for a specific schema:

./scripts/migrate-multi.sh up sales

5. (Optional) Add to PATH

If you want to run it from anywhere without typing the path, add it to your shell profile (.bashrc / .zshrc):

export PATH="$PATH:$(pwd)/scripts"

Then you can simply run:

migrate-multi.sh up

Folder Structure Example:

my_project/
│
├── db/
│   └── migrations/
│       ├── public/
│       ├── sales/
│       ├── hr/
│       └── analytics/
│
├── scripts/
│   └── migrate-multi.sh
│
└── ...

7. Best Practices & Pitfalls

  • Always run public schema first if it contains shared tables or extensions.
  • Keep migrations per schema in separate folders to avoid conflicts.
  • In CI/CD pipelines, ensure the migrate CLI is installed before running.
  • When using force, be careful: it changes the recorded version without applying migrations.

Comments

Link copied to clipboard!