PostgreSQL Tutorial: Debug PL/pgSQL procedures with DBeaver

October 5, 2023

Summary: DBeaver debugger may be used to debug PL/pgSQL stored procedures in PostgreSQL. The Debugger is available as an extension for your PostgreSQL installation. You must have superuser privileges to use the debugger.

Table of Contents

Prerequisites for Debugging

To enable interactive debugging of PL/pgSQL procedures on a Postgres server, you need to use the plugin debugger. The plugin debugger is a typical interactive debugger delivered as an extension. It requires a shared library preload in Postgres to operate the shared_preload_libraries parameter in the settings. The debugger is developed and maintained by EDB. Its source code is available for examination and improvement.

The debugger provides the required server API for debugging PL/pgSQL procedures with:

Breakpoint management;
Step-by-step tracing;
Variable acquisition and management.

Installation

PostgreSQL pldebugger on Ubuntu-based distros

If you happen to have a PostgreSQL 12 installed via apt, then the procedure is quite straightforward:

sudo apt install postgresql-12-pldebugger
sudo service postgresql restart

After that, run the following command in the database or databases that you wish to debug functions in:

CREATE EXTENSION pldbgapi;

PostgreSQL pldebugger Installation from source code

You can find the source code in this repository. Installation instructions are located in the README file.

DBeaver Debugger extension Installation

The debugger extension supports SQL debugger in PostgreSQL.

P2 repository URL: https://dbeaver.io/update/debug/latest/

You can install the extension as following steps:

Main menu Help -> Install New Software.
Paste extension P2 repository URL into Work with field and press Enter.
Check items you wish to install.
Click Next->Finish. Restart DBeaver.

IMPORTANT: The installation will not work if DBeaver is installed in a write-protected folder (like Program Files, /Applications, /usr/). To proceed you need to run DBeaver from some non write-protected folder, or run it as the Administrator or root.

Running debugger in the DBeaver interface

Open the source code of the function you want to debug. To toggle breakpoints, place the caret on the line you want the function to be stopped at and use a shortcut Shift + Control + B. Alternatively, you can toggle the breakpoint with your mouse by clicking on a ruler, as demonstrated in the screenshot below: