GitHub Actions for Postgres Deploys — a Throwaway Database as Quality Gate

Published Jul 7, 2026
by

The bug was a typo in an ALTER TABLE — and it was found by the staging deploy on a Friday afternoon. Yet that exact find is automatable: set up a GitHub Actions Postgres deployment against a throwaway database, and every pull request becomes a full dress rehearsal. Syntax and semantic errors, ordering problems, and broken idempotency surface before the merge — not on staging.

The key points up front:

  • What a database CI gate must catch: the three error classes — syntax and semantic errors, ordering problems, broken idempotency.
  • The throwaway database: Postgres as a service container, spun up fresh for every CI run — without a single secret.
  • The gate: every pull request deploys the complete schema against the empty database and runs the object tests.
  • The double-deploy trick: a second deploy in the same run is the cheapest idempotency test there is.
  • From the gate to real environments: environments, secrets, and branch rules for deploying to dev through prod.

Prerequisite: A GitHub repository with Actions and deploy scripts following the directory-runner model from Deploying a SQL Schema Without a Migration Tool — the gate merely calls them. Every step and error picture shown here was replayed against Postgres 17 in a throwaway Docker container, including the negative tests. The pipeline comes from the open DI² Starter Kit on GitHub, where it guards the example schema tree.

Contents

What a Database CI Gate Must Catch

The usual “CI for databases” advice stops at linting: a style checker over the SQL files, maybe a schema diff. That is not wrong — but the errors that actually abort a deploy are not the ones a linter finds. They only show up when the scripts run against a real database. There are three classes, in ascending order of treachery:

Syntax and semantic errors. The trivial half — a missing comma, a mistyped keyword — is caught by a linter, because that is pure syntax. The other half can only be checked by a database, because it is semantics: whether the mistyped type varchr(2) exists, whether the required extension is installed, whether the function being called has that signature. To a parser this is all valid SQL — only execution reports the missing type. With ON_ERROR_STOP, psql aborts at the first error, and the CI run goes red.

Ordering problems. The convention-based deploy model executes object files in numeric order. If an early-numbered file touches an object that is only created later — an ALTER TABLE in the 000 file on a table from the 005 file — the development database will not show it: the table has existed there since some earlier deploy. Only an empty database exposes the error:

psql:db/schemas/app/tables/000.add-country-code.sql:2: ERROR:  relation "app.customer" does not exist
exit code: 3

This is exactly why “works on my dev box” is no proof — the error is waiting for the next environment that gets built from zero. In the test it disappeared as soon as the file was renumbered behind the table file: same scripts, right order, green run.

Broken idempotency. The deploy model requires every object file to be runnable any number of times. A bare CREATE TABLE without IF NOT EXISTS violates that — and still stays invisible as long as each file only ever runs once. The first deploy is green; only the second one fails. So this class needs not just a real database but a second pass — more on that below.

All three classes share the same denominator: they need a full dress rehearsal against a database that knew nothing beforehand. And that is exactly what GitHub Actions can wire up for a few cents of compute per pull request.

The Throwaway Database: Postgres as a Service Container

GitHub Actions ships the required piece of infrastructure natively: service containers. The services: block starts a Docker container next to the job; it lives for the duration of the run and then disappears together with the database — nobody has to clean up the throwaway database:

  1: name: CI
  2:
  3: on:
  4:   pull_request:
  5:     branches: [main]
  6:
  7: permissions:
  8:   contents: read
  9:
 10: jobs:
 11:   db-dry-run-deploy:
 12:     runs-on: ubuntu-latest
 13:
 14:     services:
 15:       postgres:
 16:         image: postgres:17
 17:         env:
 18:           POSTGRES_PASSWORD: pw
 19:         ports:
 20:           - 5432:5432
 21:         options: >-
 22:           --health-cmd "pg_isready -U postgres"
 23:           --health-interval 10s
 24:           --health-timeout 5s
 25:           --health-retries 5
 26:
 27:     env:
 28:       # postgres superuser password = the service container's POSTGRES_PASSWORD (not a secret)
 29:       DB_ADMIN_PASSWORD_POSTGRES: pw
 30:       PGPASSWORD: pw

