Storing device events into PostgreSQL database

[quote=“GustavoLema1988, post:41, topic:4724, full:true”]
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

hello @bryansmith

the file that I believe you with the code
lora-app-server configuration file> lora-app-server.toml.
it is like this with the point at the end,
or so

lora-app-server configuration file> lora-app-server.toml

no point as shown in the loraserver.io document

You are correct Gustavo. I was using proper punctuation. I changed my post and made it a block quote so that no one else is mistaken. Thanks

@brocaar @bryansmith @GustavoLema1988
Hello everyone, after I configured the lora-app-server.toml file, I still can’t see the postgesql integration option. I didn’t find the reason. I reinstalled the latest components of the ubuntu operating system and the latest loraserver server. But still not successful.
1:
Application_server.integration]
Enabled=[“mqtt”, “postgresql”]
If I join postgresql, then lora-app-server fails to open web 8080 port, delete postgreql will open normally, I don’t know why?
2:
[application_server.integration.mqtt]
Server=“tcp://localhost:1883”
The second item has not changed since the originally installed configuration file has the option configuration of this port 1883 by default.
3:
[application_server.integration.postgresql]
Dsn=“postgres://loraserver_as:loraserver_as@localhost/loraserver_as_events?sslmode=disable”
My postgresql database server is also configured on another computer, for example: dsn=“postgres://usernamdb:mypassword@192.168.1.15/loraserver_as_events?sslmode=disable”

Other creations of database and table names, etc., are based on the default and default values ​​recommended by the author.
Same as the screenshot in front of me.
I don’t know why the postgrsql integration option still does not appear? Can anyone answer me? First, thank you.

The answer is here in this post:

@bryansmith
Thank you very much for your reply: 1: That is to say, even if the configuration is correct under the integration project option, you can’t see the postgresql option? 2: According to the author’s instructions, why do I join the Enabled = [“mqtt”, “postgresql”] character, the original normal website page 8080 port can not open? Thanks again.

In English please :grinning:

@bryansmith
Thank you very much for your reply: 1: That is to say, even if the configuration is correct under the integration project option, you can’t see the postgresql option? 2: According to the author’s instructions, why do I join the Enabled = [“mqtt”, “postgresql”] character, the original normal website page 8080 port can not open? Thanks again.

Hello @node-loraserver, my last response addressed part of your issue.

1: You will not see postgresql under the integrations drop down of your application. You define it in the configuration and lora-app-server will write events to the database that you define in the dsn.

  1. You need to be running version 3.1.0 or greater of lora-app-server. Make sure that you are running the latest version.

Please reread this post. Everything that you asked, has already been asked here and addressed here.

1 Like

Thank you for your reply, the version of the installed lora-app-server is the latest v3.1 version. Just don’t know why modify Enabled = [“mqtt”] to "Enabled = [“mqtt”, “postgresql”], after that, you can no longer open the WEB page of port 8080. After deleting postgresql characters, the web access is normal. Don’t know what the reason.

When creating the toml file with the command:
lora-app-server configfile> lora-app-server.toml
in the toml file we must also post the same name of the database as ra when we set up lora-app-server at the beginning

and the same name of the database we must put when we are going to do the integration or not

Could you help me with that?

Thank you…

I would keep the LoRa App Server database separate from the database used by the PostgreSQL integration. It does work to use one database, but it is cleaner to have a separate database.

1 Like

good afternoon I am trying to integrate postgresql with lora server but it does not happen that I always get this error please I need your help could you guide me what I could be doing wrong, and you could advise me this is my email jgustavo_lema@hotmail.com and my number 0987243812 is to present a project thanks

I have started a LoRa Server today, I am able to see packets coming through the interface. Now I would like to store it into PostgreSQL Database. I have read the whole post above, but I could not succeed.

I am running version:

lora-app-server version
3.3.1

I have created a new database and tables as stated by @brocaar in its OP.

My setup contains, the required lines:

  [application_server.integration]
  # Enabled integrations.
  enabled=["mqtt", "postgresql"]

   # PostgreSQL Integration Service
    [application_server.integration.postgresql]
    dsn="postgres://loraserver_as_events:dbpassword@localhost/loraserver_as_events?sslmode=disable"

I have restarted the lora-app-server and it does not complains, it can connect the database:

sudo journalctl -u lora-app-server.service --since="5 min ago"
-- Logs begin at Thu 2016-11-03 17:16:43 UTC, end at Thu 2019-10-17 10:48:41 UTC. --
Oct 17 10:45:00 helena systemd[1]: Stopping LoRa App Server...
Oct 17 10:45:00 helena systemd[1]: Stopped LoRa App Server.
Oct 17 10:45:00 helena systemd[1]: Started LoRa App Server.
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="starting LoRa App Server" docs="https://www.loraserver.io/" version=3.3.1
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="storage: setting up storage package"
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="storage: setting up Redis pool"
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="storage: connecting to PostgreSQL database"
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="storage: applying PostgreSQL data migrations"
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="storage: PostgreSQL data migrations applied" count=0
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="integration/mqtt: TLS config is empty"
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="integration/mqtt: connecting to mqtt broker" server="tcp://localhost:1883"
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="integration/mqtt: connected to mqtt broker"
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="integration/mqtt: subscribing to tx topic" qos=0 topic=application/+/device/+/tx
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="integration/postgresql: connecting to PostgreSQL database"
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="api/as: starting application-server api" bind="0.0.0.0:8001" ca_cert= tls_cert= tls_key=
Oct 17 10:45:00 helena lora-app-server[10889]: time="2019-10-17T10:45:00Z" level=info msg="api/external: starting api server" bind="0.0.0.0:8080" tls-cert= tls-key=
Oct 17 10:45:01 helena lora-app-server[10889]: time="2019-10-17T10:45:01Z" level=info msg="api/external: registering rest api handler and documentation endpoint" path=/api
Oct 17 10:45:01 helena lora-app-server[10889]: time="2019-10-17T10:45:01Z" level=info msg="api/js: starting join-server api" bind="0.0.0.0:8003" ca_cert= tls_cert= tls_key=
Oct 17 10:45:01 helena lora-app-server[10889]: time="2019-10-17T10:45:01Z" level=warning msg="creating insecure network-server client" server="192.168.1.16:8000"
Oct 17 10:45:01 helena lora-app-server[10889]: time="2019-10-17T10:45:01Z" level=info msg="finished client streaming call" grpc.code=OK grpc.method=StreamFrameLogsForGateway grpc.service=ns.NetworkServerService grpc.time_ms=0.204 span.kind=client system=grpc

But there is no data stored in loraserver_as_events database. What am I missing?