Connected to Postgres DB but dashboard has no data

[ivoavtavares]

I’m using a grafana docker image grafana/grafana-enterprise:8.2.0.

I’m capable of adding a Postgres DB as a datasource. See picture below.

The db is another docker image for postgres:15.3-alpine.
There’s only 1 table, with 10 entries, all obeying the following schema:

id = Column(Integer, primary_key=True, index=True, nullable=False)
    title = Column(String)
    content = Column(String)
    published = Column(Boolean, server_default="TRUE")
    created_at = Column(TIMESTAMP(timezone=True), nullable=False, server_default=text("now()"))

So, created_at is a field of type time, I think…

When I try to create a dashboard in grafana, with data source as the postgres db, with the query:

When I check the logs, I see:

t=2023-05-24T15:44:58+0000 lvl=dbug msg="Querying for data source via SQL store" logger=datasources id=1 orgId=1
t=2023-05-24T15:44:58+0000 lvl=dbug msg="Processing metrics query" logger=http.server query="&{data:map[datasourceId:1 format:time_series intervalMs:60000 maxDataPoints:1070 rawSql:SELECT *\nFROM posts;\n refId:A]}"
t=2023-05-24T15:44:58+0000 lvl=eror msg="query error" logger=tsdb.postgres err="dial tcp :5432: connect: connection refused"
t=2023-05-24T15:44:58+0000 lvl=info msg="Request Completed" logger=context userId=1 orgId=1 uname=newuser method=POST path=/api/ds/query status=400 remote_addr=172.18.0.1 time_ms=1 size=248 referer="http://grafana.staged-by-discourse.com/explore?left=%5B%22now-6h%22,%22now%22,%22PostgreSQL%22,%7B%22refId%22:%22A%22,%22hide%22:true,%22format%22:%22time_series%22,%22timeColumn%22:%22time%22,%22metricColumn%22:%22none%22,%22group%22:%5B%5D,%22where%22:%5B%7B%22type%22:%22macro%22,%22name%22:%22$__timeFilter%22,%22params%22:%5B%5D%7D%5D,%22select%22:%5B%5B%7B%22type%22:%22column%22,%22params%22:%5B%22value%22%5D%7D%5D%5D,%22rawQuery%22:true,%22rawSql%22:%22SELECT%5Cn%20*%5CnFROM%5Cn%20%20posts;%5Cn%22,%22datasource%22:%22PostgreSQL%22%7D%5D&orgId=1"

There’s something amiss here…

[yosiasz]

Welcome,

you will need a separate port other than the default 5432

    ports:
      - "6543:5432"

Then use 6543 in your grafana connection to postgres

[ivoavtavares]

Hey Yosiasz,
thanks for the reply.
The first picture is of my docker compose yml file. I’m doing experiments now with the Grafana v9.5.2

Then I send the request to add the as below

But I still get the error:

logger=secrets.kvstore t=2023-05-24T17:18:44.941000167Z level=debug msg="got secret value" orgId=1 type=datasource namespace=PostgreSQL
logger=tsdb.postgres t=2023-05-24T17:18:44.941128545Z level=debug msg="Creating Postgres query endpoint"
logger=tsdb.postgres t=2023-05-24T17:18:44.94124467Z level=debug msg="Generating connection string with network host" host=
logger=tsdb.postgres t=2023-05-24T17:18:44.941257413Z level=debug msg="Postgres TLS/SSL is enabled" tlsMode=
logger=tsdb.postgres t=2023-05-24T17:18:44.941269539Z level=debug msg="Generated Postgres connection string successfully"
logger=tsdb.postgres t=2023-05-24T17:18:44.941279064Z level=debug msg="Creating engine..."
logger=tsdb.postgres t=2023-05-24T17:18:44.941332526Z level=debug msg="Engine created"
logger=tsdb.postgres t=2023-05-24T17:18:44.941376682Z level=debug msg="Successfully connected to Postgres"
logger=tsdb.postgres t=2023-05-24T17:18:44.941659154Z level=error msg="Query error" err="dial tcp :5432: connect: connection refused"

[yosiasz]

@ivoavtavares

