Postgres - how to deploy changes from dev database to live version

Question

I apologize if this is a simple question - it's been difficult to find an answer by searching alone.

What is a good workflow for deploying changes from your dev database to the live version?

My app's database is Postgres running on Heroku. It is periodically updated with user content. I have a dev version (with outdated content) running locally.

I have created a new table in my local database, and I would like to deploy it live. How can I do this without duplicating all the work on the live server, and without cloning my local copy?

Thank you so much for your help!

Answer 1

tl;dr

Use Flyway to auto-apply your collection of SQL scripts.

Database Migration

The term "database migration" has appeared to describe this chore of moving changes to the structure of a database from development to testing and on to production machines.

• In the past, developers and administrators accomplished this in an ad-hoc fashion, throwing together bits of SQL and code – plus hope and prayer.
• In recent years some tools have appeared to turn this into an organized, reliable, repeatable, testable, and self-documenting process.

Flyway

The Flyway project is a Java-based kit to track a series of SQL scripts and Java jar files to be used in updating a database.

No special magic here. The tool looks for files named with a convention to put a sequence number on your SQL scripts. The tool creates an extra table within your database to store its meta-data including the number of the last script applied. The tool scans for any newer files present, applies them, and updates the meta-data table with the number of the last applied script.

So deployment means simply moving the latest SQL scripts onto the deployment machine and letting Flyway do its work.

Another benefit is quickly rebuilding a database to a certain point for use in testing. Similarly, good for use with continuous integration to keep the database in correct shape automatically.

The price you pay for these benefits is discipline. You must never again make any on-the-fly change in the structure of the database. All changes must be written as SQL scripts (and optionally as Java code in jars) and applied through Flyway.

Easier if used from the very beginning of the database’s creation. But you can also “baseline” to start using with an existing database.

Flyway includes command-line tools as well as a Java API interface. So even non-Java-centric apps can make use of Flyway.

Open-source, and free-of-cost. Supports many databases including Postgres.

Liquibase

The Liquibase project is another tool quite similar in purpose to Flyway.

Answer 2

I use EMS DB Comparer for PostgreSQL for this task. Unfortunately it's not free but I cannot found any better alternative since years. Can compare two DBs and generate the create/alter/etc scripts. Quite easy, lots of options.