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

Note: timescaledb-tune rewrites postgresql.conf, including the shared_preload_libraries line. If you re-run it after completing the timestamp9 steps below, check that the line still lists both extensions.

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

Then edit postgresql.conf again:

$ sudo gnome-text-editor /var/lib/postgres/data/postgresql.conf

And modify the existing shared_preload_libraries line under CUSTOMIZED OPTIONS to add timestamp9 alongside timescaledb:

#------------------------------------------------------------------------------
# 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 #

Extensions are enabled per-database, so connect to the database you just created rather than the default postgres database:

$ psql -d <db>

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>

Copy Data Into Your Database #

To copy data into your PostgreSQL database, you can use the COPY command. For example, if you have a CSV file named data.csv, you can run the following command:

$ psql -d <db> -c "\COPY <table> FROM 'data.csv' CSV"

Check The Database Size #

To check the size of your PostgreSQL database, you can run the following command:

$ psql -d <db> -c "SELECT pg_size_pretty(pg_database_size('<db>'));"