Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

Thank you!

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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

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.

share|improve this answer
1  
+1 for Flyway and SVN combo. –  alikox Nov 10 '12 at 14:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.