102 lines
1.7 KiB
Markdown
102 lines
1.7 KiB
Markdown
# timescaledb
|
|
|
|
<https://github.com/timescale/timescaledb>
|
|
|
|
## Test
|
|
|
|
### Récup datas
|
|
|
|
```bash
|
|
ssh grab-4
|
|
docker exec client-demo-postgres-1 pg_dump -Fc -U postgres postgres > /tmp/backup_postgres.dump
|
|
exit
|
|
|
|
cd /tmp
|
|
scp grab-4:/tmp/backup_postgres.dump .
|
|
pg_restore -c -x -t mesure -d postgres -h localhost -U postgres -W backup_postgres.dump
|
|
# pg_restore -c -x -I mesure_idx1 -d postgres -h localhost -U postgres -W backup_postgres.dump
|
|
# pg_restore -c -x -I mesure_idx2 -d postgres -h localhost -U postgres -W backup_postgres.dump
|
|
pg_restore -c -x -t captation -d postgres -h localhost -U postgres -W backup_postgres.dump
|
|
```
|
|
|
|
### Exploit
|
|
|
|
```sql
|
|
SELECT *
|
|
FROM timescaledb_information.chunks
|
|
WHERE hypertable_name = 'mesure_ng'
|
|
```
|
|
|
|
`SELECT * FROM timescaledb_information.jobs`
|
|
|
|
### WIP
|
|
|
|
#### Grafana
|
|
|
|
mixe de
|
|
|
|
sur le range :
|
|
|
|
```sql
|
|
SELECT *
|
|
FROM (
|
|
|
|
SELECT
|
|
"date" AS time,
|
|
valeur
|
|
FROM mesure_gauge
|
|
WHERE $__timeFilter("date")
|
|
AND $__range_s < 86400
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
"date" AS time,
|
|
average(valeur_temporelle)
|
|
FROM mesure_gauge_heure
|
|
WHERE $__timeFilter("date")
|
|
AND $__range_s BETWEEN 86400 AND 2592000
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
"date" AS time,
|
|
average(valeur_temporelle)
|
|
FROM mesure_gauge_jour
|
|
WHERE $__timeFilter("date")
|
|
AND $__range_s > 2592000
|
|
|
|
) t
|
|
ORDER BY time
|
|
```
|
|
|
|
et où est dans le passé
|
|
|
|
```sql
|
|
SELECT *
|
|
FROM (
|
|
|
|
-- données récentes → brut
|
|
SELECT
|
|
mg."date" AS time,
|
|
mg.valeur
|
|
FROM mesure_gauge mg
|
|
WHERE $__timeFilter(mg."date")
|
|
AND $__to > now() - interval '7 days'
|
|
|
|
UNION ALL
|
|
|
|
-- données anciennes → agrégées
|
|
SELECT
|
|
mgh."date" AS time,
|
|
average(mgh.valeur_temporelle) AS valeur
|
|
FROM mesure_gauge_heure mgh
|
|
WHERE $__timeFilter(mgh."date")
|
|
AND $__to <= now() - interval '7 days'
|
|
|
|
) t
|
|
ORDER BY time;
|
|
```
|
|
|
|
|