How to convert CQ

InfluxDB 2
, ,
1

CREATE CONTINUOUS QUERY cq_dbs_metrics_v2 ON postgres_stat RESAMPLE FOR 8m BEGIN SELECT non_negative_derivative(last(tup_fetched), 1m) AS fetched, non_negative_derivative(last(tup_returned), 1m) AS returned, non_negative_derivative(last(tup_inserted), 1m) AS inserted, non_negative_derivative(last(tup_updated), 1m) AS updated, non_negative_derivative(last(tup_deleted)) AS deleted, non_negative_derivative(last(xact_commit), 1m) AS commit, non_negative_derivative(last(xact_rollback), 1m) AS rollback, non_negative_derivative(last(blks_read), 1m) AS blks_read, non_negative_derivative(last(blks_hit), 1m) AS blks_hit, non_negative_derivative(last(conflicts), 1m) AS conflicts, non_negative_derivative(last(temp_files), 1m) AS temp_files, non_negative_derivative(last(temp_bytes), 1m) AS temp_bytes, non_negative_derivative(last(deadlocks), 1m) AS deadlocks, non_negative_derivative(last(blk_read_time), 1m) AS blk_read_time, non_negative_derivative(last(blk_write_time), 1m) AS blk_write_time INTO postgres_stat_metrics.autogen.dbs FROM postgres_stat.statemants.dbs GROUP BY time(1m), * END

I do this

from(bucket: “postgres_stat/statemants”)
|> range(start: -8m)
|> filter(fn: (r) => r[“_measurement”] == “dbs”)
|> group(columns: [“_measurement”, “host”, “datname”, “_field”])
|> aggregateWindow(every: 1m, fn: last, createEmpty: false)
|> derivative(unit: 1m, nonNegative: true)
|> set(key: “_measurement”, value: “dbs”)
|> to(org: “dba”, bucket: “postgres_stat_metrics”)

but I get OOM

2

Hello @Guzya,
What are you running it on and how much data?
Also what is your upgrade story?
I might recommend holding off on upgrading until OSS v3 comes out later this year because there are significant changes between OSS v2 and v3.

3

Hello!
Test server: 1 cpu, 2 gb
There is not much data.

name: dbs
tagKey:
datname
host
fieldKey fieldType

blk_read_time float
blk_write_time float
blks_hit float
blks_read float
conflicts float
deadlocks float
freeTXID float
numbackends float
temp_bytes float
temp_files float
tup_deleted float
tup_fetched float
tup_inserted float
tup_returned float
tup_updated float
xact_commit float
xact_rollback float

from(bucket: “postgres_stat/statemants”)
|> range(start: -10m)
|> filter(fn: (r) => r[“_measurement”] == “dbs”)
|> filter(fn: (r) => r[“_field”] == “xact_commit”)
|> count()
|> yield(name: “count”)

tables = 1132
count = 9

4

upgrade.log

[root@influxdb1-testers.mhd:~]# cat /var/lib/influxdb/upgrade.log 
{"level":"info","ts":1691047620.9970028,"caller":"upgrade/upgrade.go:402","msg":"Starting InfluxDB 1.x upgrade"}
{"level":"info","ts":1691047620.9972768,"caller":"upgrade/upgrade.go:405","msg":"Upgrading config file","file":"/etc/influxdb/influxdb.conf"}
{"level":"info","ts":1691047620.9977236,"caller":"upgrade/upgrade.go:409","msg":"Config file upgraded.","1.x config":"/etc/influxdb/influxdb.conf","2.x config":"/var/lib/influxdb/.influxdbv2/config.toml"}
{"level":"info","ts":1691047620.997875,"caller":"upgrade/upgrade.go:419","msg":"Upgrade source paths","meta":"/var/lib/influxdb/meta","data":"/var/lib/influxdb/data"}
{"level":"info","ts":1691047620.997975,"caller":"upgrade/upgrade.go:420","msg":"Upgrade target paths","bolt":"/var/lib/influxdb/.influxdbv2/influxd.bolt","engine":"/var/lib/influxdb/.influxdbv2/engine"}
{"level":"info","ts":1691047621.0041096,"caller":"bolt/bbolt.go:83","msg":"Resources opened","service":"bolt","path":"/var/lib/influxdb/.influxdbv2/influxd.bolt"}
{"level":"info","ts":1691047621.0056314,"caller":"migration/migration.go:175","msg":"Bringing up metadata migrations","service":"migrations","migration_count":18}
{"level":"info","ts":1691047655.342208,"caller":"upgrade/setup.go:73","msg":"CLI config has been stored.","path":"/var/lib/influxdb/.influxdbv2/configs"}
{"level":"info","ts":1691047655.3428268,"caller":"upgrade/database.go:202","msg":"Checking available disk space"}
{"level":"info","ts":1691047655.3473392,"caller":"upgrade/database.go:223","msg":"Computed disk space","free":"38 GB","required":"416 MB"}
{"level":"info","ts":1691047658.113675,"caller":"upgrade/database.go:51","msg":"Upgrading databases"}
{"level":"warn","ts":1691047658.1194184,"caller":"upgrade/database.go:158","msg":"Empty retention policy"}
{"level":"info","ts":1691047662.652048,"caller":"upgrade/database.go:195","msg":"Database upgrade complete","upgraded_count":2}
{"level":"info","ts":1691047662.652352,"caller":"upgrade/security.go:45","msg":"Upgrading 1.x users"}
{"level":"info","ts":1691047662.6548533,"caller":"upgrade/security.go:115","msg":"User upgrade complete","upgraded_count":1}
{"level":"warn","ts":1691047662.6551054,"caller":"upgrade/upgrade.go:484","msg":"1.x users were upgraded, but 1.x auth was not enabled. Existing clients will fail authentication against 2.x if using invalid credentials"}
{"level":"info","ts":1691047662.6553125,"caller":"upgrade/upgrade.go:489","msg":"Upgrade successfully completed. Start the influxd service now, then log in","login_url":"http://localhost:8086"}