Set up and configuration for chirpstack, thingsboard and postgresql

Hello community,

I’ve recently set up an Ubuntu server and successfully cloned the ChirpStack Git repository from [GitHub - chirpstack/chirpstack-docker: Setup ChirpStack using Docker Compose]. Within the provided Docker Compose file, I’ve configured both ThingsBoard and Node-RED, and they’re currently operational.

Now, is it possible of establishing a separate database for ThingsBoard in PostgreSQL as we already have chirpstack database configured for PostgreSQL . If this is possible, could someone kindly guide me through the necessary configurations? Below, I’ve included the YAML file with the current configurations. Any assistance would be immensely appreciated. Thank you.

#########
version: “3”

services:
chirpstack:
image: chirpstack/chirpstack:4
command: -c /etc/chirpstack
restart: unless-stopped
volumes:
- ./configuration/chirpstack:/etc/chirpstack
- ./lorawan-devices:/opt/lorawan-devices
depends_on:
- postgres
- mosquitto
- redis
environment:
- MQTT_BROKER_HOST=mosquitto
- REDIS_HOST=redis
- POSTGRESQL_HOST=postgres
ports:
- 8080:8080

thingsboard:
image: thingsboard/tb-postgres
restart: unless-stopped
volumes:
- thingsboarddata:/data
ports:
- 9090:9090

chirpstack-gateway-bridge:
image: chirpstack/chirpstack-gateway-bridge:4
restart: unless-stopped
ports:
- 1700:1700/udp
volumes:
- ./configuration/chirpstack-gateway-bridge:/etc/chirpstack-gateway-bridge
environment:
- INTEGRATION__MQTT__EVENT_TOPIC_TEMPLATE=eu868/gateway/{{ .GatewayID }}/event/{{ .EventType }}
- INTEGRATION__MQTT__STATE_TOPIC_TEMPLATE=eu868/gateway/{{ .GatewayID }}/state/{{ .StateType }}
- INTEGRATION__MQTT__COMMAND_TOPIC_TEMPLATE=eu868/gateway/{{ .GatewayID }}/command/#
depends_on:
- mosquitto

chirpstack-gateway-bridge-basicstation:
image: chirpstack/chirpstack-gateway-bridge:4
restart: unless-stopped
command: -c /etc/chirpstack-gateway-bridge/chirpstack-gateway-bridge-basicstation-eu868.toml
ports:
- 3001:3001
volumes:
- ./configuration/chirpstack-gateway-bridge:/etc/chirpstack-gateway-bridge
depends_on:
- mosquitto

chirpstack-rest-api:
image: chirpstack/chirpstack-rest-api:4
restart: unless-stopped
command: --server chirpstack:8080 --bind 0.0.0.0:8090 --insecure
ports:
- 8090:8090
depends_on:
- chirpstack

postgres:
image: postgres:14-alpine
restart: unless-stopped
volumes:
- ./configuration/postgresql/initdb:/docker-entrypoint-initdb.d
- postgresqldata:/var/lib/postgresql/data
environment:
- POSTGRES_PASSWORD=root

redis:
image: redis:7-alpine
restart: unless-stopped
command: redis-server --save 300 1 --save 60 100 --appendonly no
volumes:
- redisdata:/data

mosquitto:
image: eclipse-mosquitto:2
restart: unless-stopped
ports:
- 1883:1883
volumes:
- ./configuration/mosquitto/config/:/mosquitto/config/

node-red:
image: nodered/node-red
restart: unless-stopped
environment:
- TZ=Europe/Berlin
ports:
- 1880:1880
networks:
- node-red-net
volumes:
- node-red-data:/data

volumes:
postgresqldata:
redisdata:
thingsboarddata:
node-red-data:

networks:
node-red-net:

Yes,

As long as you have a another database you just have to set up the psql integration. Assuming you want the database on the same server create a new PSQL user and database (or just a database and use the chirpstack user) and then add the integration configuration to your chirpstack.toml file.

The instructions for creating a new user / database are straightforward and you can find it online. Creating user, database and adding access on PostgreSQL | by Arnav Gupta | Coding Blocks | Medium

Assuming you want the database on the same server, you will have to enter your postgres container first to execute the commands

sudo docker exec -it <postgres_container> sh