Three spots decide whether this runs reliably:

  • The health check (lines 21 through 25). The Postgres container does not report ready immediately — it initializes its data directory on first start. Without a health check, the first job step starts while the database is still coming up, and the first psql connect fails sporadically. With pg_isready as the --health-cmd, GitHub Actions waits until the container really accepts connections, and only then starts the steps.
  • The port mapping (lines 19 and 20). The steps run directly on the runner and reach the service container via localhost:5432 — the same connection details as with a local database.
  • The version pin (line 16). postgres:17 instead of postgres:latest — pin the image to the major version that runs in production. Otherwise the gate will eventually be testing, silently, against a newer engine than the one you deploy to. If the gate should check several major versions in parallel — say, in preparation for an engine upgrade — a strategy.matrix over the image version turns the same job into multiple runs.

What is striking is what is missing: secrets. The password pw sits in plain text in the workflow (lines 18, 29, 30) — and here that is the right call. The database exists for a few minutes, only inside the runner, with no real data. Going without secrets has a tangible side benefit: GitHub does not hand secrets to pull requests from forks — a gate without secrets therefore also runs for fork PRs, which makes all the difference for open repositories. And the pattern is tool-neutral: GitLab CI has an almost word-for-word services: equivalent, Azure DevOps starts the container as part of a container job — the throwaway database is not a GitHub specialty.

The Gate: Full Deploy and Tests on Every Pull Request

