O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required