migra is written in Python so you need to install it with
pip, the Python Package Manager (don't worry, you don't need to know or use any Python to use the
Make sure you have pip properly installed.
$ pip install migra[pg]
This will install the latest version of migra from PyPI (the global Python Package Index), along with psycopg2, the python PostgreSQL driver.
Confirm migra is installed by running
migra --help. The output should begin like this:
usage: migra [-h] [--unsafe] dburl_from dburl_target
Comparing two database schemas
To compare two database schemas:
$ migra <url_of_database_A> <url_of_database_B>
For example, we have two databases, named "alpha" and "beta". We can compare them using this command:
$ migra postgresql:///alpha postgresql:///beta
Migra will then generate whatever SQL is required to change the schema of database
alpha to match database
If the two database schemas match exactly, you'll get empty output, because no changes are required. This functions like the well-known diff command, which also returns empty output when comparing two identical files.
Don't blindly copy-and-paste the output of the
migra features a safeguard against generation of dangerous statements. If the command generates a drop statement,
migra will exit with an error. If you're sure you want the drop statement(s), you can turn off this safeguard behaviour with the
migra --unsafe postgresql:///alpha postgresql:///beta
Making changes to database schemas
If you're making changes to a serious production database, use a copy of it for these steps instead so you're not changing your production environment until you intend to.
You can make a schema-only dump of your PostgreSQL database with the following command:
pg_dump --no-owner --no-privileges --no-acl --schema-only name_of_database -f schema.dump.sql
Get the connection string of the database you want to make changes to.
migraneeds to connect to this database so it can analyse the database's schema.
Prepare a second PostgreSQL database. This database needs to have the new/desired/target schema. You might create a temporary database and set it up for this purpose.
Generate a migration script using the following command (substituting your own connection strings):
$ migra --unsafe postgresql:///existing postgresql:///database_with_new_schema -f migration_script.sql
Carefully review the migration script in
Consider in particular:
The generated script may result in data loss from your database when you apply this script. Consider if you intend for this to happen or if you need to add statements to copy data out of the relevant tables/columns before you drop them forever.
Some migration operations can take a long time and cause interruptions and downtime, particularly when involving tables containing large amounts of data. More on this here.
migration_script.sqlto your production database with a command similar to the following (again substituting your own connection string).
psql postgresql://production -1 -f migration_script.sql
Schemas within schemas
migra's output to statements relating to a single PostgreSQL schema (schema meaning namespace rather than structure in this case), simply use the
--schema flag. For instance:
migra --schema books postgresql:///a postgresql:///b
In various scenarios it's useful to restrict output to
create extension... statements only. Do this with the