Storing device events into PostgreSQL database

howdy,

not sure what I got wrong here, i am getting this error in the logs

Jan 21 21:41:02 rak-gateway chirpstack-application-server[3268]: time=“2020-01-21T21:41:02Z” level=error msg=“integration/multi: integration error” ctx_id=df35ab4e-b9f7-40fe-97cb-c69764091458 error=“insert error: pq: unsupported Unicode escape sequence” integration="*postgresql.Integration"

in my config file I got:

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

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

any idea?

guess it was posted on the forum before, don’t see a solution for it though
here: Insert error: pq: unsupported Unicode escape sequence
and here: Integration Application Server with PostgreSQL

ok problem identified, there was a payload codec function my friend put in for this device-profile that created some illegal characters so inserting into postgresql was failing. for now I removed the decoder code and it is working now.

the issue was found in the postgresql logs /var/log/postgresql/postgresql-9.6-main.log

so move on, nothing here to see :wink:

3 Likes

Thanks for the integration,

Whether it is possible for each application to have its own table or for each device to have its own table?

How do you think this integration will work, with a large number of devices such as 20,000 pieces?

Hello jlandercy, did you finally get the data in the table? im having the same issue: 0 rows

Can you store downlinks to?

@brocaar can you give me a flash about this?

No, the PostgreSQL integration stores uplink related events only. However, it is going to store txack events in the next release (acknowledgement that a downlink was accepted by the gateway for downlink transmission).

I am getting the following error. I have followed all the steps in PostgreSQL - ChirpStack open-source LoRaWAN<sup>®</sup> Network Server

Initially, I have not created tables and index
Later I have created the tables as per the description of this ticket. Then also I was getting the same error

What am I missing here? @brocaar

time=“2021-11-01T11:46:11Z” level=error msg=“integration/multi: integration error” ctx_id=ac4504a4-bd17-491d-96d9-3d5c7f068203 error=“insert error: pq: column "tags" of relation "device_up" does not exist” integration=“*postgresql.Integration”

Hi brocaar! I have a simple but vital question to continue with the integration. We have been able to get the integration with postgreSQL up and running. In the event_up table we can see each message that the devices send but we see the DATA field with the Base64 encoded information. Our intention is to share the data with an external system connected to Postgre but that the information is not encoded. What do you recommend?

If you have configured a payload coded, you will find the decoded data in the object column.

So this change is for ChirpStack v3?
Does ChirpStack v4 have this feature?

Thanks a lot and have a nice weekend.

Yes, this feature is also in v4 present.

1 Like

have you changed the schema though in v4? I saw in postgres logs some insert statements on event_up table but i created the device_up as you had in your example

Thanks!

The ChirpStack v4 data-base schema is different from v3. Please find the v4 schema for the integration here (it is automatically applied):

1 Like

Hello,
I created the chirpstack_integration database and can successfully view all uplink data from my device. Is there a way to clear all data to start fresh without causing any errors? Alternatively, is it possible to reset the data from a specific date?
Thank you in advance.

In this end, it is just a PostgreSQL database. E.g. you can drop it and re-create it if you want to start with a new database. Or you can delete records based on a where clause (SQL query).

1 Like