The job’s steps now run exactly the sequence a new real environment would go through — bootstrap, full deploy, tests. The trick is that the throwaway database is treated like a perfectly normal environment: in the deploy model from Deploying a SQL Schema Without a Migration Tool, local is simply an environment configuration like dev or prod, just with committed throwaway credentials:

  1:     steps:
  2:       - name: Checkout
  3:         uses: actions/checkout@v4
  4:
  5:       - name: Ensure psql client
  6:         run: |
  7:           command -v psql >/dev/null 2>&1 || { sudo apt-get update && sudo apt-get install -y postgresql-client; }
  8:           psql --version
  9:
 10:       - name: Bootstrap (database, schema, roles)
 11:         run: bash db/scripts/create.sh local
 12:
 13:       - name: Full deploy — all schema objects
 14:         run: bash db/scripts/deploy.sh all local
 15:
 16:       - name: Object tests
 17:         run: |
 18:           for f in db/tests/*.sql; do
 19:             echo ">>> test $f"
 20:             psql -h localhost -p 5432 -U postgres -d app_local \
 21:               -v ON_ERROR_STOP=1 \
 22:               -f "$f"
 23:           done
 24:
 25:       - name: Second deploy — idempotency proof
 26:         run: bash db/scripts/deploy.sh all local

The sequence in detail:

  • The bootstrap (lines 10 and 11) creates the database, schema, and roles — with the same script that sets up a real environment. The gate thereby tests the bootstrap itself as a side effect: an error in the role setup goes just as red as an error in a table file.
  • The full deploy (lines 13 and 14) applies all object files in section and numeric order. This is where syntax errors and ordering problems fall — the empty database has no leftovers behind which a wrong numbering could hide.
  • The object tests (lines 16 through 23) run as ordinary psql calls with ON_ERROR_STOP (line 21): one SQL file per object with DO $$ … ASSERT blocks — happy path, expected error guards, constraints. If an assertion fails, the loop aborts and the run goes red. If you want more than object assertions — test suites, TAP output, hundreds of checks — reach for the pgTAP test framework; for the gate here, psql is enough.
  • The second deploy (lines 25 and 26) is the gate’s punch line and gets the next section.

The psql client step (lines 5 through 8) is a safeguard: the ubuntu-latest runners usually ship the client, and the guard makes the workflow independent of that image detail.

For the gate to really be a gate, the job belongs in the target branch’s branch protection rules as a required status check — a red db-dry-run-deploy then blocks the merge, no matter how urgent that Friday afternoon feels.

The Double-Deploy Trick

The deploy model promises idempotency: every object file describes the desired state and can run any number of times. But a promise is not a proof. The proof costs a single extra line in the CI run: the same deploy.sh all local a second time, against the now-populated database. If it runs through cleanly, repeatability is demonstrated for every object type — not asserted.

Strictly speaking, the second run proves error-free repeatability: every file runs a second time without aborting. That it also leaves the same result behind — and does not, say, silently increment a value — remains a convention of the object files; the double deploy exposes the violations that raise an error, not the ones that silently change data.

What the second pass catches is shown by the negative test: a table file with a bare CREATE TABLE instead of CREATE TABLE IF NOT EXISTS. The first deploy runs green — the table is new, after all. The second one aborts:

psql:db/schemas/app/tables/005.customer.sql:9: ERROR:  relation "customer" already exists
exit code: 3

Without the double deploy, this error would have been merged — and would have blown up on the next regular deploy against an existing environment, which is exactly where it hurts most. With it, it is a red line in the pull request.

The second pass proves a second, subtler thing: the skip path of the run-once scripts. Data change scripts — backfills, conversions — may only run exactly once per database; the deploy runner records them in a tracking table for that purpose. On the second deploy within the same CI run they are already registered, and the log has to skip them instead of executing them again:

postdeploy: app/postdeploy/202607061000.backfill-customer-country-code.sql skipped (already applied)

Every pull request thus automatically tests both paths of the tracker — the apply path on the first deploy, the skip path on the second. A bug in that logic, say a backfill running twice, would become visible here, not on an environment with real data.

The price for all of this: one more pass against a database that is already standing — seconds for small and medium schemas, correspondingly more for very large object trees. Measured against what it catches, it is the cheapest idempotency test there is.

From the Gate to Real Environments

The gate checks; delivery happens elsewhere: on dev, test, prod. For this second step the pattern changes — instead of a throwaway database in the runner, a manually triggered workflow that deploys onto the target host via SSH. And instead of committed throwaway passwords, real secrets now, managed through GitHub Environments:

  1: name: DB - deploy
  2:
  3: on:
  4:   workflow_dispatch:
  5:     inputs:
  6:       environment:
  7:         description: 'Target environment'
  8:         required: true
  9:         default: 'dev'
 10:         type: choice
 11:         options: [dev, test, prod]
 12:
 13: jobs:
 14:   deploy:
 15:     name: Deploy to ${{ github.event.inputs.environment }}
 16:     runs-on: ubuntu-latest
 17:     environment: ${{ github.event.inputs.environment }}
 18:
 19:     steps:
 20:       - name: deploy.sh via SSH
 21:         uses: appleboy/ssh-action@v1.2.0
 22:         env:
 23:           DB_FW_PASSWORD: ${{ secrets.DB_FW_PASSWORD }}
 24:         with:
 25:           host: ${{ secrets.DEPLOY_SSH_HOST }}
 26:           username: ${{ secrets.DEPLOY_SSH_USER }}
 27:           key: ${{ secrets.DEPLOY_SSH_PRIVATE_KEY }}
 28:           envs: DB_FW_PASSWORD
 29:           script: |
 30:             set -e
 31:             cd "${{ vars.DEPLOY_PATH }}"
 32:             git fetch origin "${{ github.ref_name }}"
 33:             git reset --hard "origin/${{ github.ref_name }}"
 34:             bash db/scripts/deploy.sh all "${{ github.event.inputs.environment }}"

The environment logic sits in line 17: environment: binds the job to the GitHub Environment chosen in the dispatch form (lines 6 through 11). One Environment is created per stage, once — and inside it live the secrets (the SSH access and the schema owner’s password that deploy.sh connects with, lines 23 and 25 through 27) and the variables (the repository checkout path on the host, line 31). Three configuration details save you debugging later:

  • Secrets go into the Environment, not to the repository level. As soon as the job pins an environment:, it pulls secrets from exactly that Environment — secrets created at the repository level arrive empty there. That is the most common stumble during setup.
  • Deployment branch rules control, per Environment, which branch may deploy — say, dev and test from dev, prod only from main. The workflow itself needs no branch logic of its own; GitHub refuses the wrong branch natively.
  • Required reviewers on the prod Environment put a human approval in front of the job: the dispatch waits until an authorized person confirms. The approval gate for production is thereby configuration, not code.

Deliberately, the second step needs no more than that — the actual deploy intelligence (ordering, idempotency, run-once tracking) lives in the scripts that the PR gate has already proven against the throwaway database. The dispatch workflow merely calls them on a different host. The SSH route is deliberately the small solution — one host, one checkout, one script call. If your infrastructure is already orchestrated through self-hosted runners, Kubernetes, or a deployment tool, you replace exactly this one step and keep the gate, the Environments, and the approval unchanged.

What the Gate Does Not Catch

An honest gate knows its limit, and the limit has a name: empty database. The gate proves that the schema builds from zero, error-free and repeatably. It does not prove that the deploy will go well on a populated existing environment:

  • Data-dependent effects stay invisible. A backfill runs against zero rows in the gate — UPDATE 0, done. Whether the same statement sets the right values on twenty million existing rows, whether a late SET NOT NULL hits legacy data with NULL values — that is only decided on the real environment. The common complement for this class is a deploy test against an anonymized production dump — as a nightly job, not in the PR gate; it is too slow for that.
  • Locks and runtimes do not show. ALTER TABLE on an empty table finishes in milliseconds; the same statement on a large existing table takes locks whose duration and queueing effects the gate does not model.
  • Drift on real environments is out of view. If someone ran a manual ALTER TABLE on prod, the gate knows nothing about it — it tests the repository state, not the actual state of the environments. That is the domain of change tracking.
  • Configuration errors of the real environment — missing secrets, wrong paths, SSH permissions — only surface on the dispatch deploy. The gate deliberately runs without that infrastructure.

The limit does not diminish the value; it puts it in place: the gate eliminates the error classes that can be proven structurally — syntax and semantics, ordering, idempotency. For the data-dependent classes, their own disciplines apply: the expand/contract pattern for structural changes on populated tables and run-once tracking for data changes.

FAQ

How do you run PostgreSQL in GitHub Actions?

As a service container: declare a services: block in the job with image: postgres:17, POSTGRES_PASSWORD, and the port mapping 5432:5432, plus a health check with pg_isready. GitHub Actions starts the container next to the job and, thanks to the health check, waits until the database accepts connections; the steps then reach it at localhost:5432.

How do you test database changes in CI without a migration framework?

With a full dress rehearsal: on every pull request, deploy the complete schema against an empty throwaway database, then run object tests as psql calls with ON_ERROR_STOP. That catches syntax and semantic errors as well as ordering problems that no linter sees — an empty database has no leftovers for errors to hide behind.

What does a second deploy in the same CI run buy you?

It proves the idempotency of the deploy scripts: every object file must also run cleanly against the already-populated database. A non-repeatable statement — say, a CREATE TABLE without IF NOT EXISTS — passes the first deploy and only fails on the second. As a bonus, the second run tests the skip path of the run-once tracking.

Does the CI gate need access to database secrets?

No — deliberately not. The throwaway database lives only inside the runner, holds no real data, and uses committed throwaway credentials. That way the gate also runs for pull requests from forks, which GitHub does not hand secrets to. Real secrets only enter the picture when deploying to real environments — managed there in GitHub Environments, not at the repository level.

Does the pattern also work in GitLab CI or Azure DevOps?

Yes. The pattern — spin up a throwaway database, full deploy, object tests, second deploy — is tool-neutral. GitLab CI declares the database almost word-for-word in the pipeline’s services: section; Azure DevOps starts it as a container resource or via docker run in the agent job. Only the environment/approval mechanics of the second step go by different names in each tool.