I have a pretty standard server layout:
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.
The Answer by Craig Ringer is more important than this one. But I want to explain how tools can help you with this matter.
We now have database migration tools to help with the chore of updating your:
…as needed when your app(s) evolve.
You have a choice of several tools, including LiquiBase and Flyway.
I have used Flyway successfully in my own work. So a brief description follows, though I urge you to do a bit of research to see which tools fit your needs and style.
Flyway is built in Java, but can be used in non-Java shops as well. All of the Flyway commands are wrapped in command-line executables, so you can use them on the console or from scripts. If you are a Java house, you may choose to call the commands from within Java using objects. And for specialized needs you may choose to write Java classes to do some heavy lifting that might not be possible in straight SQL or SQL-superset (PL/pgSQL etc.) database programming.
Flyway automatically installs a table into your database, to store its own metadata. Flyway looks through designated folders for SQL scripts you have written. The specific naming conventions prescribed by Flyway control the order of execution. Each time you run Flyway, the tool examines all your scripts, looking up each one in that metadata to see if it was already run in this particular database. If not yet run, Flyway runs it and logs the results. If the script was previously run, Flyway uses a checksum to be sure the script has not been edited, otherwise flagging a potential problem.
Using a database migration tool such as Flyway is best done from the beginning of the project. But you can add Flyway to an existing database, see the documentation. Or consider recreating the database using a set of SQL scripts organized for Flyway, if that is feasible for your situation. I like the ability to recreate a new database from scratch entirely through Flyway – handy for development and testing.
Speaking of testing, you can easily add or drop SQL scripts (and optionally Java classes) to do work for testing purposes. Such purposes might include loading a known set of data. Or you might want to set up certain conditions for recreating a bug scenario.
Flyway is quite easy to work with, given its smart defaults and simple design. But you can also customize its behavior, overriding defaults, for special needs.
Flyway works well with Postgres as in operates through JDBC, either its own bundled copy of JDBC drivers or through yours. For non-Java shops, the use of the JDBC driver is transparent to you. Few features of JDBC are actually used, as Flyway does little more than pass your SQL scripts to the database. Besides creating and updating its own metadata table, Flyway is not really touching your database – you are manipulating the database yourself, through those scripts you wrote. Metaphorically, think of Flyway as the postal carrier, while you are the letters’ author.
Flyway is freemium, with a Community Edition that is open-source and free-of-cost. This edition has all the classic features that may Flyway so popular. These features may be all you ever need. Additional specialized features and technical support are available in further editions at a cost. See this feature grid. The Flyway project and company were recently acquired by Redgate Software, with Flyway creator Axel Fontaine joining.
