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.