In the world of data analysis and graphs, we have three important tools: Grafana, PostgreSQL, and Python. They work together to help us look at data and track how it changes over time. In this article, we’ll learn step by step how to use Grafana with a PostgreSQL database. We’ll also discover how to use Python to record data that changes over time. By the end of this article, you’ll know how to set up these tools, and you’ll see how they can be useful for your work with data.
First, we create our table. We also create a sequence for the primary key.
CREATE TABLE MEASUREMENTLOG
(
id numeric(10) NOT NULL,
key character varying(100) NOT NULL,
datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
status numeric(2) NOT NULL,
CONSTRAINT MEASUREMENTLOG_pkey PRIMARY KEY (id)
);
create sequence SEQ_MEASUREMENTLOG
minvalue 0
maxvalue 999999999999999999
start with 1
increment by 1
cache 1;
And a simple python script to persists a timeseries.
from random import randint
from time import sleep
from datetime import datetime
import os
import logging
import pytz
from dbutils import transactional, get_conn
from settings import DSN
tz = pytz.timezone('Europe/Madrid')
logging.basicConfig(
format='%(asctime)s [%(levelname)s] %(message)s',
level='INFO',
datefmt='%d/%m/%Y %X')
logger = logging.getLogger(__name__)
def persists(key, dt, status):
with transactional(conn=get_conn(DSN)) as db:
seq_log = db.fetch_all("SELECT nextval('seq_measurementlog')")[0][0]
db.insert('measurementlog', dict(
id=seq_log,
key=key,
datetime=dt,
status=status
))
KEY = os.getenv('KEY')
status = 0
while True:
now = datetime.now(tz)
persists(
key=KEY,
dt=now,
status=status
)
logger.info(f"[{now}] status: {status}")
status = 1 if status == 0 else 0
sleep(randint(5, 15))
Now we set up PostgreSQL database and Grafana in a docker-compose.yml.
More information about the configuration of postgresql and grafana here in the links
version: '3'
services:
pg:
image: pg
restart: unless-stopped
build:
context: .docker/pg/
dockerfile: Dockerfile
environment:
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
POSTGRES_USER: ${POSTGRES_USER}
POSTGRES_DB: ${POSTGRES_DB}
PGTZ: ${TIMEZONE}
PGDATA: /var/lib/postgresql/data/pgdata
ports:
- "5432:5432"
grafana:
image: grafana
restart: unless-stopped
build:
context: .docker/grafana/
dockerfile: Dockerfile
environment:
- GF_TIMEZONE=${TIMEZONE}
- GF_SECURITY_ADMIN_USER=${GF_SECURITY_ADMIN_USER}
- GF_SECURITY_ADMIN_PASSWORD=${GF_SECURITY_ADMIN_PASSWORD}
- GF_USERS_DEFAULT_THEME=${GF_USERS_DEFAULT_THEME}
- GF_USERS_ALLOW_SIGN_UP=${GF_USERS_ALLOW_SIGN_UP}
- GF_USERS_ALLOW_ORG_CREATE=${GF_USERS_ALLOW_ORG_CREATE}
- GF_AUTH_ANONYMOUS_ENABLED=${GF_AUTH_ANONYMOUS_ENABLED}
ports:
- "3000:3000"
depends_on:
- pg
We run the stack, Connect the grafana at port 3000 and configure the datasource
After that we can create the dashboard
We are using this query
SELECT
datetime,
status
FROM
measurementlog
WHERE
key = 'id1' and
$__timeFilter(datetime)
And that’s the dashboard
Proyect available in my github account.
Gonzalo Ayuso - Web Architect 
Hi Gonzalo,
Thank you for this article, I’m working on a project where I need to store timeseries data in postgreSQL and then display them in Grafana, this will be very helpful.
But I have a question, as it is not fully clear to me: what is the point of creating the sequence?
I understand the Python code: you retrieve the id from SEQ_MEASUREMENTLOG, and store your values with that id in MEASUREMENTLOG. It kind of makes your table look like an Excel table: with row numbers.
But why? Is it to have a primary key? Couldn’t there be an id column in MEASUREMENTLOG with “unique” and “autoincremental” constraints, so that every time you write a new value to the table, it creates the next id?
Michal
–
Yes. You donāt really need to create the sequence in a explicit way in order to create a unique value for the pk. I normally do like that because a peculiar circumstances within my deploy workflow, but you can use the data type for your column (instead of your own sequence).
Anyway, if you are working with pg and time series have a look to Timescale project
Thank you! Well I wouldn’t use the dateteime column for that, as I would like to have – for example – data coming with different tags etc. at the same time, like your “key” columnn. But is that unique primary key value some Grafana requirement? You don’t seem to use it in the Grafana query, is that just a db best practice?