Replace <postgres_container> with the name of the container which can be found through sudo docker ps.

Then add the configuration details to your chirpstack.toml file, at the bottom there should be an [integration] section:

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

  [integration.mqtt]
    server="tcp://$MQTT_BROKER_HOST:1883/"
    json=true

  [integration.postgresql]
    dsn="postgres://<postgres_user>:<password>@$POSTGRESQL_HOST/<postgres_user>?sslmode=disable"

If your PSQL database is not on the same server change $POSTGESQL_HOST to the ip and port of the database.

Hi Liam,

Thank you for the info, i am trying to create a user and database by following the link attached by you, but i am getting the below error:

@lora-network-server:~$ sudo -u postgres psql
sudo: unknown user postgres
sudo: error initializing audit plugin sudoers_audit

Were you able to successfully enter your postgres container shell using:

sudo docker exec -it <postgres_container> sh

If you are trying to execute the PSQL commands from outside the container they will fail.

Hey,

i have created a seprate database for thingsboard:

/ # psql -U postgres
psql (14.11)
Type "help" for help.

postgres=# \l
                                   List of databases
    Name     |    Owner    | Encoding |  Collate   |   Ctype    |   Access privileges
-------------+-------------+----------+------------+------------+-----------------------
 chirpstack  | chirpstack  | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres    | postgres    | UTF8     | en_US.utf8 | en_US.utf8 |
 template0   | postgres    | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
             |             |          |            |            | postgres=CTc/postgres
 template1   | postgres    | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
             |             |          |            |            | postgres=CTc/postgres
 thingsboard | thingsboard | UTF8     | en_US.utf8 | en_US.utf8 |
(5 rows)

then i did the configuration in .toml file

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

  [integration.mqtt]
    server="tcp://$MQTT_BROKER_HOST:1883/"
    json=true

  [integration.postgresql]
  dsn="postgres://thingsboard:thingsboard@$POSTGRESQL_HOST/thingsboard?sslmode=disable"

I am getting the following error:

chirpstack-docker-postgres-1                                | 2024-05-08 11:22:43.582 UTC [132] FATAL:  password authentication failed for user "postgres"
chirpstack-docker-postgres-1                                | 2024-05-08 11:22:43.582 UTC [132] DETAIL:  Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
chirpstack-docker-thingsboard-1                             | Starting ThingsBoard Installation...
chirpstack-docker-thingsboard-1                             | Installing DataBase schema for entities...
chirpstack-docker-thingsboard-1                             | Installing SQL DataBase schema part: schema-entities.sql
chirpstack-docker-postgres-1                                | 2024-05-08 11:22:54.047 UTC [133] FATAL:  password authentication failed for user "postgres"
chirpstack-docker-postgres-1                                | 2024-05-08 11:22:54.047 UTC [133] DETAIL:  Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
chirpstack-docker-thingsboard-1                             | Unexpected error during ThingsBoard installation!
chirpstack-docker-thingsboard-1                             | org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"

Below is the snippet of docker compose file:

  thingsboard:
    restart: always
    image: thingsboard/tb-pe:3.6.4PE
    ports:
      - 9090:9090
    environment:
      TB_QUEUE_TYPE: in-memory
      SPRING_DATASOURCE_URL: jdbc:postgresql://postgres:5432/thingsboard
      TB_LICENSE_SECRET: ksjbfejdnfjksnxxxxxxxxx
      TB_LICENSE_INSTANCE_DATA_FILE: /data/license.data
    volumes:
      - thingsboard-data:/data
      - thingsboard-logs:/var/log/thingsboard

  postgres:
    image: postgres:14-alpine
    restart: unless-stopped
    volumes:
      - ./configuration/postgresql/initdb:/docker-entrypoint-initdb.d
      - postgresqldata:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=root

What could be the problem over here?

I am not an expert in PSQL and I have never used thingsboard, but it seems like thingsboard is either using the wrong username/password or trying to use sha-256 and perhaps this is not supported by the postgres container? Are there any other configuration files along with the thingsboard setup? Where do you specify the username/password it connects with?

hey,
i am not getting the authentication error anymore, instead i am encountering below error.

Those are all thingsboard errors, I cannot help you with those. Although it looks like it maybe is not being properly set up on a docker networks, or else you just have the user/pass wrong to connect.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.