TimescaleDB
Find all the information about how to set up your Postgre instance for Speculare.
Speculare is built on top of TimescaleDB which is built on top of Postgres and is an extremely scalable Relational / Timeseries Database.
As we need to use some advanced Postgres functionnality and to ease the development of our project, we rely on some features of Postgres that need to be enabled as well as some plugins. We'll cover here the configuration needed to be able to run Speculare correctly.

Setup

We strongly recommend you to use the version 13, but in theory any version above 10 should work just fine.

Install TimescaleDB 13 on Ubuntu

You can refer to this link from Timescale for installation on your system: docs of Timescale.
As a little tips, I suggest you to install timescaledb-tools. This will allow you to run:
1
timescaledb-tune
Copied!
The above command will offer you to apply certain settings to your Postgres instance.
You can then verify that Postgres has been installed correctly by running:
1
systemctl status postgresql.service
2
3
● postgresql.service - PostgreSQL RDBMS
4
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
5
Active: active (exited) since Sun 2020-10-07 10:23:46 UTC; 6min ago
6
Main PID: 8159 (code=exited, status=0/SUCCESS)
7
Tasks: 0 (limit: 2362)
8
CGroup: /system.slice/postgresql.service
Copied!

Install wal2json required for Speculare

Speculare rely on one more Postgres plugin, which is wal2json.
You can install it easily by running:
1
sudo apt install postgresql-13-wal2json
Copied!

Enable wal2json

You now need to enable both plugins, otherwise installing them would be useless. For that you can edit /etc/postgresql/13/main/postgresql.conf to add wal2json to the shared_preload_libraries variable.
1
# - Shared Library Preloading -
2
3
shared_preload_libraries = 'wal2json, timescaledb' # (change requires restart)
4
#local_preload_libraries = ''
5
#session_preload_libraries = ''
6
#jit_provider = 'llvmjit' # JIT library to use
Copied!

Set up logical replication

As you probably know, Postgres doesn't offer a realtime API to listen to change over a WebSocket or such. Because of that we had to develop a Capture Data Change service which listen to the WAL functionnality.
This feature allow us to get a stream of change which is then filtered and broadcasted over WebSocket to subscribed clients. This is done though Speculare-PGCDC.
Logical replication can be enabled by changing a few line in /etc/postgresql/13/main/postgresql.conf:
1
# REPLICATION
2
wal_level = logical # minimal, archive, hot_standby, or logical (change requires restart)
3
max_wal_senders = 5 # max number of walsender processes (change requires restart)
4
#wal_keep_segments = 4 # in logfile segments, 16MB each; 0 disables
5
#wal_sender_timeout = 60s # in milliseconds; 0 disables
6
max_replication_slots = 5 # max number of replication slots (change requires restart)
7
synchronous_commit = local # synchronization level;
Copied!
After all those changes, you will need to restart Postgres for changes to take effect.
And you should be ready to use Postgres. If something is missing in this guide, don't hesitate to open an issue on Github.
Last modified 3mo ago
Copy link
Contents
Setup