On Wed, Apr 6, 2016 at 5:55 AM, Alexey Bashtanov <bashta...@imap.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@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
