Thread: Schema versioning in postgres
Hi,
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?
Regards
Abhinav
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?
Regards
Abhinav
talk2abhinav@gmail.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@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.
abhinav mehrotra, 01.02.2011 16:51: > Hi, > > My database schema is susceptible to change between various release of the product. There may be new fields added or oldfields 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? > We are pretty content with Liquibase which handles a lot of this stuff automatically. Regards Thomas