Connected to Postgres DB but dashboard has no data

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…

Welcome,

you will need a separate port other than the default 5432

    ports:
      - "6543:5432"

Then use 6543 in your grafana connection to postgres

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"

:point_up_2:

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.

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": ""
  }
}

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"

Please post your python script. not as image though.

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)

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

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…

1 Like

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

for health check errors

Also provisioning

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())
1 Like

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.

1 Like

MAny thanks for all the help @yosiasz :wink:

1 Like