DEV Community

octave Nkurunziza
octave Nkurunziza

Posted on Originally published at blog.hubfly.space

Gate your app on a Postgres migration job with service_completed_successfully

#devops #docker #tutorial #postgres

Originally published on the Hubfly blog.

TL;DR

  • What you get: a 3-service Compose file (Postgres, a one-shot migration job, an API) where the API cannot start until the migration exits 0.
  • Who it's for: anyone whose app has ever booted against a database with a missing table.
  • Time: about 20 minutes. Our second rollout took 59 seconds from click to "Stack deployment finished".

Your app started before your schema did

You push a release. The API boots, runs its first query, and gets relation "todos" does not exist. The migration ran too, just 3 seconds later.

Most Compose files only say depends_on: [db]. That waits for the database container to start, not for the schema.

Docker's docs say so plainly: Compose waits until a dependency is running, not until it is ready (Docker docs).

The fix is well known: a healthcheck on Postgres, a one-shot migration service, and condition: service_completed_successfully on the app. What the popular guides skip is what happens when the gate itself misfires. We ran this on real infrastructure and it misfired on the first try. This post shows the file that survived.

What we're building 

db (postgres:16-alpine)          stage 1
  │  healthcheck: pg_isready over TCP
  ▼  condition: service_healthy
migrate (one-shot job, exits 0)  stage 2
  │  waits for DNS + port, then runs idempotent SQL
  ▼  condition: service_completed_successfully
api (PostgREST, serves /todos)   stage 3
Enter fullscreen mode Exit fullscreen mode

The API is PostgREST, which turns a Postgres schema into a REST API. It's a good test subject because it reads the schema at boot. If the migration hasn't run, there is nothing to serve.

Hubfly's composer turned these 3 services into 3 deploy stages. It marked migrate as a Job with restart policy Never, because of restart: "no".

The stack composer showing db, migrate and api as connected stages

Step 1: Postgres with a healthcheck that tells the truth 

db:
  image: postgres:16-alpine
  environment:
    POSTGRES_DB: app
    POSTGRES_USER: app
    POSTGRES_PASSWORD: lab-only-change-me   # replace before you deploy
  volumes:
    - pgdata:/var/lib/postgresql/data
  healthcheck:
    test: ["CMD-SHELL", "pg_isready -h 127.0.0.1 -U app -d app"]
    interval: 5s
    timeout: 3s
    retries: 10
Enter fullscreen mode Exit fullscreen mode

Note the -h 127.0.0.1. Without it, pg_isready checks the Unix socket.

On first boot, the official image starts a temporary server to create your database. In our logs, that temporary server listened only on the Unix socket. The real server came up afterwards with listening on IPv4 address "0.0.0.0", port 5432.

A socket check could go green during that setup window, so we switched to TCP as a precaution. A TCP check only passes once other containers can actually connect.

Step 2: a migration job that waits, then runs idempotent SQL 

migrate:
  image: postgres:16-alpine
  restart: "no"
  environment:
    PGHOST: "{{service:db.host}}"
    PGUSER: app
    PGPASSWORD: lab-only-change-me
    PGDATABASE: app
  command:
    - sh
    - -c
    - |
      timeout 60 sh -c 'until pg_isready -t 2; do echo "waiting for db"; sleep 2; done' || exit 1
      psql -v ON_ERROR_STOP=1 \
        -c "CREATE TABLE IF NOT EXISTS todos (id serial PRIMARY KEY, task text NOT NULL, done boolean NOT NULL DEFAULT false)" \
        -c "DO 'BEGIN IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = ''web_anon'') THEN CREATE ROLE web_anon NOLOGIN; END IF; END'" \
        -c "GRANT USAGE ON SCHEMA public TO web_anon" \
        -c "GRANT SELECT ON todos TO web_anon" \
        -c "INSERT INTO todos (task) SELECT 'ship the migration gate' WHERE NOT EXISTS (SELECT 1 FROM todos)"
  depends_on:
    db:
      condition: service_healthy
Enter fullscreen mode Exit fullscreen mode

Three details matter here.

  1. The wait loop. This is the line that saved our second deploy. More on why below. The timeout 60 keeps a broken database from hanging the rollout forever; after a minute the job exits 1 and the gate stays closed.
  2. ON_ERROR_STOP=1. Without it, psql happily continues past a failed statement and exits 0. Your gate would open on a half-migrated schema.
  3. Every statement is safe to re-run. The job runs on every deploy, and the volume keeps your data between deploys. We ran it twice against the same database. The second run printed NOTICE: relation "todos" already exists, skipping and INSERT 0 0, and exited 0.

