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

postgresql-debugger-install-ubuntu

# PostgreSQL can be on a remote server but you'll need root privileges in Linux and superuser in PostgreSQL.

# First install build tools

sudo su
aptitude install build-essential
aptitude install postgresql-server-dev-9.4

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

cd /usr/local/src
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 /etc/postgresql/9.4/main/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

pg_ctlcluster --mode fast 9.4-main restart

# 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

Can't seem to get past make of pldebugger (missing Makefile.global and /contrib/conttrib-global.mk):

Makefile:32: ../../src/Makefile.global: No such file or directory
Makefile:33: /contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target `/contrib/contrib-global.mk'.  Stop.

Any suggestions why that might be? Thanks :)

Also wondering if it is possible to remote debug - my development databases are running in vagrant (virtualbox) instances so i access them via tcp... perhaps you might know and could steer me in the right direction - thanks again

This saved me hours. Thank you so much.

@ReinsBrian It's totally possible to debug Vagrant (VirtualBox) PostgreSQL databases. I'm actually doing it right now. You just need to make sure the proper ports are forwarded back to your host machine. Something in your Vagrantfile like:

        # If running under VirtualBox
        config.vm.provider "virtualbox" do |v, override|
            # Forward the default postgres port (5432) for access by the host machine, using
            # pgAdmin for example
            override.vm.network "forwarded_port", guest: 5432, host: 5433, id: "postgres"
        end

I forwarded it to 5433 to avoid any port collisions with any Postgres instances running locally (on the host machine). Then just connect to your database (via PGAdmin or however you want) at 127.0.0.1:5433.

@ReinsBrian I got that same error you're seeing when I wasn't running as root. Make sure you're doing sudo su before everything like the gist shows and not just sudo make.

Try make NO_PGXS=1, make install NO_PGXS=1

I just did the following to get rid of that missing Makefile problem:

sh
export USE_PGXS=1
gmake; gmake install

• AJ

You will also need these packages in order to compile the extension under Ubuntu:

$ sudo apt-get install libssl-dev libkrb5-dev

when making, is there a way to specify which postgresql-server-dev version to use? On my system I have all of them, up until 10, but I specifically want to compile for 9.6

I managed to compile the debugger for PostgreSQL 12 on Ubuntu server 18.04. However with the plugin_debugger.so referenced in the postgresql.conf's shared_preload_libraries, PostgresSQL failed to start with the following issue: 2019-12-25 08:44:31.610 MST [21578] FATAL: could not load library "/usr/lib/postgresql/12/lib/plugin_debugger.so": /usr/lib/postgresql/12/lib/plugin_debugger.so: undefined symbol: PostmasterIsAlive Any pointer is greatly appreciated.

I am getting the exact same error after I upgraded to PostgreSQL 12 also on Ubuntu 18.04.

@granteckels I use postgresql-12 and ubuntu 18.04 and I installed this version postgresql-12-pldebugger which is compatible, that would only be for the installation steps the other steps and configuration are the same