How to design a versioned database with PostgreSQL? I am planning to create a back-end aka an API. The database will be PostgreSQL. I would like to discuss the following scenario:


How can I have multiple versions of the same database? I would like to be able to serve data from an older database structure (v.1) to older clients while serving a newer database structure (v.2) to newer clients. I would like to maintain multiple database versions since I would like to avoid to write workarounds for each version in the surrounding API.


Please leave you architectural thoughts.
Use liquibase (you can write 99% of it in sql now, not xml)
Store liquibase schema in VCS (like git)
branch different versions in git
loop and apply branches to either different database schemas (tenant per schema. you could set schema at top of liquibase file) or loop and apply to different databases (tenant per database, would need to change liquibase db per loop)


see http://msdn.microsoft.com/en-us/library/aa479086.aspx for architectural discussion (applies equally to postgres). shared schema does not apply here.