The PostgreSQL integration writes all device related events into a PostgreSQL database, so that it can be queried by other applications or so that it can be visualized using for example Grafana using the PostgreSQL Data Source.
If the integration is enabled, it will automatically create all database tables on startup. It is important that this integration uses its own database, to avoid schema collisions.
Create the database
Please see below an example for creating the PostgreSQL database. Depending your PostgreSQL installation, these commands might be different.
Enter the PostgreSQL as the
sudo -u postgres psql
Within the PostgreSQL prompt, enter the following queries:
-- create the chirpstack_as_events user create role chirpstack_as_events with login password 'dbpassword'; -- create the chirpstack_as_events database create database chirpstack_as_events with owner chirpstack_as_events; -- enable the hstore extension \c chirpstack_as_events create extension hstore; -- exit the prompt \q
To verify if the user and database have been setup correctly, try to connect to it:
psql -h localhost -U chirpstack_as_events -W chirpstack_as_events
Activating the integration
In order for ChirpStack to start writing event data to a Postgres database, the integration must be explicitly enabled and configured in the
chirpstack-application-server.toml configuration file.
Enabling the integration
In the file, find this section:
[application_server.integration] # Enabled integrations. enabled=["mqtt"]
enabled line may look slightly different, as you may have other integrations already active. Add
"postgresql" to the array. In this case, the modified line should appear as
You must also set the configuration settings for the integration. If your configuration file does not already contain the following section, add it now:
dns= line, modify
<database> with your appropriate credentials and targets. If you followed the example above, you would use
chirpstack_as_events as your username and target database. If your target Postgres database is on the same machine as the Application Server, use
localhost as your host.
Please see Configuration for a full configuration example.