How to Dump and Restore a Database in GitHub Actions

Run pg_dump to capture the database to a file and pg_restore to reload it; in CI this drives fixture builds and migration smoke tests.

Use the custom format (-Fc) for a compressed, restorable archive. Dump from a source database, then restore into a fresh target with pg_restore --clean --no-owner to verify the dump is consistent.

Steps

• Set PGPASSWORD so pg_dump/pg_restore authenticate non-interactively.
• Dump with pg_dump -Fc to a file.
• Restore into a fresh database with pg_restore --no-owner.

Workflow

steps:
  - name: Dump
    run: pg_dump -Fc -h localhost -U postgres app > app.dump
    env: { PGPASSWORD: postgres }
  - name: Create restore target
    run: createdb -h localhost -U postgres app_restored
    env: { PGPASSWORD: postgres }
  - name: Restore
    run: pg_restore -h localhost -U postgres -d app_restored --no-owner app.dump
    env: { PGPASSWORD: postgres }

Gotchas

• A plain SQL dump (default format) is restored with psql -f, not pg_restore; pg_restore needs the custom or directory format.
• Use --no-owner and --no-acl when restoring under a different role than the dump was taken as.

Related guides