A postgres server (“cluster”) can have multiple databases; users are defined globally, and the same user can potentially connect to and query multiple databases on the same server.
Because of this fact, I find it odd that the postgres datasource doesn’t support overriding the ‘database’ configuration field in the query editor.
If I have a postgres server with hundreds of databases, this means I need to create a separate Grafana data source for each one, even if a single set of credentials can access them all. It would greatly simplify my administrative workflows if the same Grafana datasource could be used to query all databases to which a user has access.
I have not been able to find existing discussions about this decision, but would greatly appreciate being able to specify the database used for a particular panel.
The data source documentation actually describes it like it’s intended to be just a default (that can be overridden in a particular panel query).
Database name | The name of your PostgreSQL database. This database is used as the default for queries in the query editor.
Anyone know the backstory here or support adding this feature to the postgres plugin?
the approach I have used was having a dedicated shell database named grafana and from there create stored procedures that call the other databases or use the select x,y,z from db1.dbo.foobars approach. One connection with multiple db access
PostgreSQL doesn’t support cross database queries like db1.dbo.foobars → that’s SQL Server syntax. In PostgreSQL, databases are isolated even when they’re on the same cluster.
One workaround for the single Grafana datasource / multiple databases use case is postgres_fdw. You can use a dedicated grafana database as a hub, create a foreign server and user mapping for each target database, and expose the required tables through separate schemas.
For example, after setting up the FDW:
SELECT x, y, z FROM db1.foobars;
Grafana can then connect only to the grafana database while querying data exposed from the other databases.
dblink is another option, but postgres_fdw is generally more convenient because the remote tables can be queried with normal SQL.
Thanks for the suggestions, @yosiasz and @infofcc3.
I am familiar with postgres_fdw - that’s basically the complex administrative workflow I’m hoping can be simplified by making the datasource plugin a bit more flexible.
Would a contribution implementing this feature be accepted?
That would be a good route to contribute a solution
