1. Overview

PostgreSQL is a powerful open-source relational database management system used in various applications. During development, debugging PostgreSQL can be essential for identifying and resolving issues. In this blog, we’ll walk through setting up a remote PostgreSQL development debugging environment using Visual Studio Code (VSCode) on a client machine and PostgreSQL running on a Linux machine.

2. Setup on Linux

• Install Ubuntu 22.04 on a physical or virtual machine following the instructions provided here. Create a user for this practice, for example, “ubuntu.”
• Generate an SSH key pair on the Ubuntu machine to allow remote debugging without entering a username and password repeatedly:

$ sudo apt update && sudo apt install ssh -y
$ ssh-keygen -t ed25519
$ cat /home/ubuntu/.ssh/ed25519.pub >> /home/ubuntu/.ssh/authorized_keys

• Copy the private key to your Windows client machine (e.g., c:\Users\user1.ssh\id_ed25519). Notes: for a better security practice, you should generate the ssh key pair on your Client machine and add the public key information to Server’s authorized_keys, and keep your private key as a secret known only by yourself.
• Install PostgreSQL build dependencies on Ubuntu:

$ sudo apt-get install -y build-essential git gdb lcov bison flex \
          libkrb5-dev libssl-dev libldap-dev libpam0g-dev python3-dev \
          tcl-dev libperl-dev gettext libxml2-dev libxslt1-dev \
          libreadline-dev libedit-dev uuid-dev libossp-uuid-dev \
          libipc-run-perl perl libtest-simple-perl

• Clone PostgreSQL, compile it, and start the PostgreSQL server:

$ git clone https://github.com/postgres/postgres.git
$ cd postgres && git checkout REL_15_3 -b pg153 
$ ./configure  --prefix=/home/ubuntu/mypg --enable-tap-tests --enable-debug CFLAGS="-g3 -O0"
$ make –j
$ make install

$ export PGDATA=/home/ubuntu/mydb
$ export PATH=/home/ubuntu/mypg/pgapp/bin:$PATH
$ export LD_LIBRARY_PATH=/home/ubuntu/mypg/pgapp/lib
$ initdb -D $PGDATA
$ pg_ctl -D $PGDATA -l logfile start
$ psql -d postgres

3. Setup Visual Studio Code

• Install Visual Studio Code from here and install the “Remote – SSH” and “C/C++” debugging extensions.
• Install “Remote – SSH” extension by typing remote-ssh like below screenshot.

• Install a c/c++ debugging extension like below screenshot and select the one released from Microsoft.

After remote-ssh and c/c++ debugging extensions installed, you should see an small icon on the very bottom left corner, like the picture below hightlighed in blue,

click on the remote ssh connection icon and setup the remote connection configuration.

Select Connect to Host ... and then enter ssh ubuntu@ubuntu-ip-addr, If VS Code asks a local ssh configuration then use the one located at c:\Users\user1.ssh\config and enter below information:

Host ubuntu-ip-addr
HostName ubuntu-ip-addr
IdentityFile c:\Users\user1.ssh\id_ed25519
IdentiftiesOnly Yes

• If remote-ssh has been setup properly, then VSCode should pop-up a new IDE window, then use this new IDE window to open the source code from the remote Ubuntu server.
• Create a launch.json file under .vscode with the following configuration to attach the PostgreSQL backend:

{
    "version": "0.2.0",
    "configurations": [
        {
            "name": "(gdb) Attach",
            "type": "cppdbg",
            "request": "attach",
            "program": "/home/ubuntu/mypg/bin/postgres",
            "MIMode": "gdb",
            "setupCommands": [
                {
                    "description": "Enable pretty-printing for gdb",
                    "text": "-enable-pretty-printing",
                    "ignoreFailures": true
                },
                {
                    "description": "Set Disassembly Flavor to Intel",
                    "text": "-gdb-set disassembly-flavor intel",
                    "ignoreFailures": true
                }
            ]
        }
    ]
}

make sure change /home/ubuntu/mypg/bin/postgres to point to your PostgreSQL installation path on your Ubuntu server.

• Test the remote PostgreSQL debugging by clicking View -> Run, then select “gdb) Attach” and attach to the PostgreSQL backend process.

4. Summary

In this blog post, we demonstrated how to set up a remote PostgreSQL development debugging environment using Visual Studio Code on a client machine and PostgreSQL running on a Linux machine. With this setup, developers can efficiently debug PostgreSQL and identify and resolve issues during development. Happy debugging!

David Zhang

A software developer specialized in C/C++ programming with experience in hardware, firmware, software, database, network, and system architecture. Now, working in HighGo Software Inc, as a senior PostgreSQL architect.