Question: How do you manage version control?

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 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?

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 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

-----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

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.

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