On Wed, Apr 6, 2016 at 5:55 AM, Alexey Bashtanov <bashtanov@xxxxxxx> 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?
> 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@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