err="dial tcp :5432: connect: connection refused"

Shows you are still trying to connect to 5432. So is this grafana issue or python issue? I am confused, as you are showing some python code.

[ivoavtavares]

Thanks I changed the body of the request for datasource api, and those error messages are no longer there.

But the main problem still exists…

{
  "request": {
    "url": "api/ds/query",
    "method": "POST",
    "data": {
      "queries": [
        {
          "refId": "A",
          "datasource": {
            "type": "postgres",
            "uid": "b0f1e8db-a528-4bce-90ec-75f769b8c421"
          },
          "rawSql": "",
          "datasourceId": 1,
          "intervalMs": 60000,
          "maxDataPoints": 1098
        }
      ],
      "range": {
        "from": "2023-05-24T12:07:17.569Z",
        "to": "2023-05-24T18:07:17.569Z",
        "raw": {
          "from": "now-6h",
          "to": "now"
        }
      },
      "from": "1684930037569",
      "to": "1684951637569"
    },
    "hideFromInspector": false
  },
  "response": {
    "message": "An error occurred within the plugin",
    "messageId": "plugin.downstreamError",
    "statusCode": 500,
    "traceID": ""
  }
}

[ivoavtavares]

Since I can’t share here a txt file, only an image, below is an excerpt of my logs. I think it has something related to the postgresql not being signed-in, or not having a valid license.
I’ve searched, and some suggest to do:
GF_ALLOW_LOADING_UNSIGNED_PLUGINS=PLUGIN-ID
not exactly sure…

logger=datasources t=2023-05-24T17:54:02.109929975Z level=debug msg="Received command to update data source" url=http://localhost:6543
logger=datasource t=2023-05-24T17:54:02.10994076Z level=debug msg="Applying default URL parsing for this data source type" type=postgres url=http://localhost:6543
logger=sqlstore.session t=2023-05-24T17:54:02.110158045Z level=debug msg="reusing existing session" transaction=true
logger=sqlstore.session t=2023-05-24T17:54:02.110305806Z level=debug msg="reusing existing session" transaction=true
logger=secrets.kvstore t=2023-05-24T17:54:02.110429295Z level=debug msg="got secret value" orgId=1 type=datasource namespace=PostgreSQL
logger=sqlstore.session t=2023-05-24T17:54:02.110464308Z level=debug msg="reusing existing session" transaction=true
logger=sqlstore.session t=2023-05-24T17:54:02.110751733Z level=debug msg="reusing existing session" transaction=true
logger=sqlstore.session t=2023-05-24T17:54:02.118173046Z level=debug msg="reusing existing session" transaction=true
logger=secrets.kvstore t=2023-05-24T17:54:02.118364428Z level=debug msg="secret value updated" orgId=1 type=datasource namespace=PostgreSQL
logger=sqlstore.transactions t=2023-05-24T17:54:02.118374321Z level=debug msg="skip committing the transaction because it belongs to a session created in the outer scope"
logger=sqlstore.transactions t=2023-05-24T17:54:02.11838223Z level=debug msg="skip committing the transaction because it belongs to a session created in the outer scope"
logger=secrets.kvstore t=2023-05-24T17:54:02.120518128Z level=debug msg="got secret value from cache" orgId=1 type=datasource namespace=PostgreSQL
logger=token t=2023-05-24T17:54:02.126817678Z level=debug msg=FeatureEnabled feature=accesscontrol.enforcement enabled=false licenseStatus=NotFound hasLicense=false hasValidLicense=false products=[]
logger=accesscontrol.service t=2023-05-24T17:54:02.126853436Z level=debug msg="using cached permissions" key=rbac-permissions-1-user-1
logger=token t=2023-05-24T17:54:02.1274777Z level=debug msg=FeatureEnabled feature=dspermissions.enforcement enabled=false licenseStatus=NotFound hasLicense=false hasValidLicense=false products=[]

(...)

