Storing device events into PostgreSQL database

brocaar · July 3, 2019, 9:58am

Have you had the time to test this?

bryansmith · July 3, 2019, 7:17pm

Postgres integration works fine for me. I just recreated the configfile with

lora-app-server configfile > lora-app-server.toml

Afterwards I added postgresql to the enabled integrations, defined the database in the dns, created the necessary database and tables then restarted lora-app-server.

Works like a charm.

Thomas_Potherat · July 4, 2019, 7:11am

Hi @bryansmith it’s possible for you to share command line for create table in new database ?

You used the loraserver_as database or you are create a new database ?

Thanks
Thomas

bryansmith · July 4, 2019, 2:34pm

Hello Thomas,

Sure I can help you with that.

I created a new database just for my lora data storage but I used the same loraserver_as user:

sudo -u postgres psql

Create the lorastore database with owner loraserver_as:

create database lorastore with owner loraserver_as;

I then inserted all of the suggested tables and indices outlined by .@brocaar in a file. Here are his suggestions just for reference:
https://github.com/brocaar/lora-app-server/blob/master/docs/content/integrate/sending-receiving/postgresql.md

To save you some time, I’ve recreated that here in a database dump for you:

https://pastebin.com/raw/LzsGb8b9

Download that file then just import it into the database:
psql lorastore < LzsGb8b9

Now you just need to enable the integration:

[application_server.integration]
enabled=[“mqtt”, “postgresql”]

Add the dsn:

[application_server.integration.postgresql]
dsn=“postgres://loraserver_as:YourPasswordHere@localhost/lorastore?sslmode=disable”

Restart the lora-app-server and you’re ready to go. You should see the integration connecting or failing to connect in the logs.

Thomas_Potherat · July 4, 2019, 9:17pm

Hi @bryansmith
Thanks a lot !!!

I test the integration on the next week.

GustavoLema1988 · July 9, 2019, 7:54pm

I can not find [application_server.integration.postgresql]

can you tell me where I should put that command to use postgresql since I get an error when restarting lora-app-server

bryansmith · July 9, 2019, 7:58pm

Which version of the lora-app-server are you running? It believe it needs to be either 3.1.0-test.1 or 3.1

GustavoLema1988 · July 9, 2019, 9:54pm

hello @bryansmith

I was following these steps of the tutorial of this video https://www.youtube.com/watch?v=FnTP7t47DlI
and then I really do not know what version it is but I was configuring in lora-app-server what is the integration with postgresql and I made the changes as you said to me in this way:

[application_server.integration]
enabled = [“mqtt”, “postgresql”]

[application_server.integration.postgresql]
dsn = “postgres: // loraserver_as: dbpassword @ localhost / lorastore? sslmode = disable”

also create a database with the same user as you had done with this name loraserver_as such as your code

create database lorastore with owner loraserver_as;

I do not know if you could help me more or less how I can integrate Lora with Postgresql since I am developing a project to measure the water consumption in homes since with influence the tables are created automatically but I do not all appear where and using two Lopy4 as nanogateway and node using abp mode

bryansmith · July 9, 2019, 10:41pm

When did you start doing that? Lora-app-server version 3.1.0 was released yesterday so try this:

sudo apt-get update
sudo apt-get install lora-app-server

They should install the latest version which you can verify by typing:

lora-app-server version

I’m doing several Loraserver videos as well but truthfully you really need to read the documentation. I got this integration going within 5 minutes and you can as well.

It’s great that you’re measuring in home water consumption. I hope you get everything up and running smoothly with lopy4 and loraserver.

GustavoLema1988 · July 10, 2019, 1:27am

thanks for your answer @bryansmith

the part of the configuration with postgresql as it would be because I have reviewed the documentation itself and I have made all the changes and then I do not know what I am really failing I put the command version lora-app-server but it does not work since if I could enter postgresql and I created the tables according to how it says @ brocaar

bryansmith · July 10, 2019, 1:30am

You run this command in the terminal, it has to work

GustavoLema1988 · July 10, 2019, 3:03am

I should also update what it is:
lora-gateway-bridge
loraserver

since I already updated you lora-app-server

GustavoLema1988 · July 10, 2019, 2:45pm

I managed to configure the with postgresql in the file:

nano lora-app-server.toml.

but another file appeared when I updated it

nano lora-app-server.toml

I will load the code into toml.

but when I go to the website of lora server only this integration appears more
HTTP Integration
Influxdb integration

I do not get postgresql

what could be the problem nose if they could help me since the version of the lora-app-server is 3.1

bryansmith · July 10, 2019, 7:11pm

@GustavoLema1988 I think you have a slight misunderstanding.

The postgresql integration isn’t a per application level integration.

ALL of the lora related events will be logged to the postrgres database that you define in the lora-app-server.toml

