How to enable statement history in PostgreSQL

Published Wednesday, May 15, 2019

I found myself in the need to check a portion of the SQL statements on my PostgreSQL servers. The process is easy and quick but you have to be sure to disable this feature as it writes directly on the PostgreSQL log and if you leave this enabled you might get your hard drive full.

First off locate your postgresql.conf file:

$ find / -name postgresql.conf

Then open it with nano and activate the log_statement flag with the all option. In my case, the file was located under /etc/postgresql/10/main/.

$ sudo nano /etc/postgresql/10/main/postgresql.conf log_statement 'all'

Save the file and restart the postgresql server:

$ sudo service postgresql restart

Finally, if you want to see the log being written down on your console simply pass the entry of the file to the tail command like this:

$ tail -f /var/log/postgresql/postgresql-10-main.log

You are good to go. You will now be able to watch read the full history of statements executed on your PostgreSQL server.

Remember to undo this to avoid your log file to be full out of the history.

Topics: development, postgre, backend

Nelson Medina

Written by Nelson Medina

My main skills go around Web Development environment. From Vanilla PHP, passing through Laravel 5.6, Symfony 4.1 and Wordpress as main frameworks to work. On the other hand I have skills with vanilla JavaScript aswell as AngularJS (1.1.13), Backbone, NodeJS and Vue.js. Git-SCM is my tool for having track of my work on projects. MySQL and Mongo DB are no ocult sorcery to me ;). Plus, I have basic experience on Linux Servers administration and environments thus I am not afraid of using a command line. Despite I am not a Graphic Designer I know well how to use Photoshop or GIMP, as well as CSS and SASS as styling language for HTML and Web Applications.

Leave a Comment