Installing the PL/pgSQL Debugger Extension (pldbgapi) for pgAdmin III on PostgreSQL 9.4 and MacOS

postgresql-debugger-install-macos

# First install database

brew install postgres

# Clone and build the PL/pgSQL server-side debugger

srcdir=/usr/local/src
[ -e "$scrdir" ] || \
    sudo sh -c "mkdir $srcdir && chgrp admin $srcdir && chmod g+w $srcdir"
cd "$srcdir"
git clone git://git.postgresql.org/git/pldebugger.git
cd pldebugger
export USE_PGXS=1
make
make install

# Find location of postgresql.conf at the PostgreSQL console:
# SHOW config_file;

nano /usr/local/var/postgres/postgresql.conf

# In nano use ^W to search for: shared_preload_libraries
# Edit the entry to add the library 'plugin_debugger':

shared_preload_libraries = 'plugin_debugger'

# If you have multiple libs, coma separate:

shared_preload_libraries = 'pg_stat_statements,plugin_debugger'

# Restart PostgreSQL

brew services restart postgres

# In a PostgreSQL database that you want to enable debugging install the extension

CREATE EXTENSION pldbgapi;

# In pgAdmin navigate to the same database and right click a PL/pgSQL function.
# In the context menu choose Debugging > Debug. 
# A Debugger window will open and prompt for any parameters.
# It will then break on the first line of executable code.
# BRILLIANT!

# More info from the creators at:
# http://bit.ly/1Gaq51P
# http://git.postgresql.org/gitweb/?p=pldebugger.git;a=blob_plain;f=README.pldebugger;hb=HEAD

To log all SQL statements, edit /usr/local/var/postgres/postgresql.conf and set the following values:

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pg_log'
log_statement = 'all'

Also make the (completely insecure!) log directory: sudo mkdir /var/log/pg_log; sudo chmod 0777 /var/log/pg_log

Then restart the server.

I know this was updated a few years ago, but it's still one of the only good sources of info. Do you know if anything meaningfully changes on newer versions of OSX or Postgres?

1. I believe cd /usr/local/src might not be the right directory anymore.
2. I think the shared_preload_libraries = 'plugin_debugger' line's format may have changed (I'm less certain about this)

Right now I'm failing at the CREATE EXTENSION pldbgapi; step and seeing a ERROR: extension "pldbgapi" has no installation script nor update path for version "1.1".

I haven't had time to run through all of this, but I completed the first part by running these steps first:

sudo mkdir /usr/local/src
sudo chgrp admin usr/local/src
sudo chmod g+w /usr/local/src

I haven't had time to run through all of this, but I completed the first part by running these steps first:

sudo mkdir /usr/local/src
sudo chgrp admin usr/local/src
sudo chmod g+w /usr/local/src

Oh wow - yea this works! Thanks for responding and helping so quickly!

I wasn't sure if /usr/local/src needed to be created if it didn't exist and I didn't realize how postgres (or the plugin) knew where to look for plugin files.

This worked liked a charm for me, only thing I will add is that they moved the repo here:

https://github.com/EnterpriseDB/pldebugger

Hope that helps someone someday. Appreciate the gist Nathan!