logger=token t=2023-05-24T17:54:02.135417496Z level=debug msg=FeatureEnabled feature=accesscontrol.enforcement enabled=false licenseStatus=NotFound hasLicense=false hasValidLicense=false products=[]
logger=accesscontrol.service t=2023-05-24T17:54:02.13545565Z level=debug msg="using cached permissions" key=rbac-permissions-1-user-1
logger=accesscontrol.evaluator t=2023-05-24T17:54:02.135614825Z level=debug msg="matched scope" userscope=datasources:* targetscope=datasources:uid:b0f1e8db-a528-4bce-90ec-75f769b8c421
logger=secrets.kvstore t=2023-05-24T17:54:02.136234412Z level=debug msg="got secret value from cache" orgId=1 type=datasource namespace=PostgreSQL
logger=token t=2023-05-24T17:54:02.153205691Z level=debug msg=FeatureEnabled feature=accesscontrol.enforcement enabled=false licenseStatus=NotFound hasLicense=false hasValidLicense=false products=[]
logger=accesscontrol.service t=2023-05-24T17:54:02.153245565Z level=debug msg="using cached permissions" key=rbac-permissions-1-user-1
logger=datasources t=2023-05-24T17:54:02.15331345Z level=debug msg="Querying for data source via SQL store" uid=b0f1e8db-a528-4bce-90ec-75f769b8c421 orgId=1
logger=token t=2023-05-24T17:54:02.153500029Z level=debug msg=FeatureEnabled feature=dspermissions.enforcement enabled=false licenseStatus=NotFound hasLicense=false hasValidLicense=false products=[]
logger=secrets.kvstore t=2023-05-24T17:54:02.153533238Z level=debug msg="got secret value from cache" orgId=1 type=datasource namespace=PostgreSQL
logger=tsdb.postgres t=2023-05-24T17:54:02.153579314Z level=debug msg="Creating Postgres query endpoint"
logger=context userId=1 orgId=1 uname=newuser t=2023-05-24T17:54:02.153645636Z level=error msg="Plugin health check failed" error="failed to check plugin health: health check failed" remote_addr=172.28.0.1 traceID=
logger=context userId=1 orgId=1 uname=newuser t=2023-05-24T17:54:02.153674197Z level=error msg="Request Completed" method=GET path=/api/datasources/uid/b0f1e8db-a528-4bce-90ec-75f769b8c421/health status=500 remote_addr=172.28.0.1 time_ms=0 duration=687.804µs size=53 referer=http://grafana.staged-by-discourse.com/connections/your-connections/datasources/edit/b0f1e8db-a528-4bce-90ec-75f769b8c421 handler=/api/datasources/uid/:uid/health
logger=ngalert.scheduler t=2023-05-24T17:54:10.000633695Z level=debug msg="Alert rules fetched" rulesCount=0 foldersCount=0 updatedRules=0
logger=settingsprovider t=2023-05-24T17:54:15.985669254Z level=debug msg="Checking for updates"
logger=settingsprovider t=2023-05-24T17:54:15.986820494Z level=debug msg="Settings have changed" section=security
logger=settingsprovider t=2023-05-24T17:54:15.986916222Z level=debug msg="Settings have changed" section=auth.okta
logger=settingsprovider t=2023-05-24T17:54:15.986941713Z level=debug msg="Settings have changed" section=auth.gitlab
logger=settingsprovider t=2023-05-24T17:54:15.986959197Z level=debug msg="Settings have changed" section=auth.generic_oauth
logger=settingsprovider t=2023-05-24T17:54:15.986983604Z level=debug msg="Settings have changed" section=auth.grafana_com
logger=settingsprovider t=2023-05-24T17:54:15.98703496Z level=debug msg="Settings have changed" section=auth.github
logger=settingsprovider t=2023-05-24T17:54:15.987093162Z level=debug msg="Settings have changed" section=secretscan
logger=settingsprovider t=2023-05-24T17:54:15.987116375Z level=debug msg="Settings have changed" section=secrets
logger=settingsprovider t=2023-05-24T17:54:15.987185106Z level=debug msg="Settings have changed" section=remote.ruler
logger=settingsprovider t=2023-05-24T17:54:15.987235755Z level=debug msg="Settings have changed" section=white_labeling.public_dashboards
logger=settingsprovider t=2023-05-24T17:54:15.987257782Z level=debug msg="Settings have changed" section=auth.azuread
logger=settingsprovider t=2023-05-24T17:54:15.987282787Z level=debug msg="Settings have changed" section=reporting
logger=settingsprovider t=2023-05-24T17:54:15.987374759Z level=debug msg="Settings have changed" section=recorded_queries
logger=settingsprovider t=2023-05-24T17:54:15.987413349Z level=debug msg="Settings have changed" section=caching
logger=settingsprovider t=2023-05-24T17:54:15.987434707Z level=debug msg="Settings have changed" section=auth.google
logger=settingsprovider t=2023-05-24T17:54:15.987519964Z level=debug msg="Settings have changed" section=auth.grafananet
logger=settingsprovider t=2023-05-24T17:54:15.98755629Z level=debug msg="Settings have changed" section=enterprise
logger=settingsprovider t=2023-05-24T17:54:15.987656467Z level=debug msg="Validating settings updates"
logger=settingsprovider t=2023-05-24T17:54:15.987678631Z level=debug msg="Reloading settings"
logger=secrets t=2023-05-24T17:54:16.757792623Z level=debug msg="Removing expired data keys from cache..."
logger=secrets t=2023-05-24T17:54:16.757916533Z level=debug msg="Removing expired data keys from cache finished successfully"
logger=ngalert.state.manager t=2023-05-24T17:54:16.777194442Z level=debug msg="Recording state cache metrics" now=2023-05-24T17:54:16.777180563Z
logger=ngalert.multiorg.alertmanager t=2023-05-24T17:54:16.777301783Z level=debug msg="synchronizing Alertmanagers for orgs"
logger=ngalert.sender.router t=2023-05-24T17:54:16.777535587Z level=debug msg="Attempting to sync admin configs" count=0
logger=ngalert.sender.router t=2023-05-24T17:54:16.777600915Z level=debug msg="Finish of admin configuration sync"
logger=ngalert.multiorg.alertmanager t=2023-05-24T17:54:16.785850523Z level=debug msg="done synchronizing Alertmanagers for orgs"

