Thread: Open Source tool to deploy/promote PostgreSQL DDL
Hi Community,
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.
Thanks,
Neil Barrett
On 07/10/2018 03:13 PM, Hustler DBA wrote: > Hi Community, > 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 https://sqitch.org/ > normally use scripts, but they want something open source. The above is based on scripts. Will that work for the client or do they want a GUI tool? > > Thanks, > Neil Barrett -- Adrian Klaver adrian.klaver@aklaver.com
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. 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
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@appl-ecosys.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.
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
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@appl-ecosys.com <mailto:rshepard@appl-ecosys.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 > > Hi Neil, Please bottom post, as that is the convention in these lists. This convention allows people to read the history, before reading the reply. Alternatively, you can intersperse your comments if that makes the context easier to follow. You can also omit large chunks that are no longer relevant, replacing them with "[...]". 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! Cheers, Gavin
On 07/10/2018 05:04 PM, 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 > open a github,gitlab,sorceforge repo, switch to postgres and make your opensource
Hustler DBA schrieb am 11.07.2018 um 00:13: > 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. We are using Liquibase (with the XML format) and that has served as well.
On 10/07/18 23:13, Hustler DBA wrote: > Hi Community, > 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. I use Depesz Versioning[1] - simple concept and easy to use, and quite sufficient for my (simple) needs. Ray. [1] https://www.depesz.com/2010/08/22/versioning -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Tue, Jul 10, 2018 at 5:13 PM Hustler DBA <hustlerdba@gmail.com> wrote:
Hi Community,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.Thanks,Neil Barrett
A popular one is ActiveRecord Migrations. It is open source. Although ActiveRecord is also an ORM it’s not required to use it -you can just use SQL. FWIW:
I have built one that I call Mahout (https://github.com/cbbrowne/mahout) which has the merit of involving just two shell scripts, one of which is an auditing tool (pgcmp). It implements a "little language" to indicate dependencies between the SQL scripts that implement the DDL changes. The notable thing that I have seen "downthread" that it does not attempt to implement is "rollback scripts." I find that "necessity" to be a ruby-on-rails dogma that does not seem to fit what I see people doing.
On 07/11/2018 03:21 PM, Christopher Browne wrote: > I have built one that I call Mahout > (https://github.com/cbbrowne/mahout) which has the merit of involving > just two shell scripts, one of which is an auditing tool (pgcmp). > > It implements a "little language" to indicate dependencies between the > SQL scripts that implement the DDL changes. > > The notable thing that I have seen "downthread" that it does not > attempt to implement is "rollback scripts." I find that "necessity" > to be a ruby-on-rails dogma that does not seem to fit what I see > people doing. Where I work, the requirement to have rollback scripts is part of the ITIL requirement for Changes to have a backout procedure. -- Angular momentum makes the world go 'round.
> > Where I work, the requirement to have rollback scripts is part of the ITIL requirement for Changes to have a backout procedure. > Liquibase provides that ability, but IMO rollback for RDBMS is always bit tricky. Certain DDL operations can take long timeif it involves a table rewrite. PG is actually better than others.
On 07/11/2018 03:39 PM, Ravi Krishna wrote: >> Where I work, the requirement to have rollback scripts is part of the ITIL requirement for Changes to have a backout procedure. >> > Liquibase provides that ability, but IMO rollback for RDBMS is always bit tricky. Certain DDL operations can take longtime if it involves > a table rewrite. PG is actually better than others. Yeah, that's true. Sometimes I just dump the whole table, and reload if a rollback is necessary. -- Angular momentum makes the world go 'round.
On Wed, 11 Jul 2018 at 16:37, Ron <ronljohnsonjr@gmail.com> wrote: > > On 07/11/2018 03:21 PM, Christopher Browne wrote: > > I have built one that I call Mahout > > (https://github.com/cbbrowne/mahout) which has the merit of involving > > just two shell scripts, one of which is an auditing tool (pgcmp). > > > > It implements a "little language" to indicate dependencies between the > > SQL scripts that implement the DDL changes. > > > > The notable thing that I have seen "downthread" that it does not > > attempt to implement is "rollback scripts." I find that "necessity" > > to be a ruby-on-rails dogma that does not seem to fit what I see > > people doing. > > Where I work, the requirement to have rollback scripts is part of the ITIL > requirement for Changes to have a backout procedure. ITIL surely does NOT specify the use of database rollback scripts as THE SPECIFIED MECHANISM for a backout procedure. In practice, we tend to take database snapshots using filesystem tools, as that represents a backout procedure that will work regardless of the complexity of an upgrade. It is quite possible for an upgrade script to not be reversible. After all, not all matrices are invertible; there are a surprisingly large number of preconditions that are required for that in linear algebra. And in databases, not all upgrades may be reversed via rollback scripts. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
Gavin Flower <GavinFlower@archidevsys.co.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@appl-ecosys.com <mailto:rshepard@appl-ecosys.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
On 07/11/2018 04:10 PM, Christopher Browne wrote: [snip] > ITIL surely does NOT specify the use of database rollback scripts as > THE SPECIFIED MECHANISM for a backout procedure. > > In practice, we tend to take database snapshots using filesystem > tools, as that represents a backout procedure that will work regardless > of the complexity of an upgrade. > > It is quite possible for an upgrade script to not be reversible. > > After all, not all matrices are invertible; there are a surprisingly large > number of preconditions that are required for that in linear algebra. > > And in databases, not all upgrades may be reversed via rollback scripts. Does "rollback script" truly mean undoing what you just did in a transaction-like manner? -- Angular momentum makes the world go 'round.
On 07/11/2018 04:01 PM, Ron wrote: > On 07/11/2018 04:10 PM, Christopher Browne wrote: > [snip] >> ITIL surely does NOT specify the use of database rollback scripts as >> THE SPECIFIED MECHANISM for a backout procedure. >> >> In practice, we tend to take database snapshots using filesystem >> tools, as that represents a backout procedure that will work regardless >> of the complexity of an upgrade. >> >> It is quite possible for an upgrade script to not be reversible. >> >> After all, not all matrices are invertible; there are a surprisingly >> large >> number of preconditions that are required for that in linear algebra. >> >> And in databases, not all upgrades may be reversed via rollback scripts. > > Does "rollback script" truly mean undoing what you just did in a > transaction-like manner? > Hard to say without knowing the system you are using, but I would guess no. I use Sqitch and it uses the term revert: https://metacpan.org/pod/sqitchtutorial#Status,-Revert,-Log,-Repeat which I think is more accurate. I find it very handy feature when in development mode. Write script --> deploy --> test, if fails --> revert, rewrite deploy script --> deploy and so on. -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Jul 12, 2018 at 5:18 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 07/11/2018 04:01 PM, Ron wrote: >> >> On 07/11/2018 04:10 PM, Christopher Browne wrote: >> [snip] >>> >>> ITIL surely does NOT specify the use of database rollback scripts as >>> THE SPECIFIED MECHANISM for a backout procedure. >>> >>> In practice, we tend to take database snapshots using filesystem >>> tools, as that represents a backout procedure that will work regardless >>> of the complexity of an upgrade. >>> >>> It is quite possible for an upgrade script to not be reversible. >>> >>> After all, not all matrices are invertible; there are a surprisingly >>> large >>> number of preconditions that are required for that in linear algebra. >>> >>> And in databases, not all upgrades may be reversed via rollback scripts. >> >> >> Does "rollback script" truly mean undoing what you just did in a >> transaction-like manner? >> > > Hard to say without knowing the system you are using, but I would guess no. > I use Sqitch and it uses the term revert: > > https://metacpan.org/pod/sqitchtutorial#Status,-Revert,-Log,-Repeat > > which I think is more accurate. I find it very handy feature when in > development mode. Write script --> deploy --> test, if fails --> revert, > rewrite deploy script --> deploy and so on. > And, If you are familiar with git, using Sqitch become easy. It handles dependencies very well. Go with Sqitch. -- Regards, Tirveni Yadav www.bael.io What is this Universe ? From what it arises ? Into what does it go? In freedom it arises, In freedom it rests and into freedom it melts away. Upanishads.