Tutorial: Configure GitHub Actions for integration tests with PostgreSQL in ephemeral containers and automatic migration rollback — a practical guide

Deploy & DevOps · By Kevin Marcondes · Published on Oct 29, 2026 · 9 min read

This walkthrough shows how to build a workflow that starts PostgreSQL as an ephemeral service per job, applies migrations (up), runs integration tests and automatically rolls back (down) at the end — even on failures. Topic: configuring GitHub Actions for integration tests with PostgreSQL in ephemeral containers and automatic migration rollback. At the end you'll have a clean, reproducible pipeline.

1) Prerequisites and pipeline architecture

You need integration tests and migration scripts that support up and down. The workflow creates an ephemeral PostgreSQL instance per job, applies migrations, runs tests and undoes changes at the end. Never point your CI to a shared database.

Architecture: GitHub Actions runner + PostgreSQL service + migrate/rollback scripts. Set DATABASE_URL and validate the connection before the test suite to fail fast.

• Repository with a migrations directory (pairs like .up.sql and .down.sql).
• Scripts to apply and revert migrations (bash, make, or your stack's tool).
• Test runner (pytest, npm test, go test, etc.).
• Access to an Ubuntu runner on GitHub Actions and psql installed.
• DATABASE_URL configured to point at the local service.

# Variáveis de ambiente e verificação rápida
export DATABASE_URL="postgres://postgres:postgres@localhost:5432/app_test"
pg_isready -h localhost -p 5432 -U postgres
psql "$DATABASE_URL" -c "SELECT 1;"

# Exemplos de execução (substitua pelo seu runner)
# Node: npm run test:integration
# Python: pytest -m integration
# Go: go test ./... -tags=integration
./scripts/run-tests.sh

2) How the ephemeral DB and automatic rollback work

The PostgreSQL service is discarded at the end of the job, ensuring isolation between runs. Still, running a rollback (down) is useful to validate reversibility and to train the team for real rollback scenarios.

You can combine complementary strategies for isolation, rollback and transactional sandboxes depending on your stack and desired coverage.

3) Organize migrations and up/down scripts

Standardize a predictable layout and provide two scripts: migrate.sh (up) and rollback.sh (down). Scripts must fail on the first error so issues aren't hidden.

If you need more criteria and best practices on reversions, see the migrations guide in well-structured migrations and rollbacks.

# Estrutura sugerida
# migrations/
#   001_create_users.up.sql
#   001_create_users.down.sql
#   002_add_email_index.up.sql
#   002_add_email_index.down.sql
# scripts/
#   migrate.sh
#   rollback.sh

