...
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.
Postgresql and Timescale DB installation
...
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.
...
Code Block |
---|
|
CREATE USER wcsoam WITH PASSWORD 'wcsoam';
CREATE DATABASE wcsstat; |
1211. Grant DB privileges to user
Code Block |
---|
|
\с wcsstat
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "wcsoam";
|
13. Re-enter Postgresql console as wcsoam user
Code Block |
---|
|
\q
/usr/pgsql-9.6/bin/psql -U wcsoam |
14. Create metrics storage table12. Create DB to store stream history and data acquisition settings
Code Block |
---|
|
\с 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
Code Block |
---|
|
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; |
16. Configure Timescale hypertable with chunk duration set to 1 hour for example:
Code Block |
---|
|
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
Code Block |
---|
|
CREATE DATABASE wcsoam;
\c wcsoam
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "wcsoam"; |
...
DATABASE wcsoam;
\c wcsoam
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "wcsoam"; |
13. Exit Postgresql console
Timescale DB setup
To configure Timescale DB for metric storage, do the following:
1. Set the following parameter in wcsoam.properties file
Code Block |
---|
|
metric_store=timescale |
2. Set metrics storage chunk interval in init_tsdb.properties file
Code Block |
---|
|
timescale_chunk_interval=2 days |
By default, metrics are stored in 2 days chunks.
3. Launch DB setup script
Code Block |
---|
|
\q
exit./init_tsdb.sh |
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 hours5 days:
Code Block |
---|
|
*/5 * * * * PGPASSWORD="wcsoam" /usr/pgsql-9.6/bin/psql -h localhost -p 5432 -U wcsoam -d wcsstat -w -c "SELECT drop_chunks(interval '125 hoursdays', 'conditions');" |
DB structure
...