Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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 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
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!
@ProgrammerSuresh

This comment has been minimized.

Copy link

@ProgrammerSuresh ProgrammerSuresh commented Sep 27, 2018

i am getting below error after restarting the server and server is not getting started

FATAL: incompatible library "/usr/local/lib/postgresql/plugin_debugger.so": version mismatch
DETAIL: Server is version 9.6, library is version 10.0.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment