Comment on page
Find all the information about how to set up your TimescaleDB instance for Speculare. It will be the core of the whole infrastructure for Speculare.
Speculare is built on top of TimescaleDB which is itself built on top of PostgreSQL and is an extremely performant Relational / Timeseries Database.
As we need to use some advanced PostgreSQL functionality and to ease the development of our project, we rely on some features of PostgreSQL that need to be enabled as well as some plugins. We'll cover here the configuration needed to be able to run Speculare correctly.
In this document (and the others) I might refer to PostgreSQL for TimescaleDB as they are both pretty much the same thing but with the tweak that TimescaleDB is more like an add-on on top of PostgreSQL.
We strongly recommend you to use the v13+ even if in theory any version above v10 should work just fine.
Upon successful installation of TimescaleDB, I suggest you to install
timescaledb-toolsas this will allow you to run:
The above command will offer you to apply certain settings to your PostgreSQL instance (in order to "optimize" it).
You can then verify that PostgreSQL has been installed correctly by running:
systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sun 2020-10-07 10:23:46 UTC; 6min ago
Main PID: 8159 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 2362)
You can install it easily by running (or by checking their docs):
sudo apt install postgresql-13-wal2json
You now need to enable both plugins (wal2json & timescaledb) otherwise installing them would be useless. For that you have to edit
wal2json & timescaledbto the
# - Shared Library Preloading -
shared_preload_libraries = 'wal2json, timescaledb' # (change requires restart)
#local_preload_libraries = ''
#session_preload_libraries = ''
#jit_provider = 'llvmjit'
As you probably know, PostgreSQL doesn't offer a real-time API to listen to change over a Web-socket or such. Because of that we had to develop a Change Data Capture service which listen to the WAL output.
Logical replication can be enabled by changing a few line in
wal_level = logical # minimal, archive, hot_standby, or logical (change requires restart)
max_wal_senders = 5 # max number of walsender processes (change requires restart)
#wal_keep_segments = 4 # in logfile segments, 16MB each; 0 disables
#wal_sender_timeout = 60s # in milliseconds; 0 disables
max_replication_slots = 5 # max number of replication slots (change requires restart)
synchronous_commit = local # synchronization level;
After all those changes, you will need to restart PostgreSQL for changes to take effect.
An important point to note is that those are "default" settings and may not be suitable for production use.
And you should now be ready to use PostgreSQL.