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.
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.