database versioning

Question

I work as a scm developer and I am currently tasked with a activity to which involves the database versioning. Although I have done source code management I am quite new to this. Hence I would like to have different views and experience on how to implement this.

What I mean by database(oracle/sybase) version is to capture the changes which happens to the database schema/triggers/etc and store it as revisions. Basically in our company there are some changes in the customer databases which we are not aware of or at least not able to identify when and who made a particular change. We are just trying to create a record of the changes which happens in the DB.

Note: I am not a DB guy.

Answer 1

The usual practice is to allow changes to go through a build process. Basically.. have a version control tool like CVS where users check in the changes that have to to go to the QA and Prod environments.

So.. let's say, there are a couple of columns added to a table, the developer would check in a .ddl script with the "Alter table ..." command and that will be "applied" to the database the next time you do a build.

Unless you restrict users (in this case.. Developers) from directly making changes and instead use a standard build-process, tracking changes to objects is almost impossible over time.

Consider necessary details like the user who made the change, Time of change, reason (Check-in comments, bug Number, new feature request etc) which you'd need later to understand why a change was made. All the changes are usually compiled using a standard user like "APPOWNER" and in the absence of a version control system, you only have access to the latest change (last_ddl_change ).

If your concern is to track changes to Data, you can use triggers or use an application like Golden Gate that will read through the redo-logs and get you the change capture records. From your Question, it looks like you are looking for a way to track object changes.

Answer 2

The best way to do it is to have some kind of db revision software which manages all changes and allows to easily apply it to multiple databases (up/downgrade).

It requires to save all changes to revision software, no direct db changes.

Maybe similar tools for PostgreSQL will help:

• depesz scripts http://www.depesz.com/index.php/projects/.

• Python tool: https://code.google.com/p/sqlalchemy-migrate/