Timescaledb schema environment issues

English Forum
1

Hi Openems,

I have followed setting up the timescaledb schema as in: New integration: TimescaleDB. The issue is when I try to connect using the latest version of the timescale backend, I get:

ERROR: relation “edge_channel” does not exist

Looking through the code it looks like the issue is at:
Cache fromDatabase(Statement stmnt) throws SQLException

Seems like I am querying table edge_channel even though it does not exist. I am a bit of noob when it comes to databases, so sorry if this is a stupid question. Would you be able to supply me with the latest configuration of TimescaleDB? From what I can see, there are a couple of fields that I am missing from the one that is described above.

Then lastly, are there any active development still happening on Timescaledb? Or is this not really priority at the moment?

Regards,

Bart

2

Hi Openems folks,

Just an update:
I did some further digging and its clear that the schema has been updated from the original post here: New integration: TimescaleDB.

From what I can see, the issue is that the schema is not automatically created at start-up when the database is still empty, hence creating the issue above. So I guess the question is really what I should do different so that the schema is implemented if it does not yet exists? I manually tried to create the schema, similar to what I could find in SchemaGenerator.java, but with no success.

Please would someone be able to point me in the right direction to getting my environment set up? I am eager to start working on the backfilling function of TimescaleDB.

Hope to hear from you soon.

Regards,

Bart

3

Hi Bart,

sorry, I have been (and still am) currently very busy and hardly find time to answer in the Community and handle open Pull-Requests.

The TimescaleDB integration had been developed by our dev team at FENECON because we had planned to replace our existing InfluxDB 1.8 setup with it. Unfortunately after a few months of effort we had to admit, that the performance of TimescaleDB was just to slow for our requirements. Because of this we did not continue with TimescaleDB but instead built our own data-aggregation implementation based on a separate InfluxDB server.

Our setup now consists of two InfluxDB servers - one serving as a “data-warehouse” with values in high resolution - and the other one being optimized for queries from OpenEMS UI and only holding pre-aggregated data per-5-minutes or per-day. This setup is running in production on our side and we will hopefully soon publish it in OpenEMS.

We also have developed migration scripts that allow us to migrate from data-warehouse-influx to aggregated-influx. So if you now just use InfluxDB you will later be able to just add the ‘aggregated-influx’ and automatically migrate data.

So for now and until nobody else continues the work on TimescaleDB I would consider it deprecated and cannot recommend to use it with OpenEMS Backend.

Regards,
Stefan

2 Likes
4

Hi Stefan,

Thanks for the feedback, it is much appreciated! And I am very excited to test it.

Do you have any idea on the timeline on the production version being available to the Openems community?

Hope to hear from you soon.

Regards,

Bart

5

Hi Stefan,

Thanks again for all the good work. Do you perhaps have a timeline on when we can expect the “data-aggregation implementation” to be available on Openems? This is something that will be highly appreciated, and putting a date on it will help with planning and implementation on my end.

Hope to hear from you soon.

Regards,
Bart

6

@michaelgrill Could you please give a timeline and backport that feature to OpenEMS? Thanks :slight_smile:

7

Hi Stefan,

Interesting to hear your experience with trying to move to TimescaleDB as a storage for the data from OpenEMS. We have been considering the same and would be very keen to learn more about your experiences.

the performance of TimescaleDB was just to slow for our requirements

Is it possible for you to elaborate a bit more on this?
There are quite a few articles and benchmarks comparing the two technologies.
The exact trade-off for your use case would be insightful to weigh in on our own decisionmaking.

Best,
Jaap

1 Like
8

Hi Stefan,

I hope all is well.

Do you perhaps have any idea on when the new backfilling feature will be available on the platform?

Keep up the good work!

Regards,

Bartho

9

Hi There OpenEms Folk,

Seems like this has been incorporated into the latest release! Looking forward to testing it.

Cheers,

10

Hi Bartho,

exactly. The initial plan was to release the code with proper documentation, but that would have taken a lot more time. Triggered by your comments, I decided to just backport the feature from FEMS development.

The actual commit is here:

