Apply a schema migration from a SQL file
Medium risk
What do risk levels mean?
- Read-only
- Inspects state without changing anything.
- Low risk
- Reversible with a routine follow-up command.
- Medium risk
- Changes state; undo path documented.
- Destructive
- Deletes or overwrites; confirmation required.
Executes a SQL migration file against the database within a transaction so the entire change succeeds or rolls back atomically.
postgresqlmigrationschemaddl
Parameters
Name of the database to apply the migration to.
Path to the SQL file containing the migration.
Commands
Execute the migration file against the target database
psql -d <database_name> -f <migration_file>Verification
psql -d <database_name> -c "\dt"Tables or schema objects from the migration are now present.
Undo
Remind that rollback requires a separate down-migration file
echo "Rollback requires a corresponding down migration. Create one that reverses the changes in <migration_file> before applying this step."Pitfalls
- psql -f does not wrap the file in a transaction; add BEGIN and COMMIT to the migration file itself for atomicity.
- Partially applied migrations may leave the database in an intermediate state; always run inside a transaction.
- Long migration files may time out; consider breaking them into smaller units.