{{service:db.host}} is Hubfly's service reference. At deploy time it became the database's private hostname, db-1-7befe5b4.eucentral.hubfly.internal.

On plain Docker Compose (we didn't test this part), replace {{service:db.host}} with the service name db in both places: PGHOST: db and PGRST_DB_URI: postgres://app:…@db:5432/app.

The DO '...' block uses ordinary quotes instead of $$. That's deliberate: Compose treats $ as variable interpolation, and quoted SQL dodges the whole escaping question.

The second run of the migration on the same volume (NOTICE already exists, INSERT 0 0, EXIT=0)

Step 3: the app waits for the job's exit code 

  api:
    image: postgrest/postgrest:v12.2.3
    environment:
      PGRST_DB_URI: "postgres://app:lab-only-change-me@{{service:db.host}}:5432/app"
      PGRST_DB_SCHEMAS: public
      PGRST_DB_ANON_ROLE: web_anon
      PGRST_SERVER_PORT: "3000"
    ports:
      - "3000:3000"
    depends_on:
      migrate:
        condition: service_completed_successfully

volumes:
  pgdata:
Enter fullscreen mode Exit fullscreen mode

service_completed_successfully means exit code 0, nothing else. If the job exits 1, the API is never created. That's the whole point: a bad migration stops the release instead of shipping it.

The anonymous role gets SELECT only. We checked: a POST /todos from the browser returned 401 with permission denied for table todos.

For production, have PostgREST log in as a dedicated low-privilege authenticator role instead of the app owner. We kept one user here to keep the file short.

Here's the whole file, exactly as we deployed it:

services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: app
      POSTGRES_USER: app
      POSTGRES_PASSWORD: lab-only-change-me
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -h 127.0.0.1 -U app -d app"]
      interval: 5s
      timeout: 3s
      retries: 10

  migrate:
    image: postgres:16-alpine
    restart: "no"
    environment:
      PGHOST: "{{service:db.host}}"
      PGUSER: app
      PGPASSWORD: lab-only-change-me
      PGDATABASE: app
    command:
      - sh
      - -c
      - |
        timeout 60 sh -c 'until pg_isready -t 2; do echo "waiting for db"; sleep 2; done' || exit 1
        psql -v ON_ERROR_STOP=1 \
          -c "CREATE TABLE IF NOT EXISTS todos (id serial PRIMARY KEY, task text NOT NULL, done boolean NOT NULL DEFAULT false)" \
          -c "DO 'BEGIN IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = ''web_anon'') THEN CREATE ROLE web_anon NOLOGIN; END IF; END'" \
          -c "GRANT USAGE ON SCHEMA public TO web_anon" \
          -c "GRANT SELECT ON todos TO web_anon" \
          -c "INSERT INTO todos (task) SELECT 'ship the migration gate' WHERE NOT EXISTS (SELECT 1 FROM todos)"
    depends_on:
      db:
        condition: service_healthy

  api:
    image: postgrest/postgrest:v12.2.3
    environment:
      PGRST_DB_URI: "postgres://app:lab-only-change-me@{{service:db.host}}:5432/app"
      PGRST_DB_SCHEMAS: public
      PGRST_DB_ANON_ROLE: web_anon
      PGRST_SERVER_PORT: "3000"
    ports:
      - "3000:3000"
    depends_on:
      migrate:
        condition: service_completed_successfully

volumes:
  pgdata:
Enter fullscreen mode Exit fullscreen mode

Deploy it on Hubfly

  1. Create a project, open Create → Compose Stack, choose the YAML tab, paste the complete file from the end of Step 3 and click Import YAML.
  2. Check the canvas: 3 services, the pgdata volume, and 2 DEPENDS arrows. The composer's monthly estimate for this stack on the shared tier was $0.01.
  3. Click Deploy Stack and watch the activity log.
  4. On the api container, open Network → Add Domain, pick port 3000, and create the subdomain.
  5. Open https://<your-subdomain>.eu1.hubfly.app/todos.

We got this back:

The browser showing the JSON response from /todos

[{"id":1,"task":"ship the migration gate","done":false}]
Enter fullscreen mode Exit fullscreen mode

A second GET /todos from the browser took 271 ms end to end.

One surprise: ports: "3000:3000" also created a public TCP endpoint on tcp.eu1.hubfly.app by itself. That's fine for an API. Never add ports: to db.

What the 59 seconds looked like

Time (CAT) Event
06:32:16 Deploy starts, access validated
06:32:20 Volume pgdata reused
06:32:36 db deployed (stage 1)
06:32:38 db healthcheck satisfied
06:32:53 migrate deployed (stage 2)
06:32:55 migrate exited 0, gate opens
06:33:13 api deployed (stage 3)
06:33:14 Stack deployment finished

The Compose deployment activity log for the second run, stage by stage

Hubfly marked db healthy about 2 seconds after it deployed, in both runs. So don't rely on the healthcheck alone. The wait loop is what made the second run work.

Why our first deploy failed

Our first file didn't have the wait loop or the TCP healthcheck. Here's what happened.

The first deploy, showing

db went healthy at 06:28:24. migrate deployed at 06:28:36 and failed 2 seconds later. The API was never created, so the gate did its job. But why did a migration fail against a healthy database?

The job's container was rolled back, so its logs were gone. We can't prove the cause, but 2 clues from the second run point to it.

The migration job's own first line, with the wait loop in place:

db-1-7befe5b4.eucentral.hubfly.internal:5432 - no response
waiting for db
db-1-7befe5b4.eucentral.hubfly.internal:5432 - accepting connections
CREATE TABLE
...
Enter fullscreen mode Exit fullscreen mode

And PostgREST's first 2 seconds of life (container logs are in UTC; the deploy table is CAT, UTC+2):

PostgREST logs showing 

04:33:10 Failed to query the PostgreSQL version ... could not translate host name
         "db-1-7befe5b4.eucentral.hubfly.internal" to address: Name does not resolve
04:33:12 Successfully connected to PostgreSQL 16.15
04:33:14 Schema cache loaded 1 Relations
Enter fullscreen mode Exit fullscreen mode

In the second run, a freshly started container could not resolve the database's private hostname for about 2 seconds. PostgREST shrugs that off because it retries. A one-shot psql doesn't retry. It fails, exits non-zero, and takes your deploy down with it.

That's not a Hubfly quirk you can ignore elsewhere. On any orchestrator, "the dependency is healthy" and "my container can reach it right now" are two different facts. A job that runs once must check the second one itself.

What can go wrong

1. The job fails in 2 seconds and you can't see why.
A failed job may be rolled back with its logs. Add the wait loop from Step 2, and make the job print what it's doing (echo "waiting for db"). If it still fails, the next run's logs will show whether it's DNS, the port or the SQL.

2. "Healthy" doesn't mean "reachable".
pg_isready without -h checks the Unix socket, and the first-boot setup server listens only there. Use pg_isready -h 127.0.0.1 -U app -d app, and still keep the wait loop in the job: in our runs, db was marked healthy about 2 seconds after deploy either way.

3. A failed rollout leaves an old database behind.
After our failed first deploy, the rollback of db itself failed (Rollback failed for 'db'). The old db was left behind, asleep and still attached to the volume. Our second deploy then created db-1 next to it, on the same pgdata volume.

The project view with db (sleeping) and db-1 (running) side by side

Two Postgres servers on one data directory is how you lose data. Before you redeploy after a failure, open the project, and stop or remove any leftover database container.

4. The migration isn't idempotent.
The job runs on every deploy. CREATE TABLE without IF NOT EXISTS will fail on deploy number 2 and block every release after it. Guard every statement, or use a migration tool that records what it applied (Prisma's migrate deploy, Flyway, golang-migrate, Alembic).

Takeaway

service_completed_successfully is a good gate. It only works if the job behind it is honest: it waits for the database itself, stops on the first error, and is safe to run again. Put those 3 properties in your migration job and a broken schema stops the release instead of reaching users.

Deploy this

Paste the Compose file into a new Hubfly project (Create → Compose Stack → YAML) and click Deploy Stack. The shared tier needs no card. Change lab-only-change-me first.

What it costs

The composer estimated $0.01/month for this stack on the shared tier. That matches a 1 GB volume: 1 GB × $0.00001536 per GB-hour × 730 h = $0.011 (the free tier includes 1 GB of storage).

Shared containers auto-sleep, and their compute is free within the free tier's 360 hours a month. Two containers kept awake all month would use 1,460 hours.

If you keep db and api awake using a dedicated container (0.5 vCPU, 256 MB):

CPU: 0.5 = $1.46

RAM: 256 MB = $0.36

Per container: $1.94/month; for db + api: $3.88/month, plus $0.11 of storage

The migration job runs for a couple of seconds per deploy, which rounds to $0.00.

Read next:

Top comments (0)