How to do linux debugging TimescaleDB remotely using Visual Studio Code

1. Prerequisites

Version of TimescaleDB: TimescaleDB2.13

Version of PostgreSQL: PostgreSQL16

Target Machine: Rocky Linux 8 & 4core cpu

Host Machine: Windows10

See [5] for TimescaleDB version and PostgreSQL version compatibility.

2. Preparation in target machine

Hereinafter we assume your username is postgres. Perform the follwing steps in target machine according to [1]

2.1 Installation of PostgreSQL

(1) Download sources of PostgreSQL from Github repository and change directory
mkdir ts-postgres
cd ts-postgres
git clone https://github.com/postgres/postgres
cd ./postgres

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

(3) Configure. In the following, “–with-systemd” is for register postgresql service
./configure –enable-debug –with-systemd –prefix=/usr/local/pgsql/16 –with-openssl

(4) Remove a flag which means code optimization from “src/Makefile.global”
before:
CFLAGS = -Wall (…) -fexcess-precision=standard -g -O2
CXXFLAGS = -Wall (…) -g -O2

after:
CFLAGS = -Wall (…) -fexcess-precision=standard -g
CXXFLAGS = -Wall (…) -g

(5) Execute build and install
make
sudo make install

(6) (Optional) Set search path for shared libraries for PostgreSQL

sudo /sbin/ldconfig /usr/local/pgsql/lib

(7) To set Path for PostgreSQL, append ~/.bach_profile or /etc/profile the following lines
export PATH=/usr/local/pgsql/16/bin:$PATH

(8) Create data directory. Hereinafter we call this direcroty <datadir>

mkdir <datadir>

(9) Initialize database

initdb -U postgres -D <datadir>

(10) 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/bin/postgres -D <datadir>
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

(11) (Optional) Configure the system automatically boot postgresql-16 services at boot

sudo systemctl enable postgresql-16

2.2 Installation of TimescaleDB

(1) Download sources of TimescaleDB from Github repository and change directory
cd ~
git clone https://github.com/timescale/timescaledb
cd timescaledb
git checkout 2.14.0

(2) Install optional softwares as your environment
sudo dnf install cmake

(3) Build and Install
./bootstrap
cd build
make -j4
sudo make install

2.3 Create hypertable

(1) Start postgresql-16 service
sudo systemctl start postgresql-16

(2) Connect to PostgreSQL
psql -U postgres

(3) Create database and extension
create database example;
\c example
create extension if not exists timescaledb;

(4) Create sample hypertable and insert sample data into the table in psql terminal
create table conditions (
time timestamptz not null,
location text not null,
device text not null,
temperature double precision null,
humidity double precision null
);
select create_hypertable(‘conditions’, by_range(‘time’));
insert into conditions(time, location, device, temperature, humidity) values (now(), ‘office’, ‘device1’, 70.0, 50.0);

(5) Show the pid of PostgreSQL server in psql terminal
create view pid as select pg_backend_pid();
table pid;

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.(14)

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

(3) In target machine, start sample query, for example, “select * from conditions where device = ‘device1’ “. If you can see PostgreSQL server stopping at timescaledb_planner() in planner.c, debugging success..