Skip site navigation (1) Skip section navigation (2)

Re: Schema versioning in postgres

From: Chris Browne <cbbrowne(at)acm(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Schema versioning in postgres
Date: 2011-02-01 17:13:29
Message-ID: 87y65zk79i.fsf@cbbrowne.afilias-int.info (view raw or flat)
Thread:
Lists: pgsql-novice
talk2abhinav(at)gmail(dot)com (abhinav mehrotra) writes:
> My database schema is susceptible to change between various release of
> the product. There may be new fields added or old fields removed from
> a table.
> Introduction of new fields seems to simple, but removal/truncated or
> data type does not seem to very direct.
>
> Does postgres has any support for such schema versioning? 

Postgres generally conforms to the SQL standard, which (fairly properly)
does not offer any particular functionality surrounding schema
versioning.

A technique that seems commonly used is to record a version number in
the database, whether within a table, or as the name of a table:

insert into version_info.schema_version (system, version, installed)
  select 'my_system', '2.2.3', now();

On one of my applications, I encode relevant stuff as a series of tables:

cbbapp(at)localhost->  \dt
                                    List of relations
    Schema    |                 Name                 | Type  |           Owner
--------------+--------------------------------------+-------+---------------------------
 _cbbaversion | branch_is_1.0.9-SNAPSHOT             | table | don't_need_to_know_who
 _cbbaversion | generated_on_host_cbbrowne           | table | don't_need_to_know_who
 _cbbaversion | schema_generated_at_2011-01-28 15:28 | table | don't_need_to_know_who
 _cbbaversion | svn_version_7321                     | table | don't_need_to_know_who
(4 rows)

We've "saved the day" a few times by having applications set up to
refuse to start up (e.g. - indicate a FATAL error) if the schema version
found did not match the version that an application expects.

This requires no special functionality from Postgres beyond allowing the
developer to:

 - INSERT new tuples into a version table
 - DROP/CREATE/RENAME tables
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/finances.html
The world's full of apathy, but I don't care. 

In response to

pgsql-novice by date

Next:From: Bruce MomjianDate: 2011-02-01 18:38:34
Subject: Re: Need to enable --with-openssl
Previous:From: abhinav mehrotraDate: 2011-02-01 15:51:23
Subject: Schema versioning in postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group