Timescale is Postgresql based open source time series DB. Unlike Influx, Timescale does not store all the indexes in RAM, therefore it can be used to store large amount of metric values. In this case, the same Postgresql DB can be used to store metric values, stream history and data acquisition settings.
Timescale DB installation and configuration
A specific Postgresql version (9.6, 10 or 11) must be installed during setup process. If earlier Postgresql version is installed (for example, Centos 7 default repository contains Posgresql 9.2), it shoud be fully removed including /usr/bin executables.
To setup Postgresql 9.6+Timescale DB on CentOS 7, do the following:
1. Install Postgresql repository
yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2. Create /etc/yum.repos.d/timescale_timescaledb.repo file
[timescale_timescaledb] name=timescale_timescaledb baseurl=https://packagecloud.io/timescale/timescaledb/el/7/$basearch repo_gpgcheck=1 gpgcheck=0 enabled=1 gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey sslverify=1 sslcacert=/etc/pki/tls/certs/ca-bundle.crt metadata_expire=300
3. Install Postgresql
yum install -y postgresql96-server
4. Install TimescaleDB
yum install -y timescaledb-postgresql-9.6
5. Initialize Postgresql DB
/usr/pgsql-9.6/bin/postgresql96-setup initdb
6. Configure Postgresql to work with Timescale DB
timescaledb-tune --yes --pg-config=/usr/pgsql-9.6/bin/pg_config
7. Configure access to Postgresql tables in /var/lib/pgsql/9.6/data/pg_hba.conf file
# IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5
8. Launch Postgresql
su - postgres /usr/pgsql-9.6/bin/postgresql &
9. Enter Postgresql console
/usr/pgsql-9.6/bin/psql
10. Create user and DB
CREATE USER wcsoam WITH PASSWORD 'wcsoam'; CREATE DATABASE wcsstat;
12. Grant DB privileges to user
\с wcsstat GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "wcsoam";
13. Re-enter Postgresql console as wcsoam user
\q /usr/pgsql-9.6/bin/psql -U wcsoam
14. Create metrics storage table
\с wcsstat CREATE TABLE IF NOT EXIST conditions( time timestamptz not null, node_id bigint not null, mediaid varchar(255), video_height bigint, video_width bigint, video_rate bigint, video_sync bigint, video_fps bigint, video_nack bigint, video_pli bigint, video_codec bigint, audio_sync bigint, audio_rate bigint, audio_lost bigint, audio_codec bigint);
15. Create TimescaleDB extension for Timescale functions to work
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
16. Configure Timescale hypertable with chunk duration set to 1 hour for example:
SELECT create_hypertable('conditions', 'time', 'node_id', 1, chunk_time_interval => interval '1 hour');
17. Create DB to store stream history and data acquisition settings
CREATE DATABASE wcsoam; \c wcsoam GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "wcsoam";
18. Exit Postgresql console
\q exit
Data retention setup using cron
To save disk space, data retention can be set using cron utility. For example, set crontab as follows to check conditions table every 5 minutes and delete metrics data older than 12 hours:
*/5 * * * * PGPASSWORD="wcsoam" /usr/pgsql-9.6/bin/psql -h localhost -p 5432 -U wcsoam -d wcsstat -w -c "SELECT drop_chunks(interval '12 hours', 'conditions');"
DB structure
The following table shoul be in Postgresql to store metrics data
Field | Type | Note |
---|---|---|
CONDITIONS | ||
time | timestamptz | NOT NULL |
node_id | bigint | NOT NULL |
media_id | character varying(255) | |
video_height | bigint | |
video_width | bigint | |
video_rate | bigint | |
video_sync | bigint | |
video_fps | bigint | |
video_nack | bigint | |
video_pli | bigint | |
video_codec | bigint | |
audio_sync | bigint | |
audio_rate | bigint | |
audio_lost | bigint | |
audio_codec | bigint |
Metrics data are fragmented to chunks and are stored to subtables, each subtable volume is defined by chunk duration. To limit subtables amount, it is recommeded to setup data retention using cron with maximum data storage interval.