How do log ALL PostgreSQL SQL happening

20 July 2015   0 comments   MacOSX, PostgreSQL

Powered by Fusion×

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:
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
Fastest database for Tornado 09 October 2013
Related by Text:
Migration of Postgres 9.2 to 9.3 with Homebrew and json_enhancements 30 April 2014
Running simple SQL commands on the command line 08 January 2005
Connecting with psycopg2 without a username and password 24 February 2011
"No space left on device" on OSX Docker 03 October 2017
Javascript tip: nifty use of the console.log function in Firebug 07 November 2010