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:
<key>StandardErrorPath</key> <string>/opt/boxen/homebrew/var/postgres/server.log</string>
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'!
UPDATE Dec 2018
Another way to use Homebrew to restart PostgreSQL is with the command:
$ brew services restart postgresql
Comments
Nice! Thank you so much!