Open Source tool to deploy/promote PostgreSQL DDL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

Open Source tool to deploy/promote PostgreSQL DDL

Hustler DBA
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
Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Rich Shepard
In reply to this post by Hustler DBA
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

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Hustler DBA
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 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Gavin Flower-2
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
> <[hidden email] <mailto:[hidden email]>> 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


lup
Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

lup
In reply to this post by Hustler DBA


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

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Thomas Kellerer
In reply to this post by Hustler DBA
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.


Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Raymond O'Donnell
In reply to this post by Hustler DBA
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Jeremy Finzel
In reply to this post by Hustler DBA


On Tue, Jul 10, 2018 at 5:13 PM Hustler DBA <[hidden email]> 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: 
Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Christopher Browne-3
In reply to this post by Hustler DBA
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.

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Ron-2
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.

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Ravi Krishna-4
>
> 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.


Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Ron-2
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.

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Christopher Browne-3
In reply to this post by Ron-2
On Wed, 11 Jul 2018 at 16:37, Ron <[hidden email]> 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?"

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Tim Cross
In reply to this post by Gavin Flower-2

Gavin Flower <[hidden email]> 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
>> <[hidden email] <mailto:[hidden email]>> 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

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Ron-2
In reply to this post by Christopher Browne-3
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.

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Open Source tool to deploy/promote PostgreSQL DDL

tirveni yadav
On Thu, Jul 12, 2018 at 5:18 AM, Adrian Klaver
<[hidden email]> 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.

Previous Thread Next Thread