The entire backport (including other features, like bugfixes to RRD4j and the required AggregatedInflux in the Backend is here:

Sorry for now providing proper documentation, but I hope you can still work with this for now. I would appreciate if you could document your experiences with the code.

Thanks & regards,
Stefan

11

Hi Stefan,

Thank you so much for this! I will be more than happy to try and document every thing as I work through it. Hoping all goes smoothly. :wink:

Cheers,

Bartho

1 Like
12

Hi Stefan,

So I started testing the backfilling feature. Not sure if I am implementing this correctly, but please see my current findings below:

I am running the latest version of both the backend and edge applications. ( Release 2023.9.0) The edge device is successfully connecting to the WebSocket.edge on my server. And from the logs, the Timedata.AggregatedInfluxDB service is successfully connected to the influx service (v2.7.1). But it does not look like the data is pulling through to influx. Please see the logs below:

Sep 26 20:22:11 ip-172-31-29-181 systemd[1]: Started OpenEMS.
Sep 26 20:22:12 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [org.ops4j.pax.logging.internal.Activator] INFO : Enabling Java Util Logging API support.
Sep 26 20:22:12 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [org.ops4j.pax.logging.internal.Activator] INFO : Enabling SLF4J API support.
Sep 26 20:22:12 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [org.ops4j.pax.logging.internal.Activator] INFO : Enabling Apache Commons Logging API support.
Sep 26 20:22:12 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [org.ops4j.pax.logging.internal.Activator] INFO : Enabling JULI Logger API support.
Sep 26 20:22:12 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [org.ops4j.pax.logging.internal.Activator] INFO : Enabling Avalon Logger API support.
Sep 26 20:22:12 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [org.ops4j.pax.logging.internal.Activator] INFO : Enabling JBoss Logging API support.
Sep 26 20:22:12 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [org.ops4j.pax.logging.internal.Activator] INFO : Enabling Log4J v1 API support.
Sep 26 20:22:12 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [org.ops4j.pax.logging.internal.Activator] INFO : Enabling Log4J v2 API support.
Sep 26 20:22:12 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [log4j2] INFO : Initializing Log4j2 using default configuration
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [log4j2] INFO : Scanning for classes in '/org/ops4j/pax/logging/log4j2/internal/bridges/' matching criteria annotated with @Plugin
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [log4j2] INFO : Scanning for classes in '/org/apache/logging/log4j/layout/template/json/' matching criteria annotated with @Plugin
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [log4j2] INFO : Scanning for classes in '/org/apache/logging/log4j/layout/template/json/resolver/' matching criteria annotated with @Plugin
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [log4j2] INFO : Scanning for classes in '/org/apache/logging/log4j/layout/template/json/util/' matching criteria annotated with @Plugin
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [log4j2] INFO : Log4J2 configured using default configuration.
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-log4j2 [org.ops4j.pax.logging.spi.support.EventAdminConfigurationNotifier] INFO : Sending Event Admin notification (configuration successful) to org/ops4j/pax/logging/Configuration
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: org.ops4j.pax.logging.pax-logging-api [log4j2] INFO : Log4J2 configured using configuration from passed properties
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:13,463 [artLevel] INFO  [backend.application.BackendApp] ====================================================
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:13,465 [ogging])] INFO  [ventAdminConfigurationNotifier] Sending Event Admin notification (configuration successful) to org/ops4j/pax/logging/Configuration
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:13,469 [artLevel] INFO  [backend.application.BackendApp] OpenEMS Backend version [2023.10.0-SNAPSHOT] started
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:13,470 [artLevel] INFO  [backend.application.BackendApp] ====================================================
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:13,589 [artLevel] INFO  [end.metadata.file.MetadataFile] Activate [path=/opt/openems-backend/metadataUpdate.json]
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:13,690 [artLevel] INFO  [regatedinflux.AggregatedInflux] [AggregatedInflux] Activate [url=http://10.1.1.1:8086;bucket=data;apiKey=ok;measurementAvg=avgmeasurementMax=[Ljava.lang.String;@cd9ff50]
Sep 26 20:22:13 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:13,697 [hread #2] INFO  [socket.AbstractWebsocketServer] [Edge.Websocket] Starting websocket server [port=8081]
Sep 26 20:22:14 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:14,311 [artLevel] INFO  [rg.eclipse.jetty.server.Server] jetty-11.0.15; built: 2023-04-11T18:37:53.775Z; git: 5bc5e562c8d05c5862505aebe5cf83a61bdbcb96; jvm 17.0.8+9-LTS-211
Sep 26 20:22:14 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:14,366 [artLevel] INFO  [ession.DefaultSessionIdManager] Session workerName=node0
Sep 26 20:22:14 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:14,386 [artLevel] INFO  [.server.handler.ContextHandler] Started o.e.j.s.ServletContextHandler@7bd39059{/,null,AVAILABLE}
Sep 26 20:22:14 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:14,388 [artLevel] INFO  [rg.eclipse.jetty.server.Server] Started Server@7529963c{STARTING}[11.0.15,sto=0] @3031ms
Sep 26 20:22:14 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:14,431 [artLevel] INFO  [jetty.server.AbstractConnector] Started ServerConnector@3322f18a{HTTP/1.1, (http/1.1)}{0.0.0.0:8079}
Sep 26 20:22:14 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:14,433 [artLevel] INFO  [org.apache.felix.http         ] Started Jetty 11.0.15 at port(s) HTTP:8079 on context path / [minThreads=8,maxThreads=200,acceptors=1,selectors=1]
Sep 26 20:22:23 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:23,685 [thread-1] INFO  [socket.AbstractWebsocketServer] [Edge.Websocket] [monitor] Connections: 0, Pool: 0/10, Pending: 0, Completed: 0, Active: 0
Sep 26 20:22:23 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:23,718 [thread-1] INFO  [hared.influxdb.InfluxConnector] [InfluxDB] [monitor] timedata0 Pool: 0/10, Pending: 0, Completed: 0, Active: 0, MergePointsWorker[data/rp_avg: 0/1000000, data/rp_max: 0/1000000, Default: 0/1000000], Limit:0.000, RejectedExecutions:0
Sep 26 20:22:33 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:33,687 [thread-1] INFO  [socket.AbstractWebsocketServer] [Edge.Websocket] [monitor] Connections: 0, Pool: 0/10, Pending: 0, Completed: 0, Active: 0
Sep 26 20:22:33 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:33,719 [thread-1] INFO  [hared.influxdb.InfluxConnector] [InfluxDB] [monitor] timedata0 Pool: 0/10, Pending: 0, Completed: 0, Active: 0, MergePointsWorker[data/rp_avg: 0/1000000, data/rp_max: 0/1000000, Default: 0/1000000], Limit:0.000, RejectedExecutions:0
Sep 26 20:22:35 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:35,916 [socket-1] INFO  [mon.metadata.SimpleEdgeHandler] Edge [edge0]. Update config: Created _appManager (Core.AppManager): apps=[{"appId":"App.Ap..., keyForFreeApps="0000-0000-0000-0...; Created _cycle (Core.Cycle): cycleTime="1000"; Created _evcsSlowPowerIncreaseFilter (Evcs.SlowPowerIncreaseFilter): enableSlowIncrease="false", increaseRate="0.05"; Created _host (Core.Host): networkConfiguration={"interfaces":{}}, usbConfiguration=""; Created _meta (Core.Meta): currency="EUR"; Created _power (Ess.Power): d="0.1", debugMode="false", enablePid="true", i="0.3", p="0.3", strategy="OPTIMIZE_BY_MOVI..., symmetricMode="true"; Created _sum (Core.Sum): ignoreStateComponents=[""]; Created batterysimulated (Simulator.Battery): alias="", capacityKWh="50", chargeMaxCurrent=null, chargeMaxVoltage=null, disChargeMaxCurrent=null, disChargeMinVoltage=null, enabled="true", minCellVoltage.mV="3300", numberOfSlaves="10", soc="50", soh="95", temperature="30", voltage="700"; Created cntrlIO (Simulator.IO.DigitalInputOutput): alias="", enabled=false, numberOfOutputs="1"; Created ctrlApiModbusTcp0 (Controller.Api.ModbusTcp.ReadOnly): alias="", component.ids=["_sum"], enabled="true", maxConcurrentConnections="5", port="502"; Created ctrlApiRest0 (Controller.Api.Rest.ReadOnly): alias="", connectionlimit="5", debugMode="true", enabled="true", port="8084"; Created ctrlBackend0 (Controller.Api.Backend): aggregationPriority="MEDIUM", alias="", apiTimeout=60, apikey="xxx", debugMode=true, enabled=true, persistencePriority="VERY_LOW", proxyAddress="", proxyPort=0, proxyType="HTTP", resendPriority="VERY_LOW", uri="ws://10.1.1.1:80...; Created ctrlDebugLog0 (Controller.Debug.Log): additionalChannels=[""], alias="", condensedOutput=false, enabled="true", ignoreComponents=[""], showAlias="false"; Created ctrlDetailedLog0 (Controller.Debug.DetailedLog): alias="", component.ids=["loadshedding0"], enabled="true"; Created ctrlIoFixABBRelay0 (Controller.Io.FixDigitalOutput): alias="", enabled=false, isOn="false", outputChannelAddress="ioAbb0/Relay1"; Created ctrlIoFixDigitalOutput0 (Controller.Io.FixDigitalOutput): alias="", enabled=false, isOn="false", outputChannelAddress="io0/Relay1"; Created ctrlIoFixloadshedIO (Controller.Io.FixDigitalOutput): alias="", enabled=false, isOn=true, outputChannelAddress="io0/InputOutput0...; Created datasource0 (Simulator.Datasource.CSV.Predefined): alias="", enabled=false, factor="10000.0", format="ENGLISH", source="H0_HOUSEHOLD_SUM..., timeDelta="-1"; Created datasourcebattery0 (Simulator.Datasource.CSV.Predefined): alias="", enabled=false, factor=1.0, format="GERMAN_EXCEL", source="ZERO", timeDelta="-1"; Created influx0 (Timedata.InfluxDB): alias="", apiKey="8yFipL-6I4gfTfp3..., bucket="dummyData", enabled=false, isReadOnly=false, maxQueueSize=5000, measurement="data", noOfCycles=1, org="ems", queryLanguage="FLUX", url="http://10.1.1.1:...; Created io.openems.edge.dnp3 (io.openems.edge.dnp3): alias="", enabled="true"; Created io0 (Simulator.IO.DigitalInputOutput): alias="", enabled=false, numberOfOutputs="1"; Created loadshedding0 (Loadshedding.Esp): alias="", enabled=true, esp.id="westerncape-8-ko..., key="F75A90D6-8AF2448..., start.time=1695064059, test=false; Created meter0 (Simulator.GridMeter.Acting): alias="", datasource.id="datasource0", enabled=false; Created modbus0 (Bridge.Modbus.Serial): alias="", baudRate="9600", databits="8", enabled=false, invalidateElementsAfterReadErrors="1", logVerbosity="NONE", parity="NONE", portName="COM3", stopbits="ONE"; Created rrd4j0 (Timedata.Rrd4j): alias="", debugMode=false, enabled=true, isReadOnly=false, persistencePriority="MEDIUM"; Created scheduler0 (Scheduler.AllAlphabetically): alias="", controllers.ids=[""], enabled="true";
Sep 26 20:22:36 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:36,288 [socket-2] INFO  [s.backend.common.metadata.Edge] Edge [edge0]: Update version from [0.0.0] to [2023.10.0-SNAPSHOT]
Sep 26 20:22:43 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:43,688 [thread-1] INFO  [socket.AbstractWebsocketServer] [Edge.Websocket] [monitor] Connections: 1, Pool: 10/10, Pending: 0, Completed: 10, Active: 0
Sep 26 20:22:43 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:43,720 [thread-1] INFO  [hared.influxdb.InfluxConnector] [InfluxDB] [monitor] timedata0 Pool: 0/10, Pending: 0, Completed: 0, Active: 0, MergePointsWorker[data/rp_avg: 0/1000000, data/rp_max: 0/1000000, Default: 0/1000000], Limit:0.000, RejectedExecutions:0
Sep 26 20:22:53 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:53,689 [thread-1] INFO  [socket.AbstractWebsocketServer] [Edge.Websocket] [monitor] Connections: 1, Pool: 10/10, Pending: 0, Completed: 20, Active: 0
Sep 26 20:22:53 ip-172-31-29-181 java[142107]: 2023-09-26T20:22:53,721 [thread-1] INFO  [hared.influxdb.InfluxConnector] [InfluxDB] [monitor] timedata0 Pool: 0/10, Pending: 0, Completed: 0, Active: 0, MergePointsWorker[data/rp_avg: 0/1000000, data/rp_max: 0/1000000, Default: 0/1000000], Limit:0.000, RejectedExecutions:0
Sep 26 20:23:03 ip-172-31-29-181 java[142107]: 2023-09-26T20:23:03,690 [thread-1] INFO  [socket.AbstractWebsocketServer] [Edge.Websocket] [monitor] Connections: 1, Pool: 10/10, Pending: 0, Completed: 30, Active: 0

I did test a normal influxdb connection from my edge device to server and all is working fine, so the issue is between the backend and the db on the server.

The only error that is being generated every now and again is the edge device:
io.openems.common.exceptions.OpenemsError$OpenemsNamedException: No AppCenter Metadata provided.
I am not sure if this is the key to what I am missing here?

Please let me know your thoughts, I am keen to get this up and running!

Cheers,

Bart