How to install the PL/pgSQL debugger

Pgadmin4 has a very nice PL/pgSQL debugger available on github.

To install it, we follow a procedure in 3 steps :

1. To download the code from git, to compile it and to install it

2. To modify postgresql.conf and to restart the PostgreSQL server

3. To install the extension in pgadmin4

The Docker embeds PostgreSQL in a minimal Debian distribution. Before installing any software, we create an apt repository

$ apt-get update

and we install the vim editor

$ apt-get install vim

and we install git

$ apt-get install git-core

Step 1 : compilation and installation of the debugger

We install some libraries needed by the debugger

$ apt-get install build-essential
$ apt-get install postgresql-server-dev-11
$ apt-get install openssl
$ apt-get install libkrb5-dev

We compile and we install he debugger

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

Step 2 : Configuration of PostgreSQL

To restart the server, we will use pg_ctl. We have to log as postgres to use pg_ctl

$ su - postgres

But before to be able to use pg_ctl, we need to set the environment variables we will find with psql

postgres=# show data_directory;
      data_directory      
--------------------------
 /var/lib/postgresql/data
(1 row)


postgres=# show config_file;
               config_file                
------------------------------------------
 /var/lib/postgresql/data/postgresql.conf
(1 row)

By the way, we can find this information with

select name, setting from pg_settings where category = 'File Locations';

We edit the .bash_profile that will be executed by the next call of su – postgres

$ vim .bash_profile

and we add the following lines

export PGDATA=/var/lib/postgresql/data
export PATH=$PATH:/usr/lib/postgresql/11/bin

We do ctrl-d to exit postgres account and we come back

$ su - postgres
$ pg_ctl status

and pg_ctl answers

$ pg_ctl: server is running (PID: 1)

We edit the configuration file of PostgreSQL

vim /var/lib/postgresql/data/postgresql.conf

and we replace

shared_preload_libraries = ''

by

shared_preload_libraries = 'plugin_debugger'

and we restart the PostgreSQL server

pg_ctl restart

Step 3 Configuration of pgadmin4

We go in pgadmin4. The debugger is an extension and PostgreSQL extensions are linked to a database, not to the cluster. Then we choose the database where we want to install the extension. We click Extensions and we find in the list : pldbgapi ! We click on it and that’s it.

debugger

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s