Re: what database schema version management system to use?

On Wed, Apr 6, 2016 at 5:55 AM, Alexey Bashtanov <bashtanov(at)imap(dot)cc> wrote:
> Hi all,
>
> I am searching for a proper database schema version management system.
>
> My criteria are the following:
> 0) Open-source, supports postgresql
> 1) Uses psql to execute changesets (to have no problems with COPY,
> transaction management or sophisticated DDL commands, and to benefit from
> scripting)
> 2) Support repeatable migrations (SQL files that get applied every time
they
> are changed, it is useful for functions or views tracking).
>
> Reasonable?
>
> But unfortunately it looks like each of Liquibase, Flyway, SqlHawk,
> MigrateDB, Schema-evolution-manager, Depesz's Versioning, Alembic and
Sqitch
> does not satisfy some of those, right?
>
> What DB VCS do you use and how does it related with the criteria listed
> above?
> Do you have any idea what other systems to try?

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

Here is example of output:
mmoncure(at)mernix2 09:07 AM (AO_3_9) ~/src/aodb/release/ao$ DRY_RUN=1
./deploy.sh

-----------DEPLOYMENT STARTING--------------

LOG: Dry run requested
LOG: Attempting connection to control database @ host=
rcoylsdbpgr001.realpage.com dbname=ysconfig port=5432
LOG: Got connection host=10.34.232.70 dbname=ysconfig port=5432 to ysconfig
LOG: Got connection host=10.34.232.70 dbname=ysanalysis port=5432 to
ysanalysis
LOG: Got connection host=10.34.232.70 dbname=revenueforecaster port=5432 to
revenue forecaster
LOG: Got connection host=10.34.232.68 dbname=cds2 to node
LOG: Release folder is /home/mmoncure/src/aodb/release/ao/SAT/1.0.0
LOG: Database host=10.34.232.70 dbname=ysconfig port=5432 is getting update
PropertyNicheSchema.sql
LOG: Database host=10.34.232.70 dbname=ysanalysis port=5432 is getting
update PropertyNicheSchema.sql
LOG: Database host=10.34.232.70 dbname=revenueforecaster port=5432 is
getting update PropertyNicheSchema.sql
LOG: Database host=10.34.232.68 dbname=cds2 is getting update
PropertyNicheSchema.sql
LOG: Database host=10.34.232.70 dbname=ysanalysis port=5432 is getting
update ca_scenario_position.sql
LOG: building SAT ysconfig update script
LOG: building SAT ysanalysis update script
LOG: building SAT revenueforecaster update script
LOG: building SAT node update script
LOG: Applying SAT ysconfig update to host=10.34.232.70 dbname=ysconfig
port=5432
LOG: ysconfig successfully updated!
LOG: Applying SAT ysanalysis update to host=10.34.232.70 dbname=ysanalysis
port=5432
LOG: ysanalysis successfully updated!
LOG: Applying SAT revenue forecaster update to host=10.34.232.70
dbname=revenueforecaster port=5432
LOG: revenueforecaster successfully updated!
LOG: Applying SAT node id 0 update to host=10.34.232.68 dbname=cds2
LOG: node 0 successfully updated!
LOG: Applying SAT node id 1 update to host=10.34.232.69 dbname=cds2
LOG: node 1 successfully updated!

LOG: Dry run requested: changes not committed!

merlin