Enable debug in PostgreSQL

PgAdmin comes with built-in support for debugging your Pl/PgSQL programs. However in order to enable this, you’ll have to compile and install extra plug-in to PostgreSQL.

I Googled a lot but didn’t find one single post with all steps so here we go: ( I did this on my Ubuntu)

• Prepare environment:

By default, Ubuntu doen’t come with the most recent PostgreSQL. You can do the following to add PostgreSQL’s offical repository so you get the most up-to-date version (v9.6 at the time of writing). Refer to https://www.postgresql.org/download/linux/ubuntu/

Create file /etc/apt/sources.list.d/pgdg.list, and add following line:

deb http://apt.postgresql.org/pub/repos/apt/ YOUR_UBUNTU_VERSION_HERE-pgdg main

Import the repository signing key, and update the package lists:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -
sudo apt-get update

• Install development tools (if you don’t have or found error in below make):

sudo apt-get install gcc
sudo apt-get install libssl-dev
sudo apt-get install libkrb5-dev
sudo apt-get install make

• Install development modules:

sudo apt-get install build-essential (not always required)
sudo apt-get install postgresql-server-dev-9.6

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

The source can be found here, (use “snapshot” to download all files in one go)

cd /usr/lib/postgresql/9.6/
sudo mkdir -p contrib/src
cd /usr/lib/postgresql/9.6/contrib/src
git clone git://git.postgresql.org/git/pldebugger.git
cd pldebugger
export USE_PGXS=1
make
make install

•  Configuration

Once installed, edit /etc/postgresql/9.4/main/postgresql.conf to enable the debugger pluggin:

# Search for: shared_preload_libraries
# Edit the entry to add the library ‘plugin_debugger’:

shared_preload_libraries = ‘plugin_debugger’

(Refer to  https://gist.github.com/jhngrant/c1787346fcb4b0e3001a)

• Restart PostgreSQL

sudo service postgresql stop
sudo service postgresql start

• Enable this extension in PostgreSQL

The following is done at each DB level, not instance level. So log into the DB and run following SQL:

CREATE EXTENSION pldbgapi;

Done!