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
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:
Show quoted text
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]https://www.depesz.com/2010/08/22/versioning - 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:
http://edgeguides.rubyonrails.org/active_record_migrations.html
Show quoted text
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 time if 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 long time 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.