database versioning

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.

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/