How do log ALL PostgreSQL SQL happening

20 July 2015   0 comments   MacOSX, PostgreSQL

When you're using PostgreSQL for local development it's sometimes important to get an insight into ALL SQL that happens on the PostgreSQL server. Especially if you're trying to debug all the transaction action too.

To do this on OSX where you have PostgreSQL installed with Homebrew you have to do the following:

1. Locate the right postgresql.conf file. On my computer this is in /opt/boxen/homebrew/var/postgres/ but that might vary depending on how you set up Homebrew. Another easier way is to just start psql and ask there:

$ psql
psql (9.4.0)
Type "help" for help.

peterbe=# show config_file;
                   config_file
--------------------------------------------------
 /opt/boxen/homebrew/var/postgres/postgresql.conf
(1 row)

peterbe=#

Open that file in your favorite editor.

2. Look for a line that looks like this:

#log_statement = 'all'                   # none, ddl, mod, all

Uncomment that line.

3. Now if you can't remember how to restart PostgreSQL on your system you can ask brew:

$ brew info postgresql

Towards the end you'll see some files that look something like this:

$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

4. To locate where PostgreSQL dumps all logging, you have to look to how Homebrew set it up. You can do that by opening ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist. You'll find something like this:

  StandardErrorPath
  /opt/boxen/homebrew/var/postgres/server.log

That's it. You can now see all SQL going on by running:

$ tail -f /opt/boxen/homebrew/var/postgres/server.log

Remember to reverse this config change when you're done debugging because that server.log file can quickly grow to an insane size since it's probably not log rotated.

Happy SQL debuggin'!

Comments

Thank you for posting a comment

Your email will never ever be published


Related posts

Previous:
Visual speed comparison of AngularJS and ReactJS 20 July 2015
Next:
Some tips on learning React 04 August 2015
Related by Keyword:
When Docker is too slow, use your host 11 January 2018
Ctags in Atom on OSX 26 February 2016
Shout-out to eventlog 30 October 2014
Highlighted code syntax in Keynote 30 August 2014
Migration of Postgres 9.2 to 9.3 with Homebrew and json_enhancements 30 April 2014
Related by Text:
HTML Tree on Hacker News 18 May 2014
YouTube - Nigella's XXXmas 19 December 2008
How and why to use django-mongokit (aka. Django to MongoDB) 08 March 2010
How to track Google Analytics pageviews on non-web requests (with Python) 03 May 2016
hashin 0.7.0 and multiple packages 30 August 2016