Thread: Question: How do you manage version control?
Hello,
So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to version control our database functions, table changes, static data etc.
I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy functions.
However, how do you handle dependancies?
We've also used patch files if we need to modify tables - these are typically written to only run once, ie alter table add column x int. Any thoughts on putting this in to a process so that it can be run mutliple times without any issue?
Thanks,
Bryan.
On Sat, Jun 2, 2012 at 1:28 AM, Bryan Montgomery <monty@english.net> wrote:
> Hello,
> So we've been reviewing our processes and working on improving them. One
> area we've been lacking is a procedure to version control our database
> functions, table changes, static data etc.
>
> I'm curious how others do it. Ideally, we want it to be part of our release
> / build process so that we deploy functions.
>
> However, how do you handle dependancies?
>
> We've also used patch files if we need to modify tables - these are
> typically written to only run once, ie alter table add column x int. Any
> thoughts on putting this in to a process so that it can be run mutliple
> times without any issue?
At work, we have perfectly linear database versioning, so I
implemented a simple patchlevel system. The database has a singleton
config table with (among other things) PatchLevel INTEGER NOT NULL,
and every change to the database increments it. We have a single
autopatch script whose logic looks something like this:
level=query("select patchlevel from config");
query("begin");
switch (level)
{
default: explode("Incompatible source code and database, terminating");
case 1:
say("AutoPatch: Adding FooBar column to Quuxification table");
query("alter table quux add FooBar integer not null default 0")
case 2:
say("AutoPatch: Creating accountancy tables");
query("create table ........");
query("create table ....");
//Add new patch levels here
query("update config set patchlevel=3");
case 3:
break;
}
// ... other code here, which will notice if the transaction's broken
query("commit");
This script is safe to run multiple times, and is in fact quite
efficient for that case (as it does just one query and then
terminates). The transaction also protects against code bugs or other
issues, and will not half-way-patch a system.
ChrisA
Bryan Montgomery wrote on 01.06.2012 17:28: > So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to versioncontrol our database functions, table changes, static data etc. > I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy functions. > However, how do you handle dependancies? > We've also used patch files if we need to modify tables - these are typically written to only run once, ie alter tableadd column x int. Any thoughts on putting this in to a process so that it can be run mutliple times without any issue? We have quite good experience with Liquibase to manage the DB scripts Regards Thomas
Hello, am 01.06.2012 um 17:28 schrieb Bryan Montgomery: > So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to versioncontrol our database functions, table changes, static data etc. we use a very basic system since a few years, consisting mainly of shell scripts and sql scripts processed by psql. > I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy functions. > > However, how do you handle dependancies? The code for every recreatable object (i.e. views, functions, maybe types) is stored in its own file. It includes also adrop statement for these objects and formal comments to declare dependencies between the files. The files are processedwith a small script that extracts the dependency declarations and writes a create and drop script for all objectswhile maintaining the correct order. > We've also used patch files if we need to modify tables - these are typically written to only run once, ie alter tableadd column x int. Any thoughts on putting this in to a process so that it can be run mutliple times without any issue? Our database has a "versions" table, containing the version (and date) of applied patch files. Every patch file checks thecurrent version in the database and throws an exception, when it does not match its expected version. The directory with the recreatable objects is versioned along the patch files. Regards Ralf
> -----Ursprungligt meddelande----- > Från: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] För Ralf Schuchardt > Skickat: den 1 juni 2012 18:24 > Till: pgsql-general > Ämne: Re: [GENERAL] Question: How do you manage version control? I was using a very simplistic approach a while back. I kept the DDL to create current version from scratch in one file, the DDL to upgrade from last schema version in another and then a small shell script to run the upgrade. I kept the version number in a table for the shell script to check and abort if schema version didn't match and then update after successful upgrade. All files were kept under version control and if I needed older versions of the upgrade file I just browsed the history and got it from there. Very simple, no special tools and easy to run and understand for all developers (even for the "I won't go near sql"-ones). Working with branches makes it a bit harder since the database patch has to be manually handled when merging. Regards, roppert > > Hello, > > am 01.06.2012 um 17:28 schrieb Bryan Montgomery: > > > So we've been reviewing our processes and working on improving them. > One area we've been lacking is a procedure to version control our database > functions, table changes, static data etc. > > we use a very basic system since a few years, consisting mainly of shell scripts > and sql scripts processed by psql. > > > I'm curious how others do it. Ideally, we want it to be part of our release / > build process so that we deploy functions. > > > > However, how do you handle dependancies? > > The code for every recreatable object (i.e. views, functions, maybe types) is > stored in its own file. It includes also a drop statement for these objects and > formal comments to declare dependencies between the files. The files are > processed with a small script that extracts the dependency declarations and > writes a create and drop script for all objects while maintaining the correct > order. > > > We've also used patch files if we need to modify tables - these are typically > written to only run once, ie alter table add column x int. Any thoughts on > putting this in to a process so that it can be run mutliple times without any > issue? > > Our database has a "versions" table, containing the version (and date) of > applied patch files. Every patch file checks the current version in the > database and throws an exception, when it does not match its expected > version. > The directory with the recreatable objects is versioned along the patch files. > > > Regards > Ralf > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Fri, Jun 1, 2012 at 8:28 AM, Bryan Montgomery <monty@english.net> wrote: > Hello, > So we've been reviewing our processes and working on improving them. One > area we've been lacking is a procedure to version control our database > functions, table changes, static data etc. > > I'm curious how others do it. Ideally, we want it to be part of our release > / build process so that we deploy functions. We use subversion. The main schema and static data is in one file. The procedures split into modules. When a new version is rolled out, the schema and static data is not touched, and the procedure modules are all reloaded in a particular order specified in a text file (we call that file LOADORDER). The final module to run is named Fixes.sql and more on that below. Fixes is basically the cumulative set of database patches so we are guaranteed to get into a specific state from any prior version. > > We've also used patch files if we need to modify tables - these are > typically written to only run once, ie alter table add column x int. Any > thoughts on putting this in to a process so that it can be run mutliple > times without any issue? Sure. This does add some errors to database logs but with some parameters I think it's possible. The key thing is that every patch will run in order on every upgrade. So any patches that fail because they have already been run we expect to fail. Each patch runs in its own transaction. Patches may change static information but they need to do it in a way that is multi-run safe. "update foo set bar = bar + 1" is not a good thing to put in a patch of this sort. Every patch MUST either fail and roll back if already run or have no impact if already run. This does add some errors into the log files, however it also guarantees a consistent end-point regardless of where you begin along the prior versions. Best Wishes, Chris Travers
So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to version control our database functions, table changes, static data etc I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy functions. A true end to end Database Change Management best practice process should include two aspects of database development; the act of version control and the act of deployment and these steps must be in sync with each other. Just make sure that your DCM process meets all the necessary best practice requirements for DCM: 1. Change Policy Enforcement – every change is documented. Locking mechanism doesn’t allow for any object changes to be made outside of the check in /check out. 2. End to end change management solution that enables the deploy phase to connect to the change history. 3. All of the 3 database code types are managed and controlled. a. Schema structure b. Business Logic c. Lookup or reference date Read more here about DCM http://bit.ly/KE7n9A http://bit.ly/KE7n9A Robert Gravsjö wrote > >> -----Ursprungligt meddelande----- >> Från: pgsql-general-owner@ [mailto:pgsql-general- >> owner@] För Ralf Schuchardt >> Skickat: den 1 juni 2012 18:24 >> Till: pgsql-general >> Ämne: Re: [GENERAL] Question: How do you manage version control? > > I was using a very simplistic approach a while back. I kept the DDL to > create current version from scratch in one file, > the DDL to upgrade from last schema version in another and then a small > shell script to run the upgrade. > > I kept the version number in a table for the shell script to check and > abort if schema version didn't match and then > update after successful upgrade. > > All files were kept under version control and if I needed older versions > of the upgrade file I just browsed the > history and got it from there. > > Very simple, no special tools and easy to run and understand for all > developers (even for the "I won't go near sql"-ones). > > Working with branches makes it a bit harder since the database patch has > to be manually handled when > merging. > > Regards, > roppert > >> >> Hello, >> >> am 01.06.2012 um 17:28 schrieb Bryan Montgomery: >> >> > So we've been reviewing our processes and working on improving them. >> One area we've been lacking is a procedure to version control our >> database >> functions, table changes, static data etc. >> >> we use a very basic system since a few years, consisting mainly of shell >> scripts >> and sql scripts processed by psql. >> >> > I'm curious how others do it. Ideally, we want it to be part of our >> release / >> build process so that we deploy functions. >> > >> > However, how do you handle dependancies? >> >> The code for every recreatable object (i.e. views, functions, maybe >> types) is >> stored in its own file. It includes also a drop statement for these >> objects and >> formal comments to declare dependencies between the files. The files are >> processed with a small script that extracts the dependency declarations >> and >> writes a create and drop script for all objects while maintaining the >> correct >> order. >> >> > We've also used patch files if we need to modify tables - these are >> typically >> written to only run once, ie alter table add column x int. Any thoughts >> on >> putting this in to a process so that it can be run mutliple times without >> any >> issue? >> >> Our database has a "versions" table, containing the version (and date) of >> applied patch files. Every patch file checks the current version in the >> database and throws an exception, when it does not match its expected >> version. >> The directory with the recreatable objects is versioned along the patch >> files. >> >> >> Regards >> Ralf >> -- >> Sent via pgsql-general mailing list (pgsql-general@) To make >> changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > -- > Sent via pgsql-general mailing list (pgsql-general@) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- View this message in context: http://postgresql.1045698.n5.nabble.com/Question-How-do-you-manage-version-control-tp5710978p5711121.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.