October 4, 20268 min read

Database Migrations Without Kubernetes: A Ptah CI Pipeline

Run a dispatch worker only after its migration and schema checks pass, then test drift, SQL failure, retries, and cancellation.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

A dispatch worker needs a new dispatch_note column. Starting that worker before the database change makes its first insert fail. Applying the SQL is only part of the release: the pipeline also needs to establish which database state it found and whether the resulting schema matches the application.

We exercised this release with Ptah 0.12.0, PostgreSQL 18.6, and a Python worker using psycopg 3.3.6. The supporting files contain the workflow, application, migration pairs, and recorded results. The example uses disposable databases and synthetic shipments.

Use CI for a bounded release

A CI job can check a known starting schema, run the migrations for one release, verify the result, and start the application. That fits a service whose migration sequence belongs to its release pipeline. Kubernetes is not needed for this sequence.

The job ends when the release finishes. It will not observe a manual schema change tomorrow unless another job checks again. The Ptah operator is useful when the cluster should keep observing a declared schema and report divergence between releases. Choose that lifecycle when continuous reconciliation belongs in the system; CI remains a useful place to test and review the change.

Our worker starts with two shipments. Its new version inserts a third shipment with a dispatch note, then checks all three rows. Before the migration, that insert failed because dispatch_note did not exist. After the migration, the worker returned the expected rows.

The application change needs one migration:

ALTER TABLE app.shipments ADD COLUMN dispatch_note text;

Adding a nullable column lets existing rows keep their values. This example does not prove that every application release is compatible with the old schema. For a change that spans deployed versions, see the checkout rollout.

Check the starting state while holding the migration lock

The migration directory includes the previous release and the new column. The lab first applies the previous version and loads its two shipments. schema-v1.sql describes that starting schema; schema.sql describes the new version. Both comparisons are limited to the app schema. Roles, network policy, and the ops revision table have separate checks.

The migration step receives the migrator connection as PTAH_DB_URL and runs:

ptah migrations validate --dir migrations
ptah migrations up --migrations-dir migrations --verify-sum \
  --migration-lock-timeout 10s --lock-timeout 3s --statement-timeout 30s \
  --pre-up-hook 'ptah schema compare --schema-file schema-v1.sql --schemas app --exit-code'
ptah schema compare --schema-file schema.sql --schemas app --exit-code

The integrity check detects SQL edited after ptah.sum was written. The pre-up hook invokes another native Ptah command to compare the starting schema. In the tested release, Ptah calls this hook under its migration advisory lock before executing pending SQL. A nonzero hook result stops the migration.

We added an unexpected column before deployment. The hook reported schema drift and migrations up returned 2. The new dispatch column was absent afterward. The comparison printed SQL that could remove the unexpected column, but the pipeline did not execute that SQL. A release should stop for review when its assumed starting state is wrong.

The advisory lock coordinates cooperating migration processes. It does not prevent a database owner from issuing unrelated DDL through another tool. Control those access paths as part of the deployment policy.

Start the application after the final check

Successful SQL execution does not establish that the release reached its intended schema. The final comparison returned:

No schema differences detected.

Only then did the workflow run application.py with the separate APP_DB_URL. The worker inserted shipment 3 and verified the existing destinations and nullable notes. Its connection did not use the migration role.

The runnable workflow exercises these boundaries:

Case	Database result	New worker
Reviewed migration	Target schema matches; third shipment inserted	Runs successfully
Unexpected starting column	Pre-up comparison stops the migration	Skipped
Division by zero after the ALTER	PostgreSQL rolls back the new column; Ptah records failure	Skipped
Valid SQL that adds an extra column	SQL commits, but the final comparison fails	Skipped

All four cases ran in GitHub Actions. The recorded job steps show the worker succeeding in the first case and being skipped in the other three. Each job used its own PostgreSQL service. The overlap and process-termination experiments below were separate local runs.

The last case matters during recovery. Its database has changed even though the release failed. Skipping the worker does not undo committed SQL. Inspect both the database and migration history before choosing the next action.

Separate the permissions

The lab administrator creates the database and the app and ops schemas. The migrator owns those schemas but has no superuser, database-creation, role-creation, or replication privileges. Its database-specific search_path places the revision table in the precreated ops schema. Neither role has database-level CREATE permission.

