Обсуждение: Stored procedure version control
Elsewhere, somebody was asking how people implemented version control for stored procedures on (MS) SQL Server. The consensus was that this is probably best managed by using scripts or command files to generate stored procedures etc., but does anybody have any comment on that from the POV of PostgreSQL? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
On 06/29/2016 09:37 AM, Mark Morgan Lloyd wrote: > Elsewhere, somebody was asking how people implemented version control > for stored procedures on (MS) SQL Server. > > The consensus was that this is probably best managed by using scripts or > command files to generate stored procedures etc., but does anybody have > any comment on that from the POV of PostgreSQL? There is no mechanism internal to Postgres that will version control the procedures, so the answer will be the same as above. In other words some external mechanism to version control. A more complete answer will depend on the workflow you are currently using. -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Jun 29, 2016 at 12:00 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 06/29/2016 09:37 AM, Mark Morgan Lloyd wrote: >> >> Elsewhere, somebody was asking how people implemented version control >> for stored procedures on (MS) SQL Server. >> >> The consensus was that this is probably best managed by using scripts or >> command files to generate stored procedures etc., but does anybody have >> any comment on that from the POV of PostgreSQL? > > > There is no mechanism internal to Postgres that will version control the > procedures, so the answer will be the same as above. In other words some > external mechanism to version control. A more complete answer will depend on > the workflow you are currently using. I like this: https://github.com/depesz/Versioning very simple and easy to use.
>does anybody have any comment on that from the POV of PostgreSQL? Might be overkill but you could deploy your procedure as an extension because extensions come with version control: https://www.postgresql.org/docs/current/static/sql-createextension.html Another option might be to hack something together using COMMENT ON FUNCTION [...]: https://www.postgresql.org/docs/current/static/sql-comment.html Kind regards Peter
On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote: > Elsewhere, somebody was asking how people implemented version control > for stored procedures on (MS) SQL Server. > > The consensus was that this is probably best managed by using scripts or > command files to generate stored procedures etc., but does anybody have > any comment on that from the POV of PostgreSQL? > I can't comment from the POV of those who represent Postgres, but I used to work for a company who specialised in change management for database products, SQL Server and Oracle in particular. There are at least two approaches. The migrations approach and the state based approach. For migrations you create up and down scripts/code fragments to move the database through versions over time, committing them to a source control system as you go. Usually the database will contain some tables to keep track of the current live version. With the state based approach you just store the DDL for each object in the source control system. You can see how an object changes over time by just inspecting one file. You can automate the scripting process or use one of the diffing tools that supports comparing to DDL directly. State based handles merge conflicts better than migrations. Migrations handles data changes better than state based. Migrations also is better if you are deploying to multiple production databases that may all be on different versions. If your database contains a lot of logic or you have a large distributed team you are more likely to have merge issues and so state based is probably the better choice. Smaller team, less logic and a production environment where you need to be able to update from any version reliably? Migrations is a good choice. Additionally you don't have to stick with one or the other. In the early days while you have little data to worry about you might use the static approach and then switch to migrations. You just pick a baseline to start from and carry on from there. -- Neil Anderson neil@postgrescompare.com http://blog.postgrescompare.com
On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote: > Elsewhere, somebody was asking how people implemented version control > for stored procedures on (MS) SQL Server. > > The consensus was that this is probably best managed by using scripts or > command files to generate stored procedures etc., but does anybody have > any comment on that from the POV of PostgreSQL? > I can't comment from the POV of those who represent Postgres, but I used to work for a company who specialised in change management for database products, SQL Server and Oracle in particular. There are at least two approaches. The migrations approach and the state based approach. For migrations you create up and down scripts/code fragments to move the database through versions over time, committing them to a source control system as you go. Usually the database will contain some tables to keep track of the current live version. With the state based approach you just store the DDL for each object in the source control system. You can see how an object changes over time by just inspecting one file. You can automate the scripting process or use one of the diffing tools that supports comparing to DDL directly. State based handles merge conflicts better than migrations. Migrations handles data changes better than state based. Migrations also is better if you are deploying to multiple production databases that may all be on different versions. If your database contains a lot of logic or you have a large distributed team you are more likely to have merge issues and so state based is probably the better choice. Smaller team, less logic and a production environment where you need to be able to update from any version reliably? Migrations is a good choice. Additionally you don't have to stick with one or the other. In the early days while you have little data to worry about you might use the static approach and then switch to migrations. You just pick a baseline to start from and carry on from there. -- Neil Anderson neil@postgrescompare.com http://blog.postgrescompare.com
Neil Anderson wrote: > On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote: >> Elsewhere, somebody was asking how people implemented version control >> for stored procedures on (MS) SQL Server. >> >> The consensus was that this is probably best managed by using scripts or >> command files to generate stored procedures etc., but does anybody have >> any comment on that from the POV of PostgreSQL? >> > > I can't comment from the POV of those who represent Postgres, but I used > to work for a company who specialised in change management for database > products, SQL Server and Oracle in particular. There are at least two > approaches. The migrations approach and the state based approach. [etc.] Thanks everybody, summary passed on. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
-----Original Message-----
>From: Mark Morgan Lloyd Sent: Thursday, June 30, 2016 2:41 AM
>Neil Anderson wrote:
>> On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote:
>>> Elsewhere, somebody was asking how people implemented version control
>>> for stored procedures on (MS) SQL Server.
>>>
>>> The consensus was that this is probably best managed by using scripts
>>> or command files to generate stored procedures etc., but does anybody
>>> have any comment on that from the POV of PostgreSQL?
>>>
> [etc.] Thanks everybody, summary passed on.
>Mark Morgan Lloyd
>markMLl .AT. telemetry.co .DOT. uk
A bit late to the thread, but here's some specific details on how I've
implemented version control in PG 9.5, in a small team environment deploying
to single database servers in each tier (dev, qa, stage, prod). It's
working well so far, and allows my stored proc versions to be aligned with
the middle and upper tier code releases. I'm the lead database
architect-engineer for a brand new genomics application (lots of data).
Details:
- we're using git for version control, with a base name for each repo that
holds a single micro-service (like "JobManager") and a suffix for the data
tier code ("JobManagerDBMS") making it simple for devops to find the related
code for a micro-service deployment by repo.
- within a DBMS repo, I've got subfolders like "scripts", "sprocs",
"documentation", where scripts holds ad hoc scripts that need to be run
during a deployment (might be adjusting DDL or seeding or cleaning up data),
sprocs for stored function files that must be compiled into PG, and
documentation holds notes, data models, etc. We have a simple python script
that compiles/recompiles all stored proc files within a named folder -
deployment done with one call.
- I only code using source code files, by cloning an existing suitable base
stored proc (akin to a template) to a new file name (like a "get" or "set"
stored proc) and then revising to match the requirement. In a detailed
comment block within each stored proc, I list a version number (just for
reference, not used programmatically at this point), change history, author,
comments, and one or more sample calls that form the basis of my unit tests.
- after I've finished the requested work and the stored procs are working
as expected, I update a Version file in the folder, push it into git and
merge it into the development branch.
- let's say a new enhancement request comes in. I create a new branch
(like "RequestForNewThingy"), version that, do the work and merge it back in
just like the above. So we've got isolation and persistence of changes.
- I happen to be using the new DataGrip code editor, which supports this
beautifully, since my git tree appears on the right side of editor window,
allowing me directly edit/clone without leaving the editor.
My coding efficiency using this model is quite high...the overhead of using
git is trivial.
For rollbacks, we can simply point to the prior stored proc version and
recompile those. For DDL rollbacks, I have to code those scripts and supply
them...this is the one place I have to spend a bit more time creating a more
automated solution. I'd love to hear how other folks have solved
programmatic rollbacks.
Mike Sofen (San Diego, CA USA)
Mike Sofen wrote: > -----Original Message----- >> From: Mark Morgan Lloyd Sent: Thursday, June 30, 2016 2:41 AM >> Neil Anderson wrote: >>> On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote: >>>> Elsewhere, somebody was asking how people implemented version control >>>> for stored procedures on (MS) SQL Server. >>>> >>>> The consensus was that this is probably best managed by using scripts >>>> or command files to generate stored procedures etc., but does anybody >>>> have any comment on that from the POV of PostgreSQL? >>>> > >> [etc.] Thanks everybody, summary passed on. >> Mark Morgan Lloyd >> markMLl .AT. telemetry.co .DOT. uk > > A bit late to the thread, but here's some specific details on how I've > implemented version control in PG 9.5, in a small team environment deploying > to single database servers in each tier (dev, qa, stage, prod). It's > working well so far, and allows my stored proc versions to be aligned with > the middle and upper tier code releases. I'm the lead database > architect-engineer for a brand new genomics application (lots of data). Thanks Mike, I'll pass that on if the thread on CIX still looks live. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
On Wed, Jun 29, 2016 at 1:46 PM, Neil Anderson <neil@postgrescompare.com> wrote: > On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote: >> >> Elsewhere, somebody was asking how people implemented version control >> for stored procedures on (MS) SQL Server. >> >> The consensus was that this is probably best managed by using scripts or >> command files to generate stored procedures etc., but does anybody have >> any comment on that from the POV of PostgreSQL? >> > > I can't comment from the POV of those who represent Postgres, but I used to > work for a company who specialised in change management for database > products, SQL Server and Oracle in particular. There are at least two > approaches. The migrations approach and the state based approach. > > For migrations you create up and down scripts/code fragments to move the > database through versions over time, committing them to a source control > system as you go. Usually the database will contain some tables to keep > track of the current live version. > > With the state based approach you just store the DDL for each object in the > source control system. You can see how an object changes over time by just > inspecting one file. You can automate the scripting process or use one of > the diffing tools that supports comparing to DDL directly. > > State based handles merge conflicts better than migrations. Migrations > handles data changes better than state based. Migrations also is better if > you are deploying to multiple production databases that may all be on > different versions. > > If your database contains a lot of logic or you have a large distributed > team you are more likely to have merge issues and so state based is probably > the better choice. Smaller team, less logic and a production environment > where you need to be able to update from any version reliably? Migrations is > a good choice. > > Additionally you don't have to stick with one or the other. In the early > days while you have little data to worry about you might use the static > approach and then switch to migrations. You just pick a baseline to start > from and carry on from there. This is an excellent summary. I personally think the 'migrations' based approach (as you describe it) is a better approach for large teams and complex environments. A good migration script will redeploy functions and views from source without having to be instructed to do so by development. State migrations are good for simple cases, particularly when the level of database expertise on the team is low. A lot of times teams doing this tend to not even bother checking database scripts into SCM, a huge long term mistake IMO. It's not really necessary to create version down scripts. In five years of managing complex database environments we've never had to roll a version back and likely never will; in the event of a disaster it's probably better to restore from backup anyways. merlin
On 6/30/16 9:16 AM, Merlin Moncure wrote: > It's not really necessary to create version down scripts. In five > years of managing complex database environments we've never had to > roll a version back and likely never will; in the event of a disaster > it's probably better to restore from backup anyways. I'm surprised no one has mentioned http://sqitch.org. It makes it very easy to manage migrations, as well as creating downgrade scripts (if you use rework, and put each object into it's own file). I do agree that down scripts are pretty over-rated as long as you have good test practices (as in, database unit tests). In 9 years in an environment where downtime was 6 figures per hour I only had 1 or 2 deployments that had problems, and never bad enough to consider reverting. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
On 07/01/2016 06:17 PM, Jim Nasby wrote: > On 6/30/16 9:16 AM, Merlin Moncure wrote: >> It's not really necessary to create version down scripts. In five >> years of managing complex database environments we've never had to >> roll a version back and likely never will; in the event of a disaster >> it's probably better to restore from backup anyways. > > I'm surprised no one has mentioned http://sqitch.org. It makes it very > easy to manage migrations, as well as creating downgrade scripts (if > you use rework, and put each object into it's own file). > > I do agree that down scripts are pretty over-rated as long as you have > good test practices (as in, database unit tests). In 9 years in an > environment where downtime was 6 figures per hour I only had 1 or 2 > deployments that had problems, and never bad enough to consider > reverting. I've found down scripts quite useful in development. One does have to know how to neuter them on the way to production however.
On Thu, Jun 30, 2016 at 09:16:49AM -0500, Merlin Moncure wrote: > It's not really necessary to create version down scripts. In five > years of managing complex database environments we've never had to > roll a version back and likely never will; in the event of a disaster > it's probably better to restore from backup anyways. Also, a very robust approach to rollback is to clone the existing databse before upgrading the schema (if feasible due to size). This is the approach GNUmed uses - migration scripts are up only, as many of them as possible are idempotent, and we clone the existing database into a new one before the upgrade is run. That way, users can go back to the previous database immediately anytime and reattempt the upgrade when convenient. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Mark Morgan Lloyd wrote: > Elsewhere, somebody was asking how people implemented version control > for stored procedures on (MS) SQL Server. > > The consensus was that this is probably best managed by using scripts or > command files to generate stored procedures etc., but does anybody have > any comment on that from the POV of PostgreSQL? For completeness although this appears to be MS-specific, somebody has drawn my attention to http://www.red-gate.com/products/sql-development/sql-source-control/ -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]