Auditing DDL changes
This recipe shows you how you can collect 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-9.4-main.log
If log rotation is in effect, you may need to use grep on older logs as well.
If the available logs are too new, and you haven't saved the older logs in some other place, you are out of luck.
The default settings in the postgresql.conf
file for log rotation are as follows:
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age = 1d log_rotation_size = 10MB
Note
Log rotation can also be implemented with third-party utilities. For instance, the...