[yosiasz]

Please post your python script. not as image though.

[ivoavtavares]

class Grafana:
    """
    Class DocString
    """
    def __init__(
        self,
        user: str | None = None,
        password: str | None = None,
        api_key: str | None = None,
        host: str = "localhost",
        port: str = "3000",
        API_DATASOURCES:str = "/api/datasources",
        API_KEYS:str = "/api/auth/keys",
        env_path:str = "./grafana/.env.local.grafana",
    ):
        if user is None or password is None:
            load_dotenv(dotenv_path = env_path)
            self.user = os.environ["GF_SECURITY_ADMIN_USER"]
            self.password = os.environ["GF_SECURITY_ADMIN_PASSWORD"]
        self.api_key = api_key
        self.host = host
        self.port = port
        self.API_DATASOURCES = API_DATASOURCES
        self.API_KEYS = API_KEYS
    @property
    def grafana_url(self):
        """
        Method DocString
        """
        return f"http://{self.user}:{self.password}@{self.host}:{self.port}"

    def create_api_key(self):
        """
        Method DocString
        """
        print(f"Creating API KEY with {self.API_KEYS}")
        auth_data = {"Name": self.user, "Role": "Admin", "Password": self.password}
        response = requests.post(
            f"{self.grafana_url+self.API_KEYS}", json=auth_data, timeout=2
        )
        if response.status_code == 200:
            print("API Key successfully created.")
            self.api_key = response.json()["key"]
            print("Writing API Key to file.")
            with open("./grafana/.env.local.grafana_api", mode="w+", encoding="utf-8") as file:
                file.write(self.api_key)
        elif response.status_code == 409:
            print("API Key already exists! Fetching existing API Key.")
            with open("./grafana/.env.local.grafana_api", mode="r+", encoding="utf-8") as file:
                self.api_key = file.readline()
        else:
            print(response.content)

    def add_database_source(self, database: PostgresDB):
        """
        Method DocString
        """
        datasource = {
            "name": "PostgreSQL",
            "type": "postgres",
            "host": f"http://{database.config.host}",
            "database": database.config.database,
            "user": database.config.user,
            "password": database.config.password,
            "access": "proxy",
            "port" : "6543"
        }
        headers = {
            "Content-Type": "application/json",
            "Authorization": f"Bearer {self.api_key}",
        }
        response = requests.post(
            self.grafana_url + self.API_DATASOURCES,
            json=datasource,
            headers=headers,
            timeout=2,
        )

        if response.status_code == 200:
            print(
                """
                Data Source Creation of PostgreSQL was successfull.
                """
            )
        elif response.status_code == 409:  # this key has already been created
            print(
                """
                Data Source Creation of PostgreSQL already exists
                """
            )
        else:
                print(response.content)


