Grafana with PostgreSQL — Data Visualization with open-source tool

Grafana is a multi-platform open source analytics and interactive visualization web application. It provides charts, graphs, and alerts for the web when connected to supported data sources. It is expandable through a plug-in system. End users can create complex monitoring dashboards using interactive query builders. It is written on Go programming language.

Image created by own: Icons from google

1. Download the Setup from Grafana offical site.
2. Select a Grafana Version you want to install (most recent Grafana version is selected by default).
3. Select an Edition.
4. Click Windows.

Install with Windows installer

1. Click Download the installer.
2. Open and run the installer.

Log in for the first time

1. Open your web browser and go to http://localhost:3000/. 3000 is the default HTTP port that Grafana listens to if you haven’t configured a different port.
2. On the login page, type admin for the username and password.
3. Change your password.

Grafana login page

After login the dashboard look like !

Database Set-up

Follow these steps : https://www.postgresqltutorial.com/install-postgresql/

1. Download the PostgreSQL install from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
2. Run and install it
3. Click next
4. Browse the folder if you want to change the directory; otherwise it go to default path which is C:\\Program Files\\
5. Add a Password for the superuser
6. Select a port number: by default PostgreSQL runs on port 5432
7. Click next, next …. then it start installing….

Is PostgreSQL installed Successfully ?

1. Search ‘sql’ in your system search bar and open the terminal (SQL Shell).

2. It looks like the below image: Press enter for all options except the password. Enter the password that you type during the installation of database.

Populate the Database with Dummy Data

1. Firstly, you need to create the table :

CREATE TABLE t_oil
(
region      text,
country     text,
year        int,
production  int,
consumption int
);

2. Secondly, populate the table with dummy data by running the below command:

COPY t_oil FROM
PROGRAM ‘curl https://www.cybertec-postgresql.com/secret/oil_ext.txt';

The above command just dump the all oil data into t_oil table in database. You can also insert the data with INSERT command but you need to insert the each row data manually.

INSERT INTO t_oil (
region, country, year, production, consumption )
VALUES (
North America, USA, 1965, 9014, 11522);

3. Lastly, run the below command to check the data is successfully inserted or not.

SELECT * FROM t_oil;

Display all dummy data in t_oil table

Configure the PostgreSQL with Grafana Data-Source

Now database and data are installed and configured successfully.

Lets connect the database with Grafana data source.

1. Open the side menu by clicking the Grafana icon in the top header.
2. In the side menu under the Configuration icon you should find a link named Data Sources.
3. Click the + Add data source button in the top header.
4. Select PostgreSQL from the Type dropdown.

5. Click on the postreSQL, it opens the configuration tab to insert the database details.

6. Insert the following configuration

Host : localhost:5432
User: postgres
Password : // database password that you enter during installation
SSL Mode: disable

7. Click on ‘Save & Test’ button in the buttom then it show the prompt message like ‘Database Connection OK’ which means database is configured successfully with Grafana.

8. You can go again on Configuration option and you see the PostgreSQL database show in Data Sources tab.

Visualize the data on Grafana dashboard

After configure the database successfully, lets start visualize the data in Grafana.

1. Create a dashboard: Click the ‘create’ option from left panel and select dashboard.

2. It create the ‘New Dashboard’.

3. Click on ‘Add new panel’. It open the panel like,

Don’t PANIC to see too many options here. Lets discuss one by one !

4. Name the title of Panel that you want,

5. Select a visualization option that you want to plot. I select a ‘Line Graph’ for plotting the oil consumption and production.

6. Select a Database source that you have created while configure the database in data-source tab.

7. Click on ‘Edit SQL’

8. Enter the SQL query in the query editor.

9. Click the ‘Apply’ button on the top then the dashboard looks like,

10. Similarly, create a new panel for ‘Oil Consumption’ and it looks like,

11. We can also make multiple graphs in a single graph. For this, we need to click on query and add new SQL query:

12. Click on ‘Apply’ and it should look like,

Formatting the Plots

After plotting the graphs, lets do some styling.

Go in the ‘Edit Panel’ and set the display and legend parameters as below:

display settings

legend settings

Setting the parameters, the plots in the dashboard looks like:

Dashboard for Oil production and consumption

Variable Declaration in Grafana

Note: As you notice above the SQL queries for plotting the graph is specific to only USA. What if user want to see for other countries as well ?
Yes, we can also do in that way as well. Like the user select the specific country from dropdown list then dashboard will display the oil production/consumption of that country.

For this we need to declare the variables. Go into the settings of dashboard and select the variable option.

Click on ‘Add Variable’ then it opens the new variable tab. Enter the parameters as below.

Click on ‘Add’ button and ‘Save dashboard’, the dropdown list is appearing on the dashboard which show the all country names.

Now we need to link the variable to SQL queries in Grafana so that the plots should be dynamic based on the user selection.

Again go in to the edit panel and update the query like,

Here the ‘$country’ is the variable name of the dropdown list. Replace the ‘USA’ from the WHERE clause in the query with ‘$country’ variable and save the dashboard.

To Conclude

The final dashboard update the plots according to the user’s selection of the country from dropdown list.

Grafana support almost all databases to plot the real-time data and provide a different monitoring features like alerting etc. Last but not least, it saves the developer’s time to implement the front end for data visualization.