Re: How to store version number of database layout

On Tue, Feb 12, 2013 at 2:40 AM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 2/12/2013 2:01 AM, Frank Lanitz wrote:
>
>> t's more like a question of best practice:
>> How do you managing different version of database layout for e.g.
>> software releases?
>> We are planing to do an application based on postgres and want to store
>> some version number of database layout to ensure correct upgrade-scripts
>> can be applied in case of an upgrade. Is there any build in for?
>>
> my projects, we have a small table "settings", with key text, value
> text. one row is 'SCHEMA_VERSION','1.0.5'

this is the approach I go with too. What we also do is have all the
modules (.sql files) designed to be reloadable in a specific order and
guaranteeing a stored procedure layout consistent with the schema version.
We also have extensive db-level test cases which can be run on production
servers (all transactions roll back) so that we can check for
inconsistencies.

This is not infallible but we have had relatively few problems with it.

Best Wishes,
Chris Travers