Storing device events into PostgreSQL database

In the next LoRa App Server release a new integration will be added which makes it possible to store device events (up, ack, join, location, error) into a PostgreSQL database (which does not have to be the same database as LoRa App Server is using).

This will (initially) be a global integration, meaning it will be configured in lora-app-server.toml and will therefore apply to all devices on the network. I’m not sure if it is a good idea to make this configurable on the device-profile level, as these

This should be usable as a PostgreSQL + Grafana combination for data visualization
In the current test implementation I have defined the following DB schema’s and I’m looking forward to feedback on this:


create table device_up (
	id uuid primary key,
	received_at timestamp with time zone not null,
	dev_eui bytea not null,
	device_name varchar(100) not null,
	application_id bigint not null,
	application_name varchar(100) not null,
	frequency bigint not null,
	dr smallint not null,
	adr boolean not null,
	f_cnt bigint not null,
	f_port smallint not null,
	data bytea not null,
	rx_info jsonb not null,
	object jsonb not null
);

-- NOTE: These are recommended indices, depending on how this table is being
-- used, you might want to change these.
create index idx_device_up_received_at on device_up(received_at);
create index idx_device_up_dev_eui on device_up(dev_eui);
create index idx_device_up_application_id on device_up(application_id);
create index idx_device_up_frequency on device_up(frequency);
create index idx_device_up_dr on device_up(dr);
create table device_status (
	id uuid primary key,
	received_at timestamp with time zone not null,
	dev_eui bytea not null,
	device_name varchar(100) not null,
	application_id bigint not null,
	application_name varchar(100) not null,
	margin smallint not null,
	external_power_source boolean not null,
	battery_level_unavailable boolean not null,
	battery_level numeric(5, 2) not null
);

-- NOTE: These are recommended indices, depending on how this table is being
-- used, you might want to change these.
create index idx_device_status_received_at on device_status(received_at);
create index idx_device_status_dev_eui on device_status(dev_eui);
create index idx_device_status_application_id on device_status(application_id);
create table device_join (
	id uuid primary key,
	received_at timestamp with time zone not null,
	dev_eui bytea not null,
	device_name varchar(100) not null,
	application_id bigint not null,
	application_name varchar(100) not null,
	dev_addr bytea not null
);

-- NOTE: These are recommended indices, depending on how this table is being
-- used, you might want to change these.
create index idx_device_join_received_at on device_join(received_at);
create index idx_device_join_dev_eui on device_join(dev_eui);
create index idx_device_join_application_id on device_join(application_id);
create table device_ack (
	id uuid primary key,
	received_at timestamp with time zone not null,
	dev_eui bytea not null,
	device_name varchar(100) not null,
	application_id bigint not null,
	application_name varchar(100) not null,
	acknowledged boolean not null,
	f_cnt bigint not null
);

-- NOTE: These are recommended indices, depending on how this table is being
-- used, you might want to change these.
create index idx_device_ack_received_at on device_ack(received_at);
create index idx_device_ack_dev_eui on device_ack(dev_eui);
create index idx_device_ack_application_id on device_ack(application_id);
create table device_error (
	id uuid primary key,
	received_at timestamp with time zone not null,
	dev_eui bytea not null,
	device_name varchar(100) not null,
	application_id bigint not null,
	application_name varchar(100) not null,
	type varchar(100) not null,
	error text not null,
	f_cnt bigint not null
);

-- NOTE: These are recommended indices, depending on how this table is being
-- used, you might want to change these.
create index idx_device_error_received_at on device_error(received_at);
create index idx_device_error_dev_eui on device_error(dev_eui);
create index idx_device_error_application_id on device_error(application_id);
create table device_location (
	id uuid primary key,
	received_at timestamp with time zone not null,
	dev_eui bytea not null,
	device_name varchar(100) not null,
	application_id bigint not null,
	application_name varchar(100) not null,
	altitude double precision not null,
	latitude double precision not null,
	longitude double precision not null,
	geohash varchar(12) not null,

	-- this field is currently not populated
	accuracy smallint not null
);

-- NOTE: These are recommended indices, depending on how this table is being
-- used, you might want to change these.
create index idx_device_location_received_at on device_location(received_at);
create index idx_device_location_dev_eui on device_location(dev_eui);
create index idx_device_location_application_id on device_location(application_id);

4 Likes

The integration will not enabled by default, I assume?

Correct, it would work like the other integrations that can be enabled through lora-app-server.toml.

1 Like

Thanks for the info. I have no concerns on the database schema, the only thing I would suggest is that from a design perspective, it’s probably worth putting in giant letters in the docs that this is an easy way to generate a lot of data and the user should be prepared to manage that, especially if they have a lot of devices. Or set a default duration that data is removed after.

Certain vendors of proprietary network servers may have surprised certain paying customers with this type of behavior as the default…

1 Like

Hello @brocaar,

could you tell me what is the pros and cons of this new DB integration compared to influxDB?

Thx.

It depends per use-case. E.g. it is one less component to worry about, as PostgreSQL is already required by LoRa App Server so you don’t have to install InfluxDB. Also it could provide more flexibility as you can write more advanced SQL queries compared to InfluxDB. It could also be used as an intermediate storage which is used by applications to fetch (and then remove) data from. On the other hand, InfluxDB is written specific for time-series data and depending your use-case, you might find features in InfluxDB that are not present in PostgreSQL.

2 Likes

Thank you for these explanations, really perfect.

@brocaar

in device_status, I was thinking adding ‘lastSeen’ would be useful.

Is it possible to test the Beta version?

The device-status is written when the device reports a device-status :slight_smile: Thus received_at would equal lastSeen in this case.

@Silvano_Pisoni not yet, but this will be made available soon.

I would also add support for Timescale PG extension. This would allow for an optimized way to store and query time-series data.

2 Likes

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.