Re: what database schema version management system to use?

On Fri, Apr 08, 2016 at 09:09:22AM -0500, Merlin Moncure wrote:

> I rolled my own in bash. It wasn't that difficult. The basic tactic is to:
>
> *) separate .sql that can be re-applied (views, functions, scratch tables,
> etc) from .sql that can't be re-applied (create table, index, deployment
> data changes etc). I call the former 'soft' and latter 'hard' changes.
> *) keep each database tracked in its own folder in the tree and put all the
> soft stuff there. I keep all the hard stuff in a folder, 'schema'. I also
> ha ve a special library folder which tracks all databases
> *) redeploy 'soft' changes every release. The bash script deploys files in
> mtime order after setting mtime to git commit time since git doesn't track
> mtime
> *) keep a tracking table in each database tracking deployed scripts

GNUmed does pretty much the same thing except we call it
"static" vs "dyamic" changes.

Also, with modern PostgreSQL versions (UPSERT, ON CONFLICT,
IF EXISTS) many items among "index, deployment data changes"
can be turned into soft (dynamic) changes.

We've never had a single bit of patient data get lost among
GNUmed database versions up to the current v21 (but of course
we are paranoid and check md5 sums of the schema before/after
upgrades and run automated data conversion sanity checks
after an upgrade).

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346