-database name |
connect host:port |
SQL-like query language named InfluxQL
Measurement names, tag keys, tag values, field keys, and field values are case sensitive.
Numerical field values are floats unless they include a trailing i
Use "
(quotes) not '
(apostrophes)
(don't use tz('EST')
use America/New_York
or something from this list.
Not timestamps nor boolean (true, false
)
influx
Command Line Intrepreter queries the database.(slow if on same system as database )
Keywords
ALL |
influx >SHOW DATABASES name: databases _internal ruuvi NOAA_water_database >qIt an be run from another system using
influx -host dbhosst
ERR: no data received
The database is not running.
influx -database ruuvi Connected to http://localhost:8086 version 1.7.4 InfluxDB shell version: 1.7.6 as of 4/25/19 Raspberry Pi >use ruuvi
>show FIELD KEYS name: ruuvi_measurements fieldKey fieldType -------- --------- These (gray) keys are calculated values included by default with ruuvi-collector.properties:storage.values=extended They can be excluded with =raw absoluteHumidity float accelerationAngleFromX float accelerationAngleFromY float accelerationAngleFromZ float accelerationTotal float accelerationX float accelerationY float accelerationZ float batteryVoltage float airDensity float dewPoint float equilibriumVaporPressure float humidity float measurementSequenceNumber integer movementCounter integer pressure float rssi integer temperature float txPower integer >show tag keys name: ruuvi_measurements tagKey ------ dataFormat mac | >show measurements name: measurements name ---- ruuvi_measurements > show series key --- ruuvi_measurements,dataFormat=3,mac=C7103C6824C2 ruuvi_measurements,dataFormat=3,mac=D2503AFE1A96 ruuvi_measurements,dataFormat=3,mac=DC4B596ED18B ruuvi_measurements,dataFormat=3,mac=E4D56DEC6E29 ruuvi_measurements,dataFormat=4,mac=C7103C6824C2 ruuvi_measurements,dataFormat=5,mac=C3F5C373450A ruuvi_measurements,dataFormat=5,mac=C4D8A4A90981 ruuvi_measurements,dataFormat=5,mac=C876E596B2C3 ruuvi_measurements,dataFormat=5,mac=D0FD6D6506DC ruuvi_measurements,dataFormat=5,mac=D2503AFE1A96 ruuvi_measurements,dataFormat=5,mac=D3517872EC0F ruuvi_measurements,dataFormat=5,mac=D6F3DADAC85E ruuvi_measurements,dataFormat=5,mac=DC4B596ED18B ruuvi_measurements,dataFormat=5,mac=DCDF6E338D04 ruuvi_measurements,dataFormat=5,mac=E49ED586E4CE ruuvi_measurements,dataFormat=5,mac=E4D56DEC6E29 ruuvi_measurements,dataFormat=5,mac=E9383FDD20BC ruuvi_measurements,dataFormat=5,mac=F2C0C643AD03 ruuvi_measurements,dataFormat=5,mac=F7B7744722AB ruuvi_measurements,dataFormat=5,mac=F7FA744A1E1A |
SELECT COUNT( * | field_key | field_key,tag_key | /regular_expression/ ) [INTO ] FROM [ WHERE† ] [ GROUP BY [* | tag_key[,tag_key]]] [ ORDER BY time ] [ LIMIT† ] [ OFFSET] [ SLIMIT ] [ SOFFSET ] [ tz('time_zone') ]For field_keys and tag_key with the same name use:
_key
::[field
|tag
]
time
=
, <> ,
!= , > , >= , <
or <=
>
now() -duration_unit
: w d h m s ms u ns
No month code.
tz('America/New_York
)
Example if tz is specified:
'yyyy-mm-dd[Thh:mm:ss-hh:00'
'2021-03-23T00:10:00-04:00' '2021-03-23'
'yyyy-mm-dd[ hh:mm:ss[.thmuuunnn †]'
'yyyy-mm-dd[Thh:mm:ss[.thmuuunnnZ']
… time > '2019-03-26' and time <='2019-03-26 17:01:00' … time > '2019-03-26T17:00:08.960Z' limit 1 time floor ---- ----- 2019-03-26T17:00:08.964Z 44 … time > '2019-03-26T17:00:08.965Z' limit 1 time floor ---- ----- 2019-03-26T17:00:19.062Z 44MEAN( ) from ruuvi_measurements GROUP By time … for each time(12m) interval : faster too!… time >= now()-2h GROUP BY time(10m) requires at least one aggregate function.
SELECT MEAN(batteryVoltage) FROM ruuvi_measurements WHERE … GROUP BY *† ,time(12m) LIMIT 2 ; SELECT round( (32+( (9/5)*(temperature)) )*10 )/10 , mean(batteryVoltage)*100 as volts FROM ruuvi_measurements WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND "mac"='E4D56DEC6E29' group by *,time(6h)
now() - 7d
where
does not support or
for multiple time ranges.
E
astern S
tandard T
ime zone) of the most recent (last
) temperature
, etc:
influx -precision rfc3339 -database ruuviFrom a specfic ruuvi tag.
select mac, round( (32+( (9/5)*(temperature)) )*10 )/10 from ruuvi_measurements where time > now() -2m group by mac tz('America/New_York')
select temperature from ruuvi_measurements where time > now() - 15m AND mac='D3517872EC0F' limit 10 select temperature from ruuvi_measurements where time > '2019-02-19' AND mac='D3517872EC0F' limit 10 select temperature from ruuvi_measurements where time > '2019-02-19T04:08:00Z' AND mac='D3517872EC0F' limit 10All
mac
s with temperature
converted to fahrenheitselect mac,ceil(temperature*9/5)+32 from ruuvi_measurements where time > now()-1h group_by mac tz('America/New_York')All selection from Command Line from a specfic ruuvi tag.
influx -precision rfc3339 -database ruuvi -execute "select round( (32+( (9/5)*(temperature)) )*10 )/10 from ruuvi_measurements where mac='DC4B596ED18B' tz('America/New_York')"Non-interactive query database "metrics" and pretty print json:
influx -database 'metrics' -execute 'select * from cpu' -format 'json' -pretty
Connect to a specific database and set database context(to defaults)
influx -database 'metrics' -host 'localhost' -port '8086'
cURL connects to a running database engine>
Either can be run from a remote system
curl --silent --show-error --get 'http://pi93graf:8086/query?' --data-urlencode "db=ruuvi" --data-urlencode \ "q=SELECT mac,last(temperature) FROM ruuvi_measurements where mac='DC4B596ED18B' limit 3 tz('America/New_York') "
{"results":[{"statement_id":0,"series":[{"name":"ruuvi_measurements", "columns":["time","mac","last"], "values":[["2019-05-22T01:43:53.159467957-04:00","DC4B596ED18B",16.18]]}]}]}
curl --silent --show-error --connect-timeout 6 --get 'http://pi93graf:8086/query?' \ --data-urlencode "db=ruuvi" \ --data-urlencode \ "q=SELECT mac,round( (32+( (9/5)*(temperature)) )*10 )/10 FROM ruuvi_measurements group by mac tz('America/New_York') " |\ sed "s/name.:.ruuvi_measurements/ \\n/g; s/.columns.:..time.,.mac.,.//g; s/.......-..:..//g" |\ sed "s/0000000001//g; s/99999999999//g ; s/\[/\n[/g" # to get it to be more human readable.
curl --silent --show-error --get 'http://localhost:8086/query?pretty=true' --data-urlencode "db=ruuvi" --data-urlencode \ "q=SELECT rssi FROM ruuvi_measurements limit 3" { "results": [ { "statement_id": 0, "series": [ { "name": "ruuvi_measurements", "columns": [ "time", "rssi" ], "values": [ [ "2018-03-25T00:23:19.564Z", -54 ], [ "2018-03-25T00:23:23.63Z", -59 ], [ "2018-03-25T00:23:28.299Z", -50 ] ] } ] } ] } To make this more human readable :Additional examples:
| sed 's/},{/\n/g; s/"//g; s/\[//g ; s/]/\n/g; s/name:ruuvi_measurements,//g; s/columns://g; '\ 's/values://g ; s/{mac://g; s/-0.:00//g' curl --get 'http://localhost:8086/query?' --data-urlencode "db=ruuvi" --data-urlencode "q=SELECT rssi FROM ruuvi_measurements limit 3" {"results":[{"statement_id":0,"series":[{"name":"ruuvi_measurements", "columns":["time","rssi"], "values":[["2018-03-25T00:23:19.564Z",-54],["2018-03-25T00:23:23.63Z",-59],["2018-03-25T00:23:28.299Z",-50]]}]}]}
influx -precision rfc3339 -database ruuvi # ccyy-mm-ddThh:mm:ssZ select mac,ceil(temperature*9/5)+32 temp from ruuvi_measurements where time > now() - 15m group by mac limit 10 tz('America/New_York')name: ruuvi_measurements tags: mac=D3517872EC0F time mac ceil ---- --- ---- 2018-12-28T13:34:11.836-05:00 D3517872EC0F 56 2018-12-28T13:34:21.946-05:00 D3517872EC0F 56 2018-12-28T13:34:32.029-05:00 D3517872EC0F 56 name: ruuvi_measurements tags: mac=E9383FDD20BC time mac ceil ---- --- ---- 2018-12-28T13:34:20.964-05:00 E9383FDD20BC 75 2018-12-28T13:34:31.116-05:00 E9383FDD20BC 75 2018-12-28T13:34:41.268-05:00 E9383FDD20BC 75 select LAST(temperature) from ruuvi_measurements where mac='DC4B596ED18B' tz('America/New_York') (Not ET, not EDT ) name: ruuvi_measurements time last ---- ---- 2018-12-03T20:40:28.217-05:00 24.41 select ceil((temperature*9/5)+32) from ruuvi_measurements where time > 1545583683677000000 tz('America/New_York') 2018-12-23T11:48:13.764-05:00 41 2018-12-23T11:48:23.838-05:00 41 2018-12-23T11:48:33.939-05:00 41 … 2018-12-23T12:11:50.314-05:00 42 2018-12-23T12:12:00.42-05:00 42 note ugly time when ms is nn0 2018-12-23T12:12:11.032-05:00 42 select count(temperature) from ruuvi_measurements where time > 1545583683677000000 tz('America/New_York') name: ruuvi_measurements time count ---- ----- 2018-12-23T11:48:03.677000001-05:00 169 select min(batteryVoltage),mean(batteryVoltage),max(batteryVoltage), STDDEV(batteryVoltage ) from ruuvi_measurements where time > 1545583683677000000 tz('America/New_York') time min mean max stddev ---- --- ---- --- ------ 2018-12-23T11:48:03.677000001-05:00 2.803 2.9022596153846107 2.923 0.008921011610828401 select time, temperature, absoluteHumidity, pressure from ruuvi_measurements where mac='D3517872EC0F' and time > 154079 and rssi> -70 limit 2; name: ruuvi_measurements time temperature absoluteHumidity pressure ---- ----------- ---------------- --------------- 2018-03-22T17:05:14.741Z 6 4.064597097450367 99300 2018-03-22T17:06:15.745Z 6 4.064597097450367 99300 select time, temperature, absoluteHumidity,batteryVoltage, pressure, txPower from ruuvi_measurements where mac='D3517872EC0F' and batteryVoltage>0 limit 2 time temperature absoluteHumidity batteryVoltage pressure txPower ---- ----------- ---------------- -------------- -------- ------- 2018-06-06T17:53:25.336Z 49.86 40.90683320213987 3.001 99442 ### last entry as of 11/30/18 2018-06-06T17:53:36.438Z 49.83 40.84931173861518 3.019 99454 ### notice no txPower values Other MACs : E511D11ECBB8, E6C3BE6F656C , FB0ECBEF7354, F7FA744A1E1A, DFC8B0FE5CC0 D3517872EC0F lines wrapped and precision edited for breivity (ed) select * from ruuvi_measurements where mac='D3517872EC0F' and time > 154079 and rssi> -70 limit `; name: ruuvi_measurements time absoluteHumidity accelerationAngleFromX accelerationAngleFromY accelerationAngleFromZ accelerationTotal ---- ---------------- ---------------------- ---------------------- ---------------------- ----------------- 1540249289169000000 8.624138716002781 76.08684542427619 14.79288467670658 85.07363877795116 0.9898070519045619 accelerationX accelerationY accelerationZ airDensity batteryVoltage dataFormat ------------- ------------- ------------- ---------- -------------- ---------- 0.238 0.957 0.085 1.2378516828652366 2.929 3 dewPoint equilibriumVaporPressure humidity mac measurementSequenceNumber movementCounter pressure -------- ------------------------ -------- --- ------------------------- --------------- -------- 8.66 1121.3290262788335 100 D3517872EC0F 1004 86 rssi temperature txPower ---- ----------- ------- -61 8.66 4 select time, temperature,humidity,batteryVoltage, rssi from ruuvi_measurements where mac='D3517872EC0F' order by time limit 4 name: ruuvi_measurements time temperature humidity batteryVoltage rssi ---- ----------- -------- -------------- ---- 1540249341135000000 8.65 100 2.929 -76 1540249351743000000 8.65 100 2.929 -62 1540249361841000000 8.66 100 2.923 -64 1540249371940000000 8.64 100 2.923 -78 select time, mac, temperature, rssi from ruuvi_measurements order by time limit 6 name: ruuvi_measurements time mac temperature rssi ---- --- ----------- ---- 1540249289169000000 D3517872EC0F 8.66 -61 1540249290475000000 F2C0C643AD03 21 -92 1540249299762000000 D3517872EC0F 8.65 -76 1540249301098000000 F2C0C643AD03 21 -93 1540249309853000000 D3517872EC0F 8.65 -64 select time, temperature, rssi from ruuvi_measurements where mac='F2C0C643AD03' and time > 1540900000000000000 limit 6; name: ruuvi_measurements time temperature rssi ---- ----------- ---- 1540901073139000000 21 -62 1540901087223000000 24 -69 1540901097337000000 24 -85 1540909474167000000 24.91 -70 1540909517698000000 24.95 -50 1540911372699000000 26.97 -76 select time, temperature,humidity,batteryVoltage, rssi from ruuvi_measurements where mac='D3517872EC0F' and time > 1542200000000000000 limit 5; name: ruuvi_measurements time temperature humidity batteryVoltage rssi ---- ----------- -------- -------------- ---- 1542200000530000000 2.51 75 2.905 -56 1542200010627000000 2.5 74.5 2.911 -72 1542200020714000000 2.49 74 2.899 -71 1542200031307000000 2.51 73.5 2.911 -72 1542200041395000000 2.5 73.5 2.911 -70
SELECT_clause
INTO
- measurement_name
- database_name..measurement_name
- database_name.retention_policy_name.measurement_name
- database_name.retention_policy_name.:MEASUREMENT FROM /regular_expression/
FROM_clause [WHERE_clause] [GROUP_BY_clause]
:MEASUREMENT
) maintains the source measurement names in the destination .INTO
queries with different measurements or time boundaries with the WHERE
.
Example:
SELECT "water_level" INTO "h2o_feet_copy_1" FROM "h2o_feet" WHERE "location" = 'coyote_creek'
.
h2o_feet_copy_1
is a new new measurement
Result displays number of points and a meaningless timestamp.
SELECT MEAN("water_level") INTO "all_my_averages" FROM "h2o_feet"
WHERE "location" = 'coyote_creek'
AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)
AggregationsWith text fields (likeMAC ) returns null
| ||||||||||||||||||||||
COUNT( don't use * |
SelectorsSelecting a field and a selected field, value for both fields will be from the row of the selected field. For example:2023-01-13T16:48:17.121272389-05:00 21.79 3.091 2023-01-13T16:49:24.152548228-05:00 21.78 3.067 2023-01-13T16:50:27.597212999-05:00 21.8 3.079 2023-01-13T16:51:29.770300218-05:00 21.82 3.074 2023-01-13T16:52:34.458392959-05:00 21.84 3.0730000000000004 select temperature,min(batteryVoltage) from ruuvi_measurements tz('America/New_York') time temperature min 2023-01-13T07:52:16.446954713-05:00 21.38 3.0730000000000004 When selection returns values from different rows for the different fields 1969-12-31T19:00:00-05:00 is returned as the time.
select first(temperature),last(batteryVoltage) from ruuvi_measurements tz('America/New_York') time first last 1969-12-31T19:00:00-05:00 26.49 2.486 | |||||||||||||||||||||||
SAMPLE( field,n |
select count(batteryVoltage),percentile(batteryVoltage,10), min(batteryVoltage),mean(batteryVoltage),max(batteryVoltage),stddev(batteryVoltage) from ruuvi_measurements where batteryVoltage<1.78 and time > '2021-01-01' and mac='E9383FDD20BC' tz('America/New_York') name: ruuvi_measurements time count percentile min mean max stddev ---- ----- ---------- --- ---- --- ------ 2021-01-01T00:00:00.000000001-05:00 16 1.743 1.729 1.76225 1.778 0.013542525613784159 select mac,ceil(pressure/100),ceil(batteryVoltage*100)/100,ceil(temperature*9/5)+32,ceil(humidity) from ruuvi_measurements where time > NOW()-1m group by mac limit 5 tz('America/New_York')
Transformations | |||||||||||||||||||||||||||||
CEILING( rounded up | ROUND( |
Predictors | ||||||||
HOLT_WINTERS(
|
sigQUIT
(usually ^\
) while a query is running causes influx to output partial results like: ERR: -12-31T00:31:48.091-05:00","D3517872EC0F",33], ["2018-12-31T00:31:58.171-05:00","D3517872EC0F",33],["2018-12-31T00:32:08.251-05:00","D3517872EC0F",33], ["2018-12-31T00:32:18.359-05:00","D3517872EC0F",33],["2018-12-31T00:32:28.463-05:00","D3517872EC0F",33], ["2018-12-31T00:32:38.566-05:00","D3517872EC0F",33],["2018-12-31T00:32:48.679-05:00","D3517872EC0F",33], ["2018-12-31T00:34:39.727-05:00","D3517872EC0F",33],["2018-12-31T00:34:49.818-05:00","D3517872EC0F",33], ["2018-12-31T00:35:00.396-05:00"…
Retention Policy
Unique per database, define:
duration
: how long data is kept, example 4320h0m0s
(180 days) must be greater than 1h
replication factor
: how many copies of this data is stored in the cluster and the
shard group duration
: time range covered by shard groups.
sduration
must be greater than 1h
,
defaults to 1h
if duration
< 2d; 7d
if duration
> 6m
and 1d
otherwise.
autogen
with infinite duration, replication factor of 1, and shard group duration of 7 days (7*24=168h).
ALTER [CREATE] RETENTION POLICY retention_policy_name ON database_name
DURATION duration
REPLICATION n
[SHARD DURATION sduration] [DEFAULT]
GornellVT SHOW RETENTION POLICIES on ruuvi name duration shardGroupDuration replicaN default ---- -------- ------------------ -------- ------- autogen 0s† 168h0m0s 1 true > alter retention policy autogen on ruuvi DURATION 18240h > SHOW RETENTION POLICIES on ruuvi name duration shardGroupDuration replicaN default ---- -------- ------------------ -------- ------- autogen 18240h0m0s 168h0m0s 1 true > SHOW RETENTION POLICIES on ruuvi name duration shardGroupDuration replican default ---- -------- ------------------ -------- ------- autogen 0s 168h0m0s 1 true alter retention policy autogen on ruuvi DURATION 4320h # 180 days 1/2 year, (365+25)= 9360h (365*2+30)= 18240h autogen 4320h0m0s 168h0m0s 1 true
SHOW SERIES [ON database_name] [FROM_clause]
[WHERE tag_key operator [ 'tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause]
SHOW MEASUREMENTS [ON database_name] [WITH MEASUREMENT regular_expression]
[WHERE tag_key operator ['tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause
SHOW TAG KEYS [ON database_name] [FROM_clause]
[WHERE tag_key operator ['tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause]
SHOW TAG VALUES [ON database_name][FROM_clause] WITH KEY [ [operator "tag_key" | regular_expression] | [IN ("tag_key1","tag_key2")]]
[WHERE tag_key operator ['tag_value' | regular_expression]] [LIMIT_clause] [OFFSET_clause]
SHOW FIELD KEYS [ON database_name] [FROM measurement_name]
influxd daemon (service)Is influxd running? curl --connect-timeout 2 --head http://localhost:8086/ping or curl --connect-timeout 2 --head http://pi93graf:8086/ping
HTTP/1.1 204 No Content Content-Type: application/json Request-Id: 75c9524a-7d8c-11e9-9827-b827eb1c5b93 X-Influxdb-Build: OSS X-Influxdb-Version: 1.7.6 X-Request-Id: 75c9524a-7d8c-11e9-9827-b827eb1c5b93 Date: Thu, 23 May 2019 18:56:23 GMT current timeommitting the ping returns HTTP/1.1 404 Not Found Content-Type: text/plain; charset=utf-8 X-Content-Type-Options: nosniff X-Influxdb-Build: OSS X-Influxdb-Version: 1.7.9 Date: Mon, 30 Dec 2019 21:52:23 GMT Content-Length: 19
Starting (takes 40 seconds! on pi zero)
Interactively (shows errors):
commands :
Disable the _internal database in production!
|
>show SERIES key --- ruuvi_measurements,dataFormat=3,mac=DFC8B0FE5CC0 ruuvi_measurements,dataFormat=3,mac=E6C3BE6F656C ruuvi_measurements,dataFormat=3,mac=F7FA744A1E1A ruuvi_measurements,dataFormat=3,mac=FB0ECBEF7354 ruuvi_measurements,dataFormat=4,mac=D3517872EC0F ruuvi_measurements,dataFormat=4,mac=F2C0C643AD03 ruuvi_measurements,dataFormat=5,mac=E511D11ECBB8 ruuvi_measurements,dataFormat=5,mac=E6C3BE6F656C
//http://influxhost:8086/debug/pprof
/debug/pprof/Count | Profile |
1262 | allocs |
44 | block |
0 | cmdline |
22 | goroutine |
1262 | heap |
16 | mutex |
0 | profile |
10 | threadcreate |
0 | trace |
Profile Descriptions:
echo $'\e[3;10;0t \e[8;38;140t' # set window to upper left corner 0px;0px ; restore to 38c x 140c #shutdown and disable ruuviCollector nicely first vi /home/pi/RuuviCollector/RuuviCollector # did NOT;exit 0 sudo systemctl daemon-reload sudo systemctl status ruuvicollector sudo systemctl status influxdb sudo systemctl stop ruuvicollector # it's quite sudo systemctl status ruuvicollector Jun 07 16:10:00 pi93graf systemd[1]: Stopping RuuviCollector... Jun 07 16:10:01 pi93graf pi[21531]: ++ /home/pi/RuuviCollector got sig TERM Jun 07 16:10:01 pi93graf systemd[1]: ruuvicollector.service: Main process exited, code=exited, status=15/n/a Jun 07 16:10:01 pi93graf systemd[1]: Stopped RuuviCollector. Jun 07 16:10:01 pi93graf systemd[1]: ruuvicollector.service: Unit entered failed state. Jun 07 16:10:01 pi93graf systemd[1]: ruuvicollector.service: Failed with result 'exit-code'.
time influxd backup -db dbname [options] path
Exactly one backup path is required and it will be created.
-db name |
Influxdb must be RUNNING!
11/26/22 restored ruuvi started with /var/log free 1,520MB ended with 959MB i.e. 600MB consumed even thoughruuvi was alread included in db.
InfluxDB stores all integers as signed int64 data types. The minimum and maximum are -9023372036854775808 and 9023372036854775807.
Benchmark vs Elasticsearch pdf
from raspberry pi rev B as of 18-12-14 InfluxDB shell version: 1.0.2
Download shard 0 failed dial tcp [::1]:8088: connect: connection refused. Waiting 2s and retrying (0)...
pi@pi93graf:/varnew/backup $ time influxd backup -db ruuvi -portable -start '2022-11-21T21:00:01Z' influxdb.221121
2022/06/09 16:12:31 backing up metastore to 220609/meta.01
2022/06/09 16:12:31 backing up db=ruuvi
2022/06/09 16:12:31 backing up db=ruuvi rp=autogen shard=1064 to 220609/ruuvi.autogen.01064.00 since 0001-01-01T00:00:00Z
2022/06/09 16:12:32 backing up db=ruuvi rp=autogen shard=1074 to 220609/ruuvi.autogen.01074.00 since 0001-01-01T00:00:00Z
2022/06/09 16:12:35 backing up db=ruuvi rp=autogen shard=1084 to 220609/ruuvi.autogen.01084.00 since 0001-01-01T00:00:00Z
…
2022/06/09 16:17:45 backing up db=ruuvi rp=autogen shard=1953 to 220609/ruuvi.autogen.01953.00 since 0001-01-01T00:00:00Z
2022/06/09 16:17:47 backing up db=ruuvi rp=autogen shard=1956 to 220609/ruuvi.autogen.01956.00 since 0001-01-01T00:00:00Z
2022/06/09 16:17:48 backup complete:
2022/06/09 16:17:48 220609/220609/meta.01
2022/06/09 16:17:48 220609/220609/ruuvi.autogen.01064.00
2022/06/09 16:17:48 220609/220609/ruuvi.autogen.01074.00
…
2022/06/09 16:17:48 220609/220609/ruuvi.autogen.01956.00
> du
1,536,936
gzip -v * # Use gzip to compress 40% ! takes longer than the backup! ??????????????/
meta.00: 72.4% -- replaced with meta.00.gz
meta.01: 72.4% -- replaced with meta.01.gz
ruuvi.autogen.01064.00: wait for it .... 41.1% -- replaced with ruuvi.autogen.01064.00.gz
ruuvi.autogen.01074.00: 41.4% -- replaced with ruuvi.autogen.01074.00.gz
…
du
902,036
ftp elsewhere, if server craps it does not help to have the backup there!
influxd
8888888 .d888 888 8888888b. 888888b.
888 d88P" 888 888 "Y88b 888 "88b
888 888 888 888 888 888 .88P
888 88888b. 888888 888 888 888 888 888 888 888 8888888K.
888 888 "88b 888 888 888 888 Y8bd8P' 888 888 888 "Y88b
888 888 888 888 888 888 888 X88K 888 888 888 888
888 888 888 888 888 Y88b 888 .d8""8b. 888 .d88P 888 d88P
8888888 888 888 888 888 "Y88888 888 888 8888888P" 8888888P"
2022-06-09T20:04:25.043135Z info InfluxDB starting
{"log_id": "0azWwHJ0000", "version": "1.8.10", "branch": "1.8", "commit": "688e697c51fd"}
2022-06-09T20:04:25.046570Z info Go runtime {"log_id": "0azWwHJ0000", "version": "go1.13.8", "maxprocs": 1}
run: open server: listen: listen tcp 127.0.0.1:8088: bind: address already in use
102 files ! 40% compression i.e. need nearly same amount of free space.
total 902,028KB (compressed) 6107917 Jun 9 16:14 ruuvi.autogen.01564.00.gz
4027 Jun 9 16:12 meta.00.gz 7842453 Jun 9 16:14 ruuvi.autogen.01574.00.gz
4027 Jun 9 16:12 meta.01.gz 7334368 Jun 9 16:14 ruuvi.autogen.01584.00.gz
9112365 Jun 9 16:12 ruuvi.autogen.01064.00.gz 7491427 Jun 9 16:14 ruuvi.autogen.01594.00.gz
10100117 Jun 9 16:12 ruuvi.autogen.01074.00.gz 7737646 Jun 9 16:14 ruuvi.autogen.01604.00.gz
10018412 Jun 9 16:12 ruuvi.autogen.01084.00.gz 9012170 Jun 9 16:14 ruuvi.autogen.01614.00.gz
10093971 Jun 9 16:12 ruuvi.autogen.01094.00.gz 9919191 Jun 9 16:14 ruuvi.autogen.01624.00.gz
10014369 Jun 9 16:12 ruuvi.autogen.01104.00.gz 9214945 Jun 9 16:14 ruuvi.autogen.01634.00.gz
9350383 Jun 9 16:12 ruuvi.autogen.01114.00.gz 7583859 Jun 9 16:15 ruuvi.autogen.01644.00.gz
8799669 Jun 9 16:12 ruuvi.autogen.01124.00.gz 8035705 Jun 9 16:15 ruuvi.autogen.01654.00.gz
10129304 Jun 9 16:12 ruuvi.autogen.01134.00.gz 7812915 Jun 9 16:15 ruuvi.autogen.01664.00.gz
9971930 Jun 9 16:12 ruuvi.autogen.01144.00.gz 6898768 Jun 9 16:15 ruuvi.autogen.01674.00.gz
10338649 Jun 9 16:12 ruuvi.autogen.01154.00.gz 9108773 Jun 9 16:15 ruuvi.autogen.01684.00.gz
10935943 Jun 9 16:12 ruuvi.autogen.01164.00.gz 9956911 Jun 9 16:15 ruuvi.autogen.01694.00.gz
10487996 Jun 9 16:13 ruuvi.autogen.01174.00.gz 8927441 Jun 9 16:15 ruuvi.autogen.01704.00.gz
9785158 Jun 9 16:13 ruuvi.autogen.01184.00.gz 9950516 Jun 9 16:15 ruuvi.autogen.01714.00.gz
10007074 Jun 9 16:13 ruuvi.autogen.01194.00.gz 10828523 Jun 9 16:15 ruuvi.autogen.01724.00.gz
9843543 Jun 9 16:13 ruuvi.autogen.01204.00.gz 11645187 Jun 9 16:15 ruuvi.autogen.01734.00.gz
10263778 Jun 9 16:13 ruuvi.autogen.01214.00.gz 9754172 Jun 9 16:15 ruuvi.autogen.01744.00.gz
9985800 Jun 9 16:13 ruuvi.autogen.01224.00.gz 9953257 Jun 9 16:15 ruuvi.autogen.01754.00.gz
10048734 Jun 9 16:13 ruuvi.autogen.01234.00.gz 9540121 Jun 9 16:15 ruuvi.autogen.01764.00.gz
9348034 Jun 9 16:13 ruuvi.autogen.01244.00.gz 9288917 Jun 9 16:15 ruuvi.autogen.01774.00.gz
8753534 Jun 9 16:13 ruuvi.autogen.01254.00.gz 10458672 Jun 9 16:15 ruuvi.autogen.01784.00.gz
10611069 Jun 9 16:13 ruuvi.autogen.01264.00.gz 10080642 Jun 9 16:15 ruuvi.autogen.01794.00.gz
7201673 Jun 9 16:13 ruuvi.autogen.01274.00.gz 7985134 Jun 9 16:15 ruuvi.autogen.01804.00.gz
2991225 Jun 9 16:13 ruuvi.autogen.01304.00.gz 7499328 Jun 9 16:15 ruuvi.autogen.01814.00.gz
10505932 Jun 9 16:13 ruuvi.autogen.01314.00.gz 6452491 Jun 9 16:16 ruuvi.autogen.01824.00.gz
9611319 Jun 9 16:13 ruuvi.autogen.01324.00.gz 7019536 Jun 9 16:16 ruuvi.autogen.01834.00.gz
10766026 Jun 9 16:13 ruuvi.autogen.01334.00.gz 7558604 Jun 9 16:16 ruuvi.autogen.01844.00.gz
8962623 Jun 9 16:13 ruuvi.autogen.01344.00.gz 8897265 Jun 9 16:16 ruuvi.autogen.01854.00.gz
8385461 Jun 9 16:13 ruuvi.autogen.01354.00.gz 10924221 Jun 9 16:16 ruuvi.autogen.01864.00.gz
6459954 Jun 9 16:13 ruuvi.autogen.01364.00.gz 12363387 Jun 9 16:16 ruuvi.autogen.01874.00.gz
7551240 Jun 9 16:13 ruuvi.autogen.01374.00.gz 12018262 Jun 9 16:16 ruuvi.autogen.01884.00.gz
7416688 Jun 9 16:13 ruuvi.autogen.01384.00.gz 12052525 Jun 9 16:16 ruuvi.autogen.01894.00.gz
8606300 Jun 9 16:13 ruuvi.autogen.01394.00.gz 11922178 Jun 9 16:16 ruuvi.autogen.01904.00.gz
8705236 Jun 9 16:13 ruuvi.autogen.01404.00.gz 12638709 Jun 9 16:16 ruuvi.autogen.01911.00.gz
8949879 Jun 9 16:13 ruuvi.autogen.01414.00.gz 12320562 Jun 9 16:16 ruuvi.autogen.01914.00.gz
8752702 Jun 9 16:13 ruuvi.autogen.01424.00.gz 13914144 Jun 9 16:16 ruuvi.autogen.01917.00.gz
7449924 Jun 9 16:14 ruuvi.autogen.01434.00.gz 13821826 Jun 9 16:17 ruuvi.autogen.01920.00.gz
6770908 Jun 9 16:14 ruuvi.autogen.01444.00.gz 14029147 Jun 9 16:17 ruuvi.autogen.01923.00.gz
6889952 Jun 9 16:14 ruuvi.autogen.01454.00.gz 12522485 Jun 9 16:17 ruuvi.autogen.01926.00.gz
5656594 Jun 9 16:14 ruuvi.autogen.01464.00.gz 12712720 Jun 9 16:17 ruuvi.autogen.01929.00.gz
6732739 Jun 9 16:14 ruuvi.autogen.01474.00.gz 11554468 Jun 9 16:17 ruuvi.autogen.01932.00.gz
7652810 Jun 9 16:14 ruuvi.autogen.01484.00.gz 11206133 Jun 9 16:17 ruuvi.autogen.01935.00.gz
8957651 Jun 9 16:14 ruuvi.autogen.01494.00.gz 11243129 Jun 9 16:17 ruuvi.autogen.01938.00.gz
11268997 Jun 9 16:14 ruuvi.autogen.01504.00.gz 9771050 Jun 9 16:17 ruuvi.autogen.01941.00.gz
11135192 Jun 9 16:14 ruuvi.autogen.01514.00.gz 9909770 Jun 9 16:17 ruuvi.autogen.01944.00.gz
8393563 Jun 9 16:14 ruuvi.autogen.01524.00.gz 9847670 Jun 9 16:17 ruuvi.autogen.01947.00.gz
7848930 Jun 9 16:14 ruuvi.autogen.01534.00.gz 10693497 Jun 9 16:17 ruuvi.autogen.01950.00.gz
7834470 Jun 9 16:14 ruuvi.autogen.01544.00.gz 10443978 Jun 9 16:17 ruuvi.autogen.01953.00.gz
5055082 Jun 9 16:14 ruuvi.autogen.01554.00.gz 2344092 Jun 9 16:17 ruuvi.autogen.01956.00.gz
influxd restore
Uses backup copies from the specified PATH to restore databases or specific shards from InfluxDB OSS
or InfluxDB Enterprise to an InfluxDB OSS instance.
influxd restore [-portable] [options] path
portable
consumes files in an improved Enterprise-compatible format that includes a file manifest.
sudo influxd restore -metadir /varnew/backup/220609 .
Using metastore snapshot: meta.01
No other messages, no change in systemctl status influxdb
-metadir directory
where backup meta is found
Using metastore snapshot: meta.01
-host host:port
InfluxDB OSS host to connect to where the data will be restored.
Defaults to '127.0.0.1:8088'.
-db name
Name of database to be restored (InfluxDB OSS or InfluxDB Enterprise)
-rp name
Optional:Name of retention policy from the backup
With -db db_name
'
-newdb name
Name of the InfluxDB OSS database `into which will be imported.
Default: -db db_name
.
Must be unique to the target system.
-portable
Activate the portable restore mode. Default legacy restore mode .
-newrp name
Name of the retention policy to be created on the target system.
Requires '-rp -rp
-shard id
optional: With '-db PATH
` Path to directory containing the backup files.
Notes
Datafiles must be owned/group influxdb influxdb
> sudo find /var/lib/influxdb -user root
# any found need to be chown chgrp influxdb
> cd /var/lib/influxd
/var/lib/influxdb > sudo chown -vR influxdb * |grep -v retained
changed ownership of '0_chgrp' from root to influxdb
changed ownership of 'data/_internal/monitor/274/fields.idx' from root to influxdb
changed ownership of 'data/_internal/monitor/274' from root to influxdb
changed ownership of 'data/_internal/monitor/272/000000015-000000002.tsm' from root to influxdb
changed ownership of 'meta/meta.db' from root to influxdb
changed ownership of 'wal/_internal/monitor/274/_00001.wal' from root to influxdb
changed ownership of 'wal/_internal/monitor/274' from root to influxdb
later:changed ownership of 'data/ruuvi/autogen/10/000000001-000000001.tsm' from root to influxdb
changed ownership of 'data/ruuvi/autogen/5/000000001-000000001.tsm' from root to influxdb
changed ownership of 'data/_internal/monitor/276/000000010-000000001.tsm' from root to influxdb
changed ownership of 'data/_internal/monitor/276/fields.idx' from root to influxdb
changed ownership of 'wal/_internal/monitor/276/_00036.wal' from root to influxdb
changed ownership of 'wal/_internal/monitor/276/_00035.wal' from root to influxdb
See Go builtins
from pi zero 3/27/19 InfluxDB shell version: 1.7.4 , `
influx_inspect,
chronograf
inport CSV data