For many of my tables, I've added auditing triggers (based on this wiki page). It works very well and has helped me several times figure out who changed what in the system. We have a Python/Django application that sits on top of the data and that code is tracked in Git. However, there seems to be one area of our system where changes are not tracked very well. And that's the function/triggers, etc in PostgreSQL. I wish there was a way I could add a similar audit capability to the schema as I have with the data itself.

How do DBA track these changes? Note: I'm in a position where there is more than one person with sufficient privileges to "CREATE OR REPLACE" a function, so I can't necessarily count on a person to write a script and check it into Git and I certainly can't force them. If possible, it needs to be automatic.

I've thought about writing a python script to "create" a script file and write it to a file and then programmatically commit changes to a git repo.

Another option would be to add some tables to my audit schema and just query each night and look for changes and right them to a table. Certainly could work, but not quite as nice as being able to do a diff/blame via git.

This may not be the greatest question because I'm not exactly sure what I'm looking for and there may not be an exact "right" answer, but I would like to know what people do. I've done some "googling" on the topic, but I'm not finding a lot (maybe I'm just using the wrong terms).

Finally, I'm currently using PostgreSQL 9.1.

2 Answers 2


Right now there isn't really a single wonderful option for this.

You need to make sure people use revision control properly and consistently.

Some after-the-fact checks can be used to query for unexpected procedure changes and alert if the pg_proc.prosrc column doesn't match what you have in revision control.

If necessary you can force all changes through a single person's approval. Alternately you can deny access to create and update procedures to all accounts except one - and have that account automatically apply changes that're committed to git via a hook. I don't recommend this though - it'll make operations very restrictive and make it hard to fix unexpected issues.

  • Appreciate the answer. Yeah, I'm afraid you are correct. There is not a single solution. :\ Unfortunately the company I work for uses Heroku for hosting it's PostgreSQL instances. So, there's only one user/password. And you can't create a ROLE (which is complete worthless in my opinion). So, every developer has the right to create/change a function. I can scream/preach all I want that they need to create a script and add it to version control and track it, but as you can imagine, that often gets ignored and I have no idea who changed something.
    – David S
    Commented Jun 6, 2013 at 3:28
  • @DavidS Hopefully PostgreSQL 9.4 will have full event trigger support, allowing you to create triggers for things like function creation/replacement. It might be possible in 9.3 (soon to be released) if you can install a custom compiled C extension, but that won't do you any good if you're on Heroku. Commented Jun 6, 2013 at 3:45

A bit late but, you could script out the functions on a period basis and then commit to git as an automated process. That way you could at least track changes, and have the ability to find older implementations that have been overwritten in the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.