# scripts/migrate.sh
#!/usr/bin/env bash
set -euo pipefail
shopt -s nullglob
: "${DATABASE_URL:?Defina DATABASE_URL}"
for f in migrations/*.up.sql; do
  echo "[UP] Aplicando $f"
  psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -1 -f "$f"
done

echo "Migrations UP concluídas."

# scripts/rollback.sh
#!/usr/bin/env bash
set -euo pipefail
shopt -s nullglob
: "${DATABASE_URL:?Defina DATABASE_URL}"
# Reverte na ordem inversa
for f in $(ls migrations/*.down.sql | sort -r); do
  echo "[DOWN] Revertendo $f"
  psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -1 -f "$f"
done

echo "Rollback concluído."

4) Define the GitHub Actions workflow with a PostgreSQL service

Create a workflow that starts the postgres service, installs the psql client, applies migrations, runs tests and always attempts rollback at the end. Use GitHub Secrets instead of plaintext passwords.

To harden pipelines and secrets handling, see the CI/CD security checklist. If you also deploy with Actions, check the automatic deploy pipeline guide.

name: ci-integration-postgres

on:
  push:
    branches: [main]
  pull_request:

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        ports:
          - 5432:5432
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: app_test
        options: >-
          --health-cmd="pg_isready -U postgres"
          --health-interval=5s
          --health-timeout=5s
          --health-retries=20

    env:
      DATABASE_URL: postgres://postgres:postgres@localhost:5432/app_test

    steps:
      - uses: actions/checkout@v4

      - name: Instalar cliente PostgreSQL
        run: |
          sudo apt-get update
          sudo apt-get install -y postgresql-client

      - name: Aguardar Postgres e validar conexão
        run: |
          for i in {1..30}; do
            if pg_isready -h localhost -p 5432 -U postgres; then
              break
            fi
            sleep 1
          done
          psql "$DATABASE_URL" -c "SELECT version();"

      - name: Rodar migrations (UP)
        run: bash scripts/migrate.sh

      - name: Executar testes de integração
        run: |
          ./scripts/run-tests.sh

      - name: Rollback de migrations (DOWN) — sempre
        if: always()
        run: bash scripts/rollback.sh

5) How to guarantee rollback even when tests fail

Use if: always() on the rollback step. That ensures the down runs regardless of test results. Failures during rollback should fail the job to surface non-reversible migrations.

If you prefer, create a separate cleanup job with needs and if: always() to run additional actions after the suite.

• Rollback step with if: always().
• Errors in rollback should fail the pipeline to force immediate fixes.
• Optional cleanup job to run extra tasks after tests.

# Exemplo de job de limpeza opcional
jobs:
  test:
    runs-on: ubuntu-latest
    # ... passos do job de testes ...

  cleanup:
    runs-on: ubuntu-latest
    needs: [test]
    if: always()
    steps:
      - uses: actions/checkout@v4
      - name: (Opcional) operações de limpeza adicionais
        run: |
          echo "Nada a fazer: o service Postgres é efêmero."
          # Em casos especiais, notifique ou arquive artefatos.

6) Parallelism and isolated DBs per job: how to set up

To parallelize, generate a unique database name per job and export it in DATABASE_URL to avoid collisions between concurrent runs. A matrix allows testing multiple Postgres versions.

If you use self-hosted runners and need to scale concurrency, see the guide on automatic scaling of self-hosted runners.

• Database name using matrix.pg, github.run_id and github.run_attempt.
• Idempotent creation via CREATE DATABASE or createdb.
• Rollback remains valid with multiple isolated databases.

jobs:
  test:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        pg: [14, 15, 16]
    services:
      postgres:
        image: postgres:${{ matrix.pg }}
        ports: ["5432:5432"]
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        options: >-
          --health-cmd="pg_isready -U postgres"
          --health-interval=5s --health-timeout=5s --health-retries=20
    env:
      DB_NAME: app_test_${{ matrix.pg }}_${{ github.run_id }}_${{ github.run_attempt }}
      DATABASE_URL: postgres://postgres:postgres@localhost:5432/${{ env.DB_NAME }}
    steps:
      - uses: actions/checkout@v4
      - name: Instalar cliente PostgreSQL
        run: sudo apt-get update && sudo apt-get install -y postgresql-client
      - name: Criar banco isolado
        run: |
          psql "postgres://postgres:postgres@localhost:5432/postgres" \
            -v ON_ERROR_STOP=1 -c "CREATE DATABASE \"${DB_NAME}\";" || true
      - name: Rodar migrations e testes
        run: |
          bash scripts/migrate.sh
          ./scripts/run-tests.sh
      - name: Rollback (sempre)
        if: always()
        run: bash scripts/rollback.sh

7) Seeding test data in CI without leaving a mess

Seeds insert the minimal data your tests need. Apply them after migrations and, if there is no explicit rollback for seeds, rely on the ephemeral DB for cleanup.

Prefer idempotent seeds (UPSERT/ON CONFLICT) to avoid duplicates and flakiness across re-runs.

• Run seeds after migrations and before tests.
• Fixed IDs and UPSERTs prevent duplicates.
• Rollback can explicitly remove seeded records if needed.

# scripts/seed.sh (exemplo)
#!/usr/bin/env bash
set -euo pipefail
: "${DATABASE_URL:?}"
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
INSERT INTO users(id, name, email)
VALUES (1, 'Test User', '[email protected]')
ON CONFLICT (id) DO UPDATE SET name=EXCLUDED.name, email=EXCLUDED.email;
SQL

echo "Seeds aplicados."

8) Final checks and troubleshooting common issues

Confirm the schema was applied, tests used the intended database and the rollback cleaned up changes. On failures, focus on connection, authentication and migration ordering.

If the problem persists, isolate a minimal case with a single migration and a test to reduce variables and speed diagnosis.

• Connection refused: wait for service health and ensure psql is installed.
• Authentication: use secrets; use a user with adequate privileges.
• Timeouts: increase health-retries/interval; avoid heavy queries during setup.
• Locks: apply migrations in a transaction (-1) and serialize complex schema changes.
• psql not found: install postgresql-client in the job.
• No down scripts: rely on the ephemeral DB and prioritize writing rollback scripts soon.

# Checks rápidos
psql "$DATABASE_URL" -c "\\dt public.*"                          # Tabelas no schema público
psql "$DATABASE_URL" -c "SELECT count(*) FROM public.users;"       # Tabela-chave
psql "$DATABASE_URL" -c "\\dt public.users" || true               # Após rollback, deve falhar/estar vazia

Frequently asked questions

Do I need rollback if the CI DB is already ephemeral?

Yes — if you want to validate your down scripts. The ephemeral DB ensures cleanup; running rollback verifies migrations are reversible and helps avoid being stuck during a real production rollback.

Can I use docker-compose instead of services in GitHub Actions?

Yes, but GitHub Actions services are simpler for a single PostgreSQL instance. docker-compose adds flexibility when you need multiple services or custom networks in the pipeline.

How do I isolate parallel builds so they don't conflict?

Use unique database names per job (run_id, run_attempt, matrix). Create the DB before running migrations and export DATABASE_URL with the distinct name for each run.

Where do I put the Postgres password in CI?

Store it in GitHub Secrets and reference the secret in the workflow. Examples use default values for clarity, but production projects must use secrets to reduce exposure and for auditability.

What if my migrations don't have down scripts?

Rely on the ephemeral DB to ensure isolation between jobs, but prioritize writing down scripts. Without them you can't validate rollbacks, increasing risk in production.

How can I seed test data without polluting the environment?

Apply idempotent seeds after migrations and remove them in rollback if needed. With ephemeral DBs the container is discarded at the end of the job, providing automatic cleanup.

See also

• Tutorial: set up mTLS between Traefik and Docker Compose services using smallstep CA and automatic certificates — step by step
• Tutorial: integrate Sentry into your GitHub Actions deploy pipeline — automatic source map upload & issue creation (PHP & JavaScript)
• Configure retention and compression for Docker container logs with journald + logrotate + rsyslog on a low-disk VPS
• Configure BuildKit garbage collection and systemd timers to auto‑clean Docker images and volumes: a practical tutorial