Re: Open Source tool to deploy/promote PostgreSQL DDL

Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> 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(at)appl-ecosys(dot)com <mailto:rshepard(at)appl-ecosys(dot)com>> 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