Installing PostgreSQL with TimescaleDB and Timestamp9 on Arch Linux
Table of Contents
Introduction #
This is a quick-setup guide for installing PostgreSQL with TimescaleDB and Timestamp9 on Arch Linux. This setup is ideal for those looking to leverage the powerful time series capabilities of TimescaleDB while utilizing the enhanced timestamp precision provided by Timestamp9.
Getting started #
This tutorial assumes the following:
- A functional and up-to-date Arch Linux installation.
- Basic familiarity with the command line and package management in Arch Linux.
Install PostgreSQL #
To install PostgreSQL, run the following command:
$ sudo pacman -S postgresql
Then initialize the database cluster:
$ sudo -u postgres initdb -D /var/lib/postgres/data
Start/enable the PostgreSQL service:
$ sudo systemctl enable --now postgresql
Install TimescaleDB #
Next we’ll install TimescaleDB:
$ sudo pacman -S timescaledb
After installation, you need to add the TimescaleDB extension to your PostgreSQL configuration. Edit the postgresql.conf file:
$ sudo gnome-text-editor /var/lib/postgres/data/postgresql.conf
And add the following line to the end of the file after CUSTOMIZED OPTIONS:
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
shared_preload_libraries = 'timescaledb'
Then restart the PostgreSQL service:
$ sudo systemctl restart postgresql
Tune TimescaleDB #
Run the following command to tune TimescaleDB for your system:
$ sudo timescaledb-tune
I accepted all the defaults, but you can customize the settings as needed. After running the command, restart the PostgreSQL service again:
$ sudo systemctl restart postgresql
Install Timestamp9 #
From the timestamp9 GitHub repository:
$ git clone https://github.com/optiver/timestamp9.git
$ cd timestamp9
$ mkdir build
$ cd build
$ cmake ..
$ make
$ sudo make install
However, their requirements for cmake are out-of-date and the build process fails. Instead, replaced the cmake .. command with the following:
$ cmake .. -DCMAKE_POLICY_VERSION_MINIMUM=3.5
Modify the following line to the end of the file after CUSTOMIZED OPTIONS:
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
shared_preload_libraries = 'timescaledb,timestamp9'
Then restart the PostgreSQL service again:
$ sudo systemctl restart postgresql
At some point, I’ll create a PKGBUILD for timestamp9 to make installation easier.
Create A Database and User #
To create a database and user for your PostgreSQL setup, follow these steps:
$ sudo -u postgres createuser --interactive
I suggest using your Linux username for the PostgreSQL user for simplicity and give it superuser privileges.
$ createdb <db>
Enable Extensions #
To enable the TimescaleDB and Timestamp9 extensions in your database, connect to the PostgreSQL shell:
$ sudo -u postgres psql
Then run the following commands:
CREATE EXTENSION timestamp9;
CREATE EXTENSION timescaledb;
Verify that the extensions loaded correctly:
$ psql -d postgres://:@localhost:5432/<db> -c "\dx"
And the output should be something like this:
List of installed extensions
Name | Version | Default version | Schema | Description
-------------+---------+-----------------+------------+---------------------------------------------------------------------------------------
plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.26.1 | 2.26.1 | public | Enables scalable inserts and complex queries for time-series data (Community Edition)
timestamp9 | 1.4.0 | 1.4.0 | public | timestamp nanosecond resolution
(3 rows)
Connect To Your Database #
To connect to your PostgreSQL database, you can use the psql command-line tool:
$ psql -d postgres://:@localhost:5432/<db>
Or just:
$ psql -d <db>