How to install the PL/pgSQL debugger

Spectacle.T10043

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

To install it, we have 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 server

3. To install the extension in pgadmin4

The Docker PostgreSQL is embedded in a minimal Debian kernel. 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

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 the 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

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)

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

$ vi .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 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

We go in pgadmin4. The PL/pgSQL 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 then create. And we find in the list : pldbgapi !

debugger