How to install the PL/PGSQL debugger and use it in PgAdmin

The debugger is an extension of PostgreSQL installation and it needs to be installed and configured in order to be able to use it.

Once installed, it can be very useful to debug PL/pgSQL functions in PostgreSQL, EDB-SPL functions, or stored procedures.

I thereby explain the necessary steps to install and enable the debugger. In my case I have already installed PostgreSQL version 13.3 and pgAdmin version 5.4 so I will not cover how to install these two.

First install the PostgreSql pldebugger:
sudo apt-get install postgresql-13-pldebugger

In case that the package is not available it can be also compiled:
sudo apt-get install build-essential
sudo apt-get install postgresql-server-dev-13
cd /usr/local/src
git clone git://git.postgresql.org/git/pldebugger.git
cd pldebugger
export USE_PGXS=1
make
make install

Once installed, you need to edit the postgresql.conf by:
sudo nano /etc/postgresql/13/main/postgresql.conf

Use Ctrl+W to search for shared_preload_libraries, replace it with:
shared_preload_libraries = 'plugin_debugger'

Save the changes (Ctrl+X and press Y followed by Enter). Once this part is done you need to restart PostgreSQL service by:
sudo service postgresql stop
sudo service postgresql start

In order to check if everything is up and running, you need to check the PostgreSQL log:
sudo tail -f /var/log/postgresql/postgresql-13-main.log

The last step is to enable the debugger in a PostgreSQL database that you want to debug by installing the extension with:
CREATE EXTENSION pldbgapi;

You can do this either from the PostgreSQLcommand line or from pgAdmin directly.

Now let us assume that we want to debug the get_film_count (len_from integer, len_to integer) function that returns the number of films that have the length between len_from and len_to.

In pgAdmin navigate to the same database and right click the PL/pgSQL function get_film_count(integer,integer). We have now two new tools, Debug and Set BreakPont, as shown in the following figure:

pgAdmin_debugger_menu

In the context menu choose Debugging > Debug and a Debugger window will open to prompt for any parameters (as seen in the below figure).

pgAdmin_debug

Once some values are given (len_from and len_to in our case), it will then break on the first line of the function after the Debug button is clicked.

In the other context menu, if you choose Debugging -> Set breakpoint the following window opens:

pgAdmin_set_breakpoint

Waiting for the next function call. When a function call happens, the debugger waits for you to add breakpoints, or step through it line-by-line.

I found the debugger “a must have” for developing and debugging PL/pgSQL functions in PostgreSQL. More information about how to use the pgAdmin debugger can be found in the official documentation