Category PostgreSQL

Debugging PostgreSQL with VS Code
Linux, PostgreSQL, Windows

Debugging PostgreSQL with Visual Studio Code

by jb

Debugging a database system often requires looking beyond SQL statements and configuration parameters. When investigating complex issues, understanding what happens inside the PostgreSQL source code can provide valuable insights into query execution, process behavior and internal database mechanisms. For developers, contributors and advanced database administrators, the ability to step through PostgreSQL code can significantly simplify troubleshooting and performance analysis.

In this blog post, we will explore how to debug PostgreSQL using Visual Studio Code and the GNU Debugger (GDB). You will learn how to build PostgreSQL with debugging symbols, configure Visual Studio Code for source-level debugging, set breakpoints, inspect variables and follow the execution flow of PostgreSQL backend processes.

Whether you are developing PostgreSQL extensions, investigating database internals or simply looking to deepen your understanding of how PostgreSQL works under the hood, this guide provides a practical introduction to debugging one of the world’s most popular open-source database system.

Requirements

The following is required to begin a debugging session:

  • A Linux VM with a running postgres database. The installation of this kind of VM is described in this post (Compiling PostgreSQL). Make sure to answer yes (y) when the script asks you to create a development build.
  • Visual Studio Code with the Remote Development extension installed.

Configuration

The following config file for VS Code needs to be created on the linux server in the postgres source code directory (/home/postgres/postgresql):

# run as root
su - postgres -c "mkdir ~/postgresql/.vscode
cat >~/postgresql/.vscode/launch.json <<EOF
{
    "version": "0.2.0",
    "configurations": [
        {
            "name": "Attach to Postgres Backend",
            "type": "cppdbg",
            "request": "attach",
            "program": "/usr/local/pgsql/bin/postgres",
            "processId": "${command:pickProcess}",
            "MIMode": "gdb",
            "setupCommands": [
                {
                    "description": "Ignore SIGUSR1 (used by PostgreSQL)",
                    "text": "handle SIGUSR1 nostop noprint pass",
                    "ignoreFailures": false
                },
                {
                    "description": "Enable pretty-printing",
                    "text": "-enable-pretty-printing",
                    "ignoreFailures": true
                }
            ]
        }
    ]
}
EOF

Next we setup the remote connection in VS Code and connect to the Linux VM.

  • Remote Explorer => New Remote => ssh postgres@lin5 => Connect => Enter password (changeme)
  • Now we open the folder with the PostgreSQL source code: /home/postgres/postgresql
  • After the connection to the linux server is established we install the C/C++ extension from Microsoft on the server.

VS Code should now look as follows:

Debugging a PostgreSQL background process

We are now able to set a breakpoint in the PostgreSQL source code file in VS Code, start a database session and step in the code when PostgreSQL reaches the breakpoint.

Starting a PostgreSQL session

First we need to create a database session and get the background process pid. Don’t close the session, we will need it later.

# run as root
su - postgres
psql
SELECT pg_backend_pid();

Setting a breakpoint

In VS Code we can open the source code file (/home/postgres/postgresql/src/backend/access/transam/xact.c) and search for the function StartTransactionCommand(void). This function is started whenever a new transaction begins. We can hit F9 to set a breakpoint on that line.:

Starting the Debugging session

We can now start the debugging session by hitting F5. When asked for the process we enter the background process pid. VS Code looks now as follows:

Hitting the breakpoint

If we go back to our PostgreSQL session and enter begin; to start a new transaction we notice that the command seems to hang. In VS Code we can verify that the debugger is now at the breakpoint and waiting for further instructions:

We are now able to Continue code execution (F5), Step over the current statement (F10) or disconnect the debugger with Shift+F5. We are also able to inspect the current value of variables which is quite useful.

Conclusion

Debugging PostgreSQL with Visual Studio Code and GDB is an effective way to understand database internals, troubleshoot complex issues and analyze backend processes. While the initial setup requires some effort, source-level debugging provides valuable insights into PostgreSQL’s behavior and execution flow.

I hope this guide helps you get started with PostgreSQL debugging and serves as a useful reference for future development and troubleshooting tasks.

0