How to do linux debugging PostgreSQL remotely using Visual Studio Code

1. Prerequisites

Version of PostgreSQL: PostgreSQL13

Target Machine: CentOS7

Host Machine: Windows10

2. Preparation in target machine

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

(1) Download sources of PostgreSQL from official website and change directory

wget https://ftp.postgresql.org/pub/source/v13.0/postgresql-13.0.tar.gz
tar -xzvf ./postgresql-13.0.tar.gz
cd ./postgresql-13.0

(2) Install optional softwares as your environment

sudo yum install readline-devel zlib-devel

(3) Configure

./configure –enable-debug

(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) To set Path for PostgreSQL, append ~/.bach_profile or /etc/profile the following lines

PATH=/usr/local/pgsql/bin:$PATH
export PATH

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

mkdir <datadir>

(8) Initialize database

initdb -D <datadir>

(9) Create service unit file “/etc/systemd/system/postgresql-13.service” as follow

[Unit]
Description=PostgreSQL13 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

(10) (Optional) Configure the system automatically boot postgresql-13 services at boot

sudo systemctl enable postgresql-13

(11) Start postgresql-13 service

sudo systemctl start postgresql-13

(12) Connect to PostgreSQL

psql -U postgres

(13) Create sample table and insert sample data into the table in psql terminal

create table t1(id integer, name text);
insert into t1 values(1, ‘hoge1’);
insert into t1 values(2, ‘hoge2’);

(14) Show the pid of PostgreSQL server in psql terminal

select pg_backend_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/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, 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..

5. References

[1] PostgreSQL official document, Chapter 16. Installation from Source Code
https://www.postgresql.org/docs/13/installation.html

[2] PostgreSQL source file browser
https://www.postgresql.org/ftp/source/v13.0/

[3] Remote Development using SSH
https://code.visualstudio.com/docs/remote/ssh

[4] Configuring C/C++ debugging
https://code.visualstudio.com/docs/cpp/launch-json-reference

Published by Yuuki Fujii

I love open souce database management systems.

Leave a comment

Your email address will not be published. Required fields are marked *