grafana = Grafana()
    grafana.create_api_key()
    postgres_db = PostgresDB(filename="./db/database.ini", section="postgresql")
    grafana.add_database_source(postgres_db)

[yosiasz]

So you have posted above a wall of logs. Please point out the specific error you are dealing with.

[ivoavtavares]

Sorry about that Yosiasz!
I’ve edited the above post…

The main error now is this, I think…

logger=context userId=1 orgId=1 uname=newuser t=2023-05-24T17:54:02.153645636Z level=error msg="Plugin health check failed" error="failed to check plugin health: health check failed" remote_addr=172.28.0.1 traceID=

It’s hard to pinpoint the main cause for my dashboard not showing the existing table… However, I think this is the most likely culprit…

[yosiasz]

This is what I get when querying for datasources

[{‘id’: 2, ‘uid’: ‘gIZv5OQVk’, ‘orgId’: 1, ‘name’: ‘postgres’, ‘type’: ‘postgres’, ‘typeName’: ‘PostgreSQL’, ‘typeLogoUrl’: ‘public/app/plugins/datasource/postgres/img/postgresql_logo.svg’, ‘access’: ‘proxy’, ‘url’: ‘localhost:5432’, ‘password’: ‘’, ‘user’: ‘grafana’, ‘database’: ‘grafana’, ‘basicAuth’: False, ‘isDefault’: True, ‘jsonData’: {‘postgresVersion’: 903, ‘sslmode’: ‘disable’, ‘tlsAuth’: False, ‘tlsAuthWithCACert’: False, ‘tlsConfigurationMethod’: ‘file-path’, ‘tlsSkipVerify’: True}, ‘readOnly’: False}]

Not sure which one is required to create a datasource.

Might be worth reading

Data source HTTP API | Grafana documentation

for health check errors

Also provisioning

Provision Grafana | Grafana documentation

[yosiasz]

this worked but it did not set the password.

headers= {
    'Accept': 'application/json',
    'Content-Type': 'application/json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

jsonData = {
            "connMaxLifetime": 14400,
            "database": "grafana",
            "maxIdleConns": 100,
            "maxIdleConnsAuto": True,
            "maxOpenConns": 100,
            "postgresVersion": 1500,
            "sslmode": "disable"
        }

payload = {
            "orgId": 1,
            "name": "_zihon_9",
            "type": "postgres",
            "access": "proxy",
            "url": "localhost:6543",
            "user": "postgres",
            "password": "ADD_YOURS",
            "database": "",
            "basicAuth": False,
            "basicAuthUser": "",
            "withCredentials": False,
            "isDefault": False,
            "jsonData": jsonData
        }

#r = requests.post(url = URL, headers = headers, data=json.dumps())

#/api/datasources
list_datasources = requests.get(url = URL, headers = headers)

#print(list_datasources.json())

create_ds = requests.post(url = URL, headers = headers, data=json.dumps(payload))

print(create_ds.json())

[ivoavtavares]

Thanks, it helped!
After reading the provision documentation, I got a better sense how the request body should have been created. Also, your last reply also helped a lot!

To set a password, we have to create

secureJsonData = {
            "password": database.config.password,
}

and then pass "secureJsonData":secureJsonData, in the body request.

Also, I don’t know why but I must not use json.dumps…
And I had to change the hostname from localhost to local_postgres, which was the alias for the docker container.

[ivoavtavares]

MAny thanks for all the help @yosiasz