Version Control Software for Database Objects

On Thu, 2005-01-13 at 15:18 -0800, Mark Dexter wrote:

We maintain multiple versions of our application's database and we are
looking for version control software to help us automate this.
Specifically, we would like to have a program that automatically
tracks all changes to the database (tables, views, functions, etc.)
and assists with updating customers' databases from one version to the
next.

Does anyone know of such a program that works with PostgreSQL? Thanks
for your help.

Perhaps Point in Time Recovery can help you:

<http://developer.postgresql.org/docs/postgres/backup-online.html&gt;

It's a new feature in 8.0.

You might also want to look into some of the available replication
solutions.

Regards,
Jeff Davis

Mark Dexter wrote:

We maintain multiple versions of our application's database and we are
looking for version control software to help us automate this.
Specifically, we would like to have a program that automatically tracks
all changes to the database (tables, views, functions, etc.) and assists
with updating customers' databases from one version to the next.

Does anyone know of such a program that works with PostgreSQL? Thanks
for your help.

Assuming you mean version control of the _schema_, rather than the data
therein, then no, I don't know of any such program available. However,
it's not too hard to do it yourself, which is what we've done. Create a
table which has only one row, containing a schema version number, and
build some constant into your application which has the same number -
the application should check the database schema number on startup and
complain loudly if the two don't match. Every time you release a new
version of the application that has a schema change, then both increment
the schema number and write a script that will perform the schema update
from version n to version n + 1. If you name your scripts in a
consistent way, it's not hard to then write a script that compares the
current schema version at a site with that expected by the new
application version, and incrementally runs each update script.

Tim

--
-----------------------------------------------
Tim Allen tim@proximity.com.au
Proximity Pty Ltd http://www.proximity.com.au/
http://www4.tpg.com.au/users/rita_tim/

Tim Allen <tim@proximity.com.au> writes:

Mark Dexter wrote:

We maintain multiple versions of our application's database and we are
looking for version control software to help us automate this. Specifically,
we would like to have a program that automatically tracks all changes to the
database (tables, views, functions, etc.) and assists with updating
customers' databases from one version to the next.
Does anyone know of such a program that works with PostgreSQL? Thanks for
your help.

I do a pretty crude version of this manually. I do pg_dump -s which dumps out
the SQL for the schema and check that into CVS. I do this after any set of
database changes and add comments about what I've modified. (With 7.4 and
prior you have to filter out some lines that always change. But in 8.0 the
output looks to be cleaned up a lot.)

This gives me a record of the changes. But it doesn't really help migrate the
changes to another server. Well I guess it helps in that it gives me something
to go on. But it certainly doesn't do it for me.

I'm pretty skeptical about tools that do this stuff automatically. But I know
such tools exist. I don't know if any support Postgres though. You might check
out Toad and ERwin. Postgres is advancing so quickly I suspect none of the
commercial packages will be up to date though.

--
greg

PG Lightning Admin has version control(with a diff viewer) for functions
built in.
It wouldn't be that difficult to add other objects after I release 1.0

Here is a screen shot:

http://www.amsoftwaredesign.com/downloads/pg_screenshots/function_version_control.PNG

It creates a table in the public schema to hold the version information
and it keeps track of who did what by pulling in the userid from the OS,
not from PG.
On a NT domain or active dir it will also look up the full name of the user.

I will be releasing the program as shareware for 29.99 ( a pretty good deal)
It also has a function editor and query editor with code completion and
param hinting for built in as well as user created functions.
Here are some more screen shots:
http://www.amsoftwaredesign.com/pg_ss.asp.asp

You could also beta test and receive it for free :-) Let me know if you
would like to participate.

Thanks,

Tony Caduto
AM Software Design
Milwaukee WI.
http://www.amsoftwaredesign.com