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!

Enable debug in PostgreSQL
Tagged on:                 

3 thoughts on “Enable debug in PostgreSQL

  • 03/18/2018 at 10:12 am
    Permalink

    Thanks for the post. I am getting closer…
    during compiling, however I am getting the following error:
    In file included from pldbgapi.c:98:0:
    /usr/include/postgresql/9.6/server/libpq/libpq-be.h:36:27: fatal error: gssapi/gssapi.h: No such file or
    directory
    compilation terminated.
    Wonder if you have any suggestion. Thanks in advance.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

− 4 = 5

This site uses Akismet to reduce spam. Learn how your comment data is processed.