How To Configure a PostgreSQL Datasource in Grafana

Introduction 

So, you’ve got a PostgreSQL database packed with juicy data, and you want to turn those raw numbers into slick, interactive Grafana dashboards? Good call! Grafana’s PostgreSQL datasource is like the secret handshake that lets you visualize your data in style—no extra ETL magic required. In this guide, we’ll walk through getting PostgreSQL and Grafana to play nice, covering everything from connection settings to query tuning. Whether you’re debugging performance metrics or just flexing your SQL muscles, by the end of this, you’ll have a fully operational setup. Let’s dive in!

This guide covers installing PostgreSQL, enabling SSL, allowing remote connections, and configuring Grafana to visualize live data. Don't have Grafana? Sign up for MetricFire's free trial to try it out and follow along with this tutorial!

Install and Configure PostgreSQL

It's no secret that PG is popular among developers (this applies to postgres and Peter Gabriel), and we're all about keeping things PG here! This article assumes that you already have an instance of PG up and running. If not, we've included some steps on how to install/run this on your own server (Linux):

• sudo apt update
• sudo apt install postgresql postgresql-contrib -y
• sudo systemctl enable --now postgresql

Create a DB and User

Login to postgres:

• sudo -u postgres psql

CREATE DATABASE testdb;
CREATE USER grafana_user WITH ENCRYPTED PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE testdb TO grafana_user;

Allow Remote Connections

Modify the PG configuration file to reflect the following changes. This file is typically located at: /etc/postgresql/14/main/postgresql.conf

listen_addresses = '*'
ssl = on
ssl_cert_file = '/var/lib/postgresql/certs/server.crt'
ssl_key_file = '/var/lib/postgresql/certs/server.key'

Confirm the changes were applied:

• sudo -u postgres psql -c "SHOW listen_addresses; SHOW ssl; SHOW ssl_cert_file; SHOW ssl_key_file;"

Modify the host based authentication file, typically located at: /etc/postgresql/14/main/pg_hba.conf

host testdb grafana_user <Grafana_IP>/32 md5
hostssl testdb grafana_user <Grafana_IP>/32 md5

Enable SSL in PostgreSQL

Without SSL, your PostgreSQL queries, credentials, and data are basically streaking across the internet in plain text—a hacker’s dream. Enabling SSL encrypts your database traffic, ensuring:

✅ No stolen passwords (plaintext auth? No thanks)
✅ No prying eyes on your queries (keep your analytics yours)
✅ No man-in-the-middle attacks (because security > regrets)

A few config tweaks, and your PostgreSQL setup goes from the wild west to Fort Knox. Lock it down!

sudo mkdir -p /var/lib/postgresql/certs
cd /var/lib/postgresql/certs
sudo openssl req -new -x509 -nodes -days 365 -out server.crt -keyout server.key
sudo chown postgres:postgres server.*
sudo chmod 600 server.key

sudo ufw allow from <Grafana_IP> to any port 5432 proto tcp
sudo ufw reload

Confirm that PG is listening on port 5432:

• sudo netstat -tulnp | grep postgres
• sudo ss -lntp | grep 5432

Confirm PostgreSQL is using the certificate:

SHOW ssl;
SHOW ssl_cert_file;
SHOW ssl_key_file;

Insert Sample Data

Login to postgres:

• sudo -u postgres psql

Switch to your example database:

• \c testdb;

Confirm you are in the correct db (testdb) before inserting data:

SELECT current_database();

CREATE TABLE public.sample_data (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    value INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO public.sample_data (name, value, created_at) VALUES
    ('CPU Load', floor(random() * 100), NOW()),
    ('Memory Usage', floor(random() * 15500) + 500, NOW()),
    ('Disk Space', floor(random() * 490) + 10, NOW());

Configure a Grafana PostgreSQL Data Source

1. Go to: Grafana → Configuration → Data Sources → PostgreSQL
2. Enter Details:
   • Host: <PostgreSQL_Server_IP>:5432
   • Database: testdb
   • User: grafana_user
   • Password: your_secure_password
3. Set SSL Mode: verify-ca
4. Paste server.crt contents into TLS/SSL Root Certificate field
5. Click "Save & Test"

NOTE: locate the SSL cert in your PG instance with this command: sudo cat /var/lib/postgresql/certs/server.crt

You can also just use a CA bundle, which is probably best practice anyway:

• sudo cat /var/lib/postgresql/certs/server.crt > /var/lib/postgresql/certs/ca-bundle.pem

Then, in Grafana, upload `ca-bundle.pem` to the TLS/SSL Root Certificate field.

Create a Grafana Dashboard Panel

In your instance of Grafana, go to → Dashboards → New Dashboard → "Add a new panel"

Select your new PostgreSQL Datasource, set format as Time Series, select the Code option, and enter a Query for your sample data:

SELECT created_at AS "time", name AS "metric", value AS "value"
FROM public.sample_data
WHERE created_at >= now() - interval '5 minutes'
ORDER BY created_at;

Then Save your changes by clicking Apply.

Conclusion

Boom! You’ve just leveled up your PostgreSQL game by making it secure, remote-friendly, and Grafana-ready—no more rogue queries streaking across the internet in plain text. Now, with a few clicks, you can visualize database trends, track performance metrics, and flex your data skills like a true dashboard ninja. Whether you're monitoring system health, tracking app usage, or just love watching numbers dance in Grafana, this setup gives you real-time insights with zero guesswork. So why wait? Spin it up, graph it out, and make your data work for you!