Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> writes: > On 11/07/18 11:04, Hustler DBA wrote: >> Thanks Adrian and Rich, >> I will propose sqitch to the client, but I think they want something >> with a GUI frontend. >> >> They want to deploy database changes, track which environments the >> change was deployed to, be able to rollback a change (with a rollback >> script), track when and if the change was rolled back and in which >> environment/database... so pretty much a deployment and tracking GUI >> software with a frontend. >> >> In the past, for doing database deployments to Oracle, I created a >> tool using PHP (frontend/backend), MySQL (repository to track >> deployments and store deployment logs) and scripted the release >> scripts for deployment and rollback, and had my tool manage the >> scripts up the environments. The client is "looking" for something >> more open source for PostgreSQL. Do we have anything similar to this? >> >> Neil >> >> On Tue, Jul 10, 2018 at 6:22 PM, Rich Shepard >> <rshepard@xxxxxxxxxxxxxxx <mailto:rshepard@xxxxxxxxxxxxxxx>> wrote: >> >> On Tue, 10 Jul 2018, Hustler DBA wrote: >> >> A client of mine is looking for an open source tool to deploy >> and promote >> PostgreSQL DDL changes through database environments as part >> of SDLC. What >> tools (open source) does the community members use? I normally use >> scripts, but they want something open source. >> >> >> Neil, >> >> I'm far from a professional DBA, but scripts are certainly open >> source >> because they're text files. >> > Being text files has nothing to do with being Open Source! As I could > send you a BASH script, or Java source code of a program, where they are > under a Proprietary licence. > > On the other hand, being a script could be open source, it all depends > on the licence! > >> >> To track changes for almoste everything I highly recommend Git >> for version >> control. It's distributed and can handle most types of files. I >> use it for >> tracking coding projects and well as report and other text >> documents that >> are edited and revised prior to release. >> >> Rich >> >> > > Note that using scripts makes it easier to automate and to document, > plus it gives you far more control. With PostgreSQL I use psql, as it > is easier to use than any GUI tool. I use an editer to create SQL > scripts and execute them from psql. Note that you can use psql to > execute SQL from within a BASH script. > > Scripts once working and tested, can be reused and stored in git. This > is not something you can do with actions in a GUI! > +1 for using scripts. I've tried various 'fancy' deployment tools and always found them lacking. I prefer to have deployment/migration scripts as part of the GIT repo which has all the rest of the DDL/DML for the system. Check out the version from GIT you want to deploy, run the scripts and your done. To track deployments, have a version table in your target which your scripts update on completion of the deployment. Other scripts can then query this table to determine which version has been deployed. I typically don't worry about rollback as part of the scripts. This tends to just make deployments far more complicated than necessary (and therefore more error prone). Instead, ensure you have an effective backup restore process and ensure that backup is part of the deployment task. Rollback capability in deployment scripts or programs is rarely of significant benefit because it is highly dependent on the type of changes being made (so needs to be developed as part of the specific deployment) and is typically only possible for a very short time following deployment (usually shorter than the time required to make the decision to rollback). I've seen environments where rollback in the scripts 8is mandatory and as a consequence, deployment of the rollback components takes nearly as much time as development of the feature/change being deployed. I suspect maintaining a GUI for such systems is probably more complex than the benefits it realises. While I guess it would be possible to have a web based interface, sorting out and maintaining access permissions without compromising security will likely take more time than the benefits a GUI offers. Management often likes the idea of a GUI as they think it means the deployments can then be performed by less skilled (and cheaper) staff. Reality is, you probably want your more skilled and experienced staff dealing with deployments and they will typically prefer the flexibility of scripts over the constraints of a GUI. Given the high level of variability in environments, you are probably best off developing the process and scripts rather than trying to find an existing tool. Putting a web front end is likely easier than finding a tool flexible enough to fit with the environment which avoids situations where the tool begins to dictate how you operate (tail wagging the dog). Tim -- Tim Cross
