How to do linux debugging PostgreSQL remotely using Visual Studio Code(Using meson+ninja)

1. Prerequisites

The version of PostgreSQL: PostgreSQL16

Target Machine: Rockylinux8

The version of ninja: 1.11

Host Machine: Windows10

2. Preparation in target machine

Hereinafter we assume os user is postgres who has sudo privilege. Perform the follwing steps in target machine according to [1].

(1) Download sources of PostgreSQL from official website and change directory
git clone https://github.com/postgres/postgres
cd ./postgres
git checkout REL16 -b test

(2) Install optional softwares as your environment
sudo dnf install readline-devel zlib-devel systemd-devel

(3) Install meson
sudo dnf install dnf-plugins-core
sudo dnf install epel-release
sudo dnf config-manager –set-enabled powertools
sudo dnf install meson

(4) Install ninja
wget -O ninja-linux.zip https://github.com/ninja-build/ninja/releases/download/v1.11.1/ninja-linux.zip
sudo unzip ninja-linux.zip -d /usr/local/bin/

(5) Make install directory of postgresql and data directory of postgresql
sudo mkdir -p /usr/local/pgsql/16
sudo chown -R postgres /usr/local/pgsql/
sudo mkdir -p /var/lib/pgsql/16
sudo chown -R postgres /var/lib/pgsql/16

(6) Configures a build directory for the Meson project.
meson setup ./build –buildtype debug –debug –optimization=’0′ –prefix=/usr/local/pgsql/16 -Dcassert=true -Dc_args=’-DWAL_DEBUG’

(7) Build source code
ninja -C build

(8) Install binaries
sudo ninja install -C build

(9) (Optional) Set search path for shared libraries for PostgreSQL
sudo /sbin/ldconfig /usr/local/pgsql/16/lib
(10) To set Path for PostgreSQL, append ~/.bach_profile or /etc/profile the following lines
PATH=/usr/local/pgsql/16/bin:$PATH
export PATH
(11) Initialize database
initdb -U postgres -D /var/lib/pgsql/16
(12) Create service unit file “/etc/systemd/system/postgresql-16.service” as follow
[Unit]
Description=PostgreSQL16 database server
Documentation=man:postgres(1)

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/16/bin/postgres -D /var/lib/pgsql/16
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target
(13) (Optional) Configure the system automatically boot postgresql-16 services at boot
sudo systemctl enable postgresql-16
(14) Start postgresql-13 service
sudo systemctl start postgresql-16
(15) Connect to PostgreSQL
psql -U postgres
(16) Create sample table and insert sample data into the table in psql terminal
create database tmp;
\c tmp
set wal_debug=on;
set client_min_messages=DEBUG5;
create table t1(id integer, name text);
insert into t1 values(1, ‘hoge1’);
insert into t1 values(2, ‘hoge2’);

3. Preparation in host machine

(1) According to [3] etc., install Visual Studio Code and set up for remote development using extensions, Remote-SSH, C/C++, etc.

(2) Select [Run]>[Add Configuration] and add the following configuration to “launch.json”

{
“name”: “(gdb) Attach”,
“type”: “cppdbg”,
“request”: “attach”,
“program”: “/usr/local/pgsql/16/bin/postgres”,
“processId”: “${command:pickProcess}”,
“MIMode”: “gdb”,
“setupCommands”: [
{
“description”: “Enable pretty-printing for gdb”,
“text”: “-enable-pretty-printing”,
“ignoreFailures”: true
}
]
}

4. Debugging

(1) In host machine, select [Run]>[Start Debugging] and select pid of 2.

(2) In host machine, view [Breakpoints] and set a breakpoint on any function, for example, planner.

(3) In target machine, start sample query, for example, “select * from t1 where id = 1”. If you can see PostgreSQL server stopping at planner() in planner.c, debugging success..