Upgrade production database during software development lifecycle
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.