Upgrade production database during software development lifecycle

Question

Background

Using Oracle, there are a number of tools that help with migrating and applying development changes into the production environment (such as Embarcadero Change Manager). These tools can be configured to perform any database DDL upgrade with little to no human intervention.

Problem

I have development and production servers running PostgreSQL 9.x. After the initial deployment of the database DDL to the production server, I will continue to make changes to the development database. These changes will include bug fixes to stored procedures, changes to tables, additional sequences, new views, more types, more tables, and so forth.

Question

What are the steps to upgrade/migrate the DDL for a production PostgreSQL database application in an automatic fashion (or nearly automatically) such that it has the new changes made in development?

Related

• http://apgdiff.startnet.biz/

Thank you!

Answer 1

Advice

Never manually apply database changes; use scripts that are in a version control system (VCS).

After initial deployment, store the "delta" scripts in the VCS. When upgrading, apply all scripts that are missing. Tracking the scripts that have been applied is a standard problem.

Solutions

There are tools to automate this task, including:

• Liquibase
• Flyway

Liquibase

We are pretty satisfied with Liquibase, which stores the schema definition in XML files. A commandline tool then applies a "changelog" to the database. The changelog can be applied to all environments: development, test, staging, production, continuous build, etc.

Flyway

Also consider Flyway, which uses SQL text files with a naming convention.

Other Considerations

The downside of Liquibase and Flyway is that you basically manage an incremental script. If you need to create a database from scratch, you need to run all change sequentially.

Data Modeling Tool

Document your data model using an ER Modeling tool, such as:

• DbSchema
• TOAD Data Modeler
• Erwin

We use a macro to convert TOAD-based models into a Liquibase changelog. This means we can recreate the database "from scratch" from our model documentation, as well as incremental (Liquibase) scripts that are used to maintain existing installations.