If you followed the instructions appropriately your data is currently being written to your postgresql database. This is all happening in the background. Look at your database and you should see events being logged.

GustavoLema1988 · July 10, 2019, 8:47pm

thanks for the information @bryansmith

one more query, the moment to add the integration with postgresql it is possible to integrate it with grafana to visualize the data that arrive from the sensors, since I am using as an example the LM35 where the data arrive like:

Value: 12 ° C

since when integrating lora server with influxdb I was created automatically the tables and could make queries of two tables like:

device_frm_payload_data_temperature
device_uplink

I want to see more tables to be able to make queries, where I see that postgresql allows creating tables where I think we can visualize:

uplink
device status
join
ack
*error
*Location

I do not know if I’m wrong with that, they recommend me to integrate lora server with influxdb or with postgresql, since my work is based on measuring water consumption

Thank you…

brocaar · July 11, 2019, 7:16am

This works fine with the current tables You can select from the object field, which is a jsonb type field. For documentation, see: https://www.postgresql.org/docs/current/functions-json.html.

GustavoLema1988 · July 11, 2019, 5:08pm

Thanks for your help @brocaar

then it is possible to integrate postgresql with grafana because in grafana there is a tab that says postgresql as influxdb but that is done automatically when choosing postgresql the values read by the sensors would pass to grafana when I choose postgresql or should do some command nose if you could help me with that ?

brocaar · July 12, 2019, 8:44am

What you need to do is:

Configure PostgreSQL as a Grafana data-source
Use that data-source and write a SQL query, which will then be visualized by Grafana

node-loraserver · July 12, 2019, 10:47am

Like the questions of the friends above, I follow the configuration of the routines.
[application_server.integration]
Enabled = [“mqtt”, “postgresql”]
Cannot open the web page of port 8080.
My version is also the latest lora-app-server 3.1
If you remove “postgresql”, it will be normal again. But there is always no postgresql integration option, only three integration options in the picture.
Modified lora-app-server.toml configuration

GustavoLema1988 · July 12, 2019, 5:57pm

thanks for the information @brocaar

but I do not know what happened but now I get this in portal-gateway-bridge already reboots but when I see the status of the server returns to this error and the same happens with loraserver that could be wrong

12 12:50:23 iot-Satellite-S55-A lora-gateway-bridge[27369]: time=“2019-07-12T12:50:23-05:00” level=error msg=“apply gateway-configuration error” error=“gateway does not exist”
jul 12 12:50:24 iot-Satellite-S55-A lora-gateway-bridge[27369]: time=“2019-07-12T12:50:24-05:00” level=info msg=“integration/mqtt: publishing event” event=up qos=0 topic=gateway/30aea4fffe786954/event/up
jul 12 12:50:24 iot-Satellite-S55-A lora-gateway-bridge[27369]: time=“2019-07-12T12:50:24-05:00” level=info msg=“integration/mqtt: downlink frame received” topic=gateway/30aea4fffe786954/command/down
jul 12 12:50:24 iot-Satellite-S55-A lora-gateway-bridge[27369]: time=“2019-07-12T12:50:24-05:00” level=error msg=“backend/semtechudp: could not handle packet” addr=“192.168.0.9:49154” data_base64="AhzrBDCupP/+eG
jul 12 12:50:35 iot-Satellite-S55-A lora-gateway-bridge[27369]: time=“2019-07-12T12:50:35-05:00” level=info msg=“integration/mqtt: publishing event” event=up qos=0 topic=gateway/30aea4fffe786954/event/up
jul 12 12:50:35 iot-Satellite-S55-A lora-gateway-bridge[27369]: time=“2019-07-12T12:50:35-05:00” level=info msg=“integration/mqtt: downlink frame received” topic=gateway/30aea4fffe786954/command/down
jul 12 12:50:35 iot-Satellite-S55-A lora-gateway-bridge[27369]: time=“2019-07-12T12:50:35-05:00” level=error msg=“backend/semtechudp: could not handle packet” addr=“192.168.0.9:49154” data_base64="AtxSBDCupP/+eG
jul 12 12:50:46 iot-Satellite-S55-A lora-gateway-bridge[27369]: time=“2019-07-12T12:50:46-05:00” level=info msg=“integration/mqtt: publishing event” event=up qos=0 topic=gateway/30aea4fffe786954/event/up
jul 12 12:50:46 iot-Satellite-S55-A lora-gateway-bridge[27369]: time=“2019-07-12T12:50:46-05:00” level=info msg=“integration/mqtt: downlink frame received” topic=gateway/30aea4fffe786954/command/down
jul 12 12:50:46 iot-Satellite-S55-A lora-gateway-bridge[27369]: time=“2019-07-12T12:50:46-05:00” level=error msg=“backend/semtechudp: could not handle packet” addr=“192.168.0.9:49154” data_base64="Aq9qBDCupP/+eG
~
~
maybe you could help me with this