Storing device events into PostgreSQL database

That might indeed be a next integration :wink: I do not have any experience yet with this extension, if you would like to collaborate that would be great!

1 Like

There will be one additional field added to the SQL schemas, which will be tags (hstore) field. It will also be possible to assign additional (user-defined) meta-data to devices in the next LoRa App Server release. These tags can then be used for aggregation (or potentially in the future for filtering in the web-interface / API or deploying a FUOTA update to a sub-set of devices).

2 Likes

Hi brocaar,

Do you know when will you release this code? I am eagerly waiting for this.

Regards
Jayesh

Hi @brocaar
This is the great idea to track the device and it’s status!

I hope that the data can be directly written to postgresql as soon as possible. I have installed the postgresql database on one of my other machines;

Please see https://forum.loraserver.io/t/release-lora-app-server-v3-1-test-release/5025. I’m looking forward to feedback!

3 Likes

Hi @brocaar,

It’s possible for you to share lora-app-server.toml example for configure PostgreSQL integration in Loraserver ?

Thanks,

Thomas

1 Like

Here is an example:

[application_server.integration]
enabled=["mqtt", "postgresql"]

[application_server.integration.mqtt]
server="tcp://localhost:1883"

[application_server.integration.postgresql]
dsn="postgres://loraserver_as:loraserver_as@localhost/loraserver_as_events?sslmode=disable"

Hello, I modified the configuration file according to your example. As a result, the original system web IP 8080 port page could not be opened, and the modified part was deleted again. The port 8080 web page returned to normal. I do not know why. The original Loraserver system is 3.01, and other configurations of the POSTGRESQL database have been upgraded according to TEST3.1. But did not find postgresql integration. Only the previous http and influxdb integration options. The current 3.01 version is working fine.

Have you had the time to test this?

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.

1 Like

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

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:

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.

7 Likes

Hi @bryansmith
Thanks a lot !!!

I test the integration on the next week.

:slight_smile:

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

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

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

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.

1 Like

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

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