Hello.
I am running everything (redis, postgres, mqtt, chirpstack, chirpstack-gateway-bridge) from docker containers. If I go to Chirpstack I see my gateway and 2 nodes and all looks good. I have begun to play with postgresql but know little about it. I logged into
sudo -u postgres psql
and performed a list then did a \c chirpstack …
I can list the fields. but when I try and list the rows I get 0.
Can someone tell me 1. what should I see here? and 2. why don’t I see anything?
I followed the steps and modified chirpstack.toml according to :
https://www.chirpstack.io/docs/chirpstack/integrations/postgresql.html
I have in my toml file:
[integration.postgresql]
dsn="postgres://chirpstack:chirpstack@$POSTGRESQL_HOST/chirpstack?sslmode=disable"
given the above url suggests:
-- create role for authentication
create role chirpstack_integration with login password 'chirpstack_integration';
-- create database
create database chirpstack_integration with owner chirpstack_integration;
-- exit psql
\q
Do I have the right username and password in toml? Toml came with this already listed under the section so I just copied it???
[postgresql]
Thanks
Something is wrong with the permission I guess.
The owner is chirpstack_integration
And what is the permission for user chirpstack?
This is the basic knowledge of postgres. You may take a look at the user/role at postgres webpages or other pages.
Rest assure that ChirpStack does the right thing.
@IoTThinks …thanks for the link to roles as that is very helpful. I still am having issues. I follow the directions at the link:
https://www.chirpstack.io/docs/chirpstack/integrations/postgresql.html
Per the recommendation I added the [integration.postgresql] section in chirpstack.toml:
[integration]
enabled=["mqtt", "postgresql"]
[integration.mqtt]
server="tcp://$MQTT_BROKER_HOST:183/"
username="$MQTT_UNAME"
password="$MQTT_PWD"
json=true
[integration.postgresql]
dsn="postgres://chirpstack:chirpstack@$POSTGRESQL_HOST/chirpstack?sslmode=disable"
This line was copied from a default line in the same file at the top section:
# PostgreSQL configuration.
[postgresql]
dsn="postgres://chirpstack:chirpstack@$POSTGRESQL_HOST/chirpstack?sslmode=disable"
env variable POSTGRESQL_HOST is defined in docker-compose as postgres
So being new to all this I have a working system except for the postgres piece (and HaSS) so I am not sure exactly what to change here…Do I need to have these two lines match?
Thanks
@bconway …can you help to clarify/comment on this issue as I am following your recommended github repo that you pointed me to a while back: repo
- I use the 001…sh command file in /postgresql/initdb and run it at the psql prompt to generate chirpstack db and owner
- I modified the chirpstack.toml file
[postgresql]
dsn="postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRESQL_HOST/POSTGRES_DB?sslmode=disable"
AND
[integration]
enabled=["mqtt", "postgresql"]
[integration.postgresql]
dsn="postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRESQL_HOST/POSTGRES_DB?sslmode=disable"
- I modified docker-compose.yml
postgres:
container_name: 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
- POSTGRES_USER=chirpstack
- POSTGRES_PASSWORD=chirpstack
- POSTGRES_DB=chirpstack
When I bring docker-compose up -d I get the network_app_server constantly restarting and I cannot get to the network server webpage.
The solution is to put back root as the password and remove the other environment variables for postgres within docker-compose.yml and remove the integration portion that is in chirpstack.toml. But when I do this postgres does NOT log anything. What am I missing here?
Thank you
