Database Reference
In-Depth Information
Auditing DDL changes
This recipe shows how one can collect DDL (Data Definition Language) from database logs in
order to audit changes to the database structure.
Getting ready
Edit your
postgresql.conf
file, and 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
CREATE
,
ALTER
, and
DROP
commands in the log.
postgres@hvost:~$ egrep -i "create|alter|drop" \ /var/log/postgresql/
postgresql-8.4-main.log
If log rotation is in effect, you may need to grep all logs also.
In case the log is too old, and you have not saved the older logs in some other place, you are
out of luck.
The default settings in
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
Make sure to check those if you think you may need logs that are older than seven days.
How it works...
The "Getting ready" part instructs postgreSQL to log all DDL commands in PostgreSQL's
main log.
The
How to do it...
part extracts just the DDL queries from the logfile.
