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.

I have a pretty standard server layout:

  • 2 Application servers running Tomcat on Redhat.
  • 2 Database servers running Postgres on Redhat. One database is a master and replicates to the slave.

I'd like to automatically upgrade the scheme when I update the web apps. The web apps are deployed using RPMs. I'm at a loss to figure out if there's a recommended way of doing this.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Unless the tools you're using have built-in support for database schema migrations (like ActiveRecord/Rails does, albeit rather unsafely) - you need to do it yourself in your application.

The usual technique is to have a table in your DB that contains metadata about the schema and application. You might structure it in key/value form, or as a single row with multiple cols; it doesn't matter. Either way, this table should store a schema version.

When you connect on app startup, before doing anything else with the DB, you should query the metadata table for the schema version. If it's less than the current version in the application, your application should apply schema change scripts from the old version to the new version. I used @Singleton @Startup EJB3s for that, and read the schema change scripts using the classloader from property files embedded in the application jars; you're probably using Spring or some similar framework with similar capabilities.

Because you have two application servers, you'll probably have to LOCK TABLE your_database_metadata_table before beginning to apply changes to ensure that both servers don't attempt to apply the same database migration scripts.

DDL changes are easier in PostgreSQL than some other DBs because of its support for transactional DDL. You can BEGIN a transaction, apply all the database changes in one go - DDL, metadata table updates, etc etc - and COMMIT. You won't land up with a half-applied schema change. This is only viable when the schema change won't take too long or you can afford a delay. If you're ALTERing the data type of a 100-million row table, you don't want to do that in a single big transaction unless everything else can wait.

Replication is taken care of automatically if you're using the built-in streaming replication; it doesn't differentiate between DDL and DML, they're are all just database changes as far as the replication system is concerned.

If you're using another replication engine like Slony-I or Bucardo, then it depends on how the replication engine deals with schema changes. This is engine specific, so you need to look into the docs for your replication engine. (The current mess with 3rd party replication and DDL changes should improve dramatically some time after the 9.3 event triggers, logical replication and command deparsing support is added to PostgreSQL core. Some of that looks like it'll slip to 9.4.)

Now, if you want to do your DDL changes non-disruptively without interruptions to the applications, that's a whole other level of fun.

share|improve this answer
    
This sounds about right. I was considering using Flyway or DbDeploy for the versioning. You're suggesting that you get the app to do the upgrade? I was going to get the install of the RPM to do it. Thoughts on that? –  alex.collins Jan 3 '13 at 11:03
    
@alex.collins Pre- and Post-install scripts in the RPM should work fine, with the same caveats about ensuring that only one instance attempts to upgrade the DB and ensuring that aborted installs roll changes back cleanly. –  Craig Ringer Jan 3 '13 at 12:35
add comment

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.