Auditing DDL changes

This recipe shows you how you can collect the data definition language (DDL) from database logs in order to audit changes to the database structure.

Getting ready

Edit your postgresql.conf file to set the following:

log_statement = 'ddl'

Setting it to mod or all is also OK for this. Don't forget to reload the configuration:

/etc/init.d/postgresql reload

How to do it...

Now find all occurrences of the CREATE, ALTER, and DROP commands in the log:

postgres@hvost:~$ egrep -i “create|alter|drop” \   /var/log/postgresql/postgresql...