Version of TimescaleDB: TimescaleDB2.13<\/p>\n\n\n\n
Version of PostgreSQL: PostgreSQL16<\/p>\n\n\n\n
Target Machine: Rocky Linux 8 & 4core cpu<\/p>\n\n\n\n
Host Machine: Windows10<\/p>\n\n\n\n
See [5] for TimescaleDB version and PostgreSQL version compatibility.<\/p>\n\n\n\n
Hereinafter we assume your username is postgres. Perform the follwing steps in target machine according to [1]<\/p>\n\n\n\n
(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<\/p>\n\n\n\n
(2) Install optional softwares as your environment
sudo dnf install readline-devel zlib-devel systemd-devel openssl-devel<\/p>\n\n\n\n
(3) Configure. In the following, “–with-systemd” is for register postgresql service
.\/configure –enable-debug –with-systemd –prefix=\/usr\/local\/pgsql\/16 –with-openssl<\/p>\n\n\n\n
(4) Remove a flag which means code optimization from “src\/Makefile.global”
before:
CFLAGS = -Wall (\u2026) -fexcess-precision=standard -g -O2
CXXFLAGS = -Wall (\u2026) -g -O2
<\/p>\n\n\n\n
after:
CFLAGS = -Wall (\u2026) -fexcess-precision=standard -g
CXXFLAGS = -Wall (\u2026) -g<\/p>\n\n\n\n
(5) Execute build and install
make
sudo make install<\/p>\n\n\n\n
(6) (Optional) Set search path for shared libraries for PostgreSQL<\/p>\n\n\n\n
sudo \/sbin\/ldconfig \/usr\/local\/pgsql\/lib<\/p>\n\n\n\n
(7) To set Path for PostgreSQL, append ~\/.bach_profile or \/etc\/profile the following lines
export PATH=\/usr\/local\/pgsql\/16\/bin:$PATH<\/p>\n\n\n\n
(8) Create data directory. Hereinafter we call this direcroty <datadir><\/p>\n\n\n\n
mkdir <datadir><\/p>\n\n\n\n
(9) Initialize database<\/p>\n\n\n\n
initdb -U postgres -D <datadir><\/p>\n\n\n\n
(10) Create service unit file “\/etc\/systemd\/system\/postgresql-16.service” as follow<\/p>\n\n\n\n
[Unit]\nDescription=PostgreSQL16 database server\nDocumentation=man:postgres(1)\n\n[Service]\nType=notify\nUser=postgres\nExecStart=\/usr\/local\/pgsql\/bin\/postgres -D <datadir>\nExecReload=\/bin\/kill -HUP $MAINPID\nKillMode=mixed\nKillSignal=SIGINT\nTimeoutSec=0\n\n[Install]\nWantedBy=multi-user.target<\/pre>\n\n\n\n(11) (Optional) Configure the system automatically boot postgresql-16 services at boot<\/p>\n\n\n\n
sudo systemctl enable postgresql-16<\/p>\n\n\n\n
2.2 Installation of TimescaleDB<\/h2>\n\n\n\n
(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<\/p>\n\n\n\n(2) Install optional softwares as your environment
sudo dnf install cmake<\/p>\n\n\n\n(3) Build and Install
.\/bootstrap
cd build
make -j4
sudo make install<\/p>\n\n\n\n2.3 Create hypertable<\/h2>\n\n\n\n
(1) Start postgresql-16 service
sudo systemctl start postgresql-16<\/p>\n\n\n\n(2) Connect to PostgreSQL
psql -U postgres<\/p>\n\n\n\n(3) Create database and extension
create database example;
\\c example
create extension if not exists timescaledb;<\/p>\n\n\n\n
(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);<\/p>\n\n\n\n(5) Show the pid of PostgreSQL server in psql terminal
create view pid as select pg_backend_pid();
table pid;<\/p>\n\n\n\n3. Preparation in host machine<\/h2>\n\n\n\n
(1) According to [3] etc., install Visual Studio Code and set up for remote development using extensions, Remote-SSH, C\/C++, etc. <\/p>\n\n\n\n
(2) Select [Run]>[Add Configuration] and add the following configuration to “launch.json”<\/p>\n\n\n\n
{
“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
}
]
}<\/p>\n\n\n\n4. Debugging<\/h2>\n\n\n\n
(1) In host machine, select [Run]>[Start Debugging] and select pid of 2.(14)<\/p>\n\n\n\n
(2) In host machine, view [Breakpoints] and set a breakpoint on any function, for example, timescaledb_planner().<\/p>\n\n\n\n
(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..<\/p>\n\n\n\n
5. References<\/h2>\n\n\n\n
[1] PostgreSQL official document, Chapter\u00a017.\u00a0Installation from Source Code
https:\/\/www.postgresql.org\/docs\/16\/installation.html<\/a><\/p>\n\n\n\n[2] Remote Development using SSH
https:\/\/code.visualstudio.com\/docs\/remote\/ssh<\/a><\/p>\n\n\n\n[3] Configuring C\/C++ debugging
https:\/\/code.visualstudio.com\/docs\/cpp\/launch-json-reference<\/a><\/p>\n\n\n\n[4] TimescaleDB document, Upgrade PostgreSQL
https:\/\/docs.timescale.com\/self-hosted\/latest\/upgrades\/upgrade-pg\/<\/a><\/p>\n\n\n\n