How to transfer DDL changes between different stages?

Question

What technical solutions are available to track DDL changes and transfer them between different stages (e.g. DEV -> QA -> PROD) on postgreSQL databases? Is there a common mechanism to do that?

Answer 1

There are some. The most reliable I've seen so far (at least when the development process is also have to be considered) is having diffs.

Basically, all DB objects (tables, types, functions, etc.) are defined one by one in files, which are then under version control. When you want to change something, you write a diff (ALTER TABLE, for example) and change the affected definition files, too. The changed files and the diff itself have to be checked in, of course. Then you can roll out these diffs on the different stages, each time registering the diff with its unique name (which might be a ticket number with something else).

All this does not really need special software, your favourite DB client, the usual version control system and some small mechanism of registering the diffs is enough. For the latter, you can use depesz's versioning schema.

Answer 2

There are many tools (just google for 'database migration tool') that support the use case above. See also that wikipedia article about Schema Mirgation.