The worker receives USAGE on app and SELECT/INSERT on its tables. Its attempts to alter the shipment table and read ops.schema_migrations both failed. The migrator’s attempt to create an unrelated schema also failed. These checks cover the permissions needed by this worker; a different application needs its own reviewed grants.

Both roles connected with sslmode=verify-full and a trusted lab certificate. The successful connection reported TLS 1.3. Using an IP address absent from the certificate failed hostname verification. Disabling TLS failed the server’s connection policy. PostgreSQL documents the trust and hostname checks for verify-full.

The CI database is a service on the job’s runner. Production needs a runner with an authorized network route to the database, an appropriate trust store, and credentials scoped to that target. The fixed passwords in bootstrap.sql are disposable fixture values. They are not a pattern for storing deployment credentials.

Serialize releases and handle retries deliberately

We launched two Ptah processes against the same database. The first held the migration lock while its transaction executed a short pg_sleep. The second waited, then finished successfully after the first committed. The journal contained one applied record per migration. A competing run with a 300 ms migration-lock timeout instead returned 2 while the first continued.

The timeout flags bound different waits: --migration-lock-timeout limits entry to the migration run, while --lock-timeout and --statement-timeout constrain work inside each migration transaction. They do not impose a complete application-release deadline.

CI concurrency should cover the entire target release, including application promotion. The rehearsal uses a fixed concurrency group with cancel-in-progress: false. A production group should identify the deployment target consistently across branches and workflows. GitHub’s default queue retains only one pending run, so a later queued release can replace an earlier one. That is a CI scheduling property; Ptah’s database lock cannot change it. See GitHub’s concurrency rules.

A retry after a successful migration returned 0 and left the journal and rows unchanged. With no pending SQL, Ptah skipped the pre-up hook. The final schema comparison still ran. We added an unexpected column after success: up returned 0 with no pending SQL, but the final comparison returned 1. That is why the target check stays on the retry path.

An interrupted migration has a different recovery path. We sent SIGTERM while the transaction was sleeping. The process exited 143. PostgreSQL rolled back the added column, but Ptah retained a failed revision with applied=0/2. An ordinary retry returned 2 and reported the dirty state. After inspecting that state, we explicitly authorized a retry of the unchanged migration:

ptah migrations up --migrations-dir slow --verify-sum \
  --migration-lock-timeout 10s --lock-timeout 3s --statement-timeout 30s \
  --pre-up-hook 'ptah schema compare --schema-file schema-v1.sql --schemas app --exit-code' \
  --allow-dirty

The retry succeeded, the final schema matched, and the worker ran. Keep that permission out of an automatic retry loop. This experiment tested a native process termination and PostgreSQL transactional DDL. It did not test GitHub’s runner-cancellation sequence or recovery from nontransactional statements.

Keep production credentials behind a separate boundary

The checked-in workflow is a release rehearsal. It runs on pull requests, uses a disposable database, and references no deployment secrets. Actions are pinned to commit SHAs; the PostgreSQL image is pinned by digest. The Ptah archive is checked against a fixed SHA-256 before extraction. The runner image itself can receive updates, so the evidence records the actual tool versions.

Production promotion needs a trusted workflow and an approved immutable revision of the migrations and application. ptah.sum detects a change relative to that checksum file; an author who changes both files has created a different artifact. Review must bind to the commit or artifact digest that the deployment will actually consume. Do not regenerate checksums during promotion or resolve a moving branch after approval.

A GitHub job can reference a protected environment whose branch rules and reviewers restrict access to deployment secrets. Those protections must be configured separately, and availability depends on the repository and plan. Naming an environment in YAML does not prove that its approvals are configured. Our rehearsal did not configure or test production environment approvals. GitHub documents the environment controls.

Keep untrusted PR code on runners without production credentials or a route to production. A privileged pull_request_target or workflow_run job must not execute untrusted checkout content or accept its artifacts as approved release inputs. Use narrowly scoped credentials, expose them only to the relevant step, and review captured output before retaining it. GitHub’s secure-use guidance covers those workflow boundaries.

The working release path remains small: verify the reviewed files, check the starting schema under the migration lock, apply, check the target, and start the worker. The recorded failures show why each gate stays in that sequence.

Put Ptah to work

Example files

← All articles