Introduction

Grafana is a monitoring and dashboard system that supports metrics backends like Prometheus . This post talks about the process of migrating from using a SQLite backend for Grafana to PostgreSQL . An initial Grafana installation (at least the Docker image) defaults to using SQLite. While this is nice to get up and running quickly and try out Grafana, to make Grafana highly available (HA), it’s better to use a database backend like MySQL or PostgreSQL.

High Availability

Although it’s easy to dismiss HA for an internal monitoring service like Grafana, when implemented well, monitoring becomes increasingly crucial to the health of an organization’s business and infrastructure. As something like Grafana becomes more important as the pulse of the organization, even brief service interruptions become increasingly visible. If you are running a container orchestration service such as AWS Elastic Container Service (ECS) or Kubernetes (k8s) , then running Grafana with a an EBS Docker volume on ECS or Persistent Volume (PV) on k8s is an HA option that may be “good enough.” In this post, I assume that Grafana has been installed via an OS package or via Docker container running SQLite.

Aside: PostgreSQL HA

This talk of HA begs the question, “is your database HA?” There are quite a number of HA solutions for PostgreSQL including multi-master (although I’ve never tried it) and hot standby allowing reads on the standby node. If you’re running on AWS, you might as well run RDS with multiple availability zone failover .

SQLite to PostgreSQL Migration

The migration from SQLite to PostgreSQL is made much easier by the existence of the pgloader project which loads data into PostgreSQL from various sources. We can copy the SQLite database from Grafana’s data directory /var/lib/grafana/grafana.db and use that in the migration. We create a main.load script like the one below, lifted almost directly from the SQLite example in the pgloader documentation.

load database
  from sqlite:///path/to/grafana.db
  into postgresql://username:password@hostname/grafana
  with include drop, create tables, create indexes, reset sequences
  set work_mem to '16MB', maintenance_work_mem to '512 MB';

Schema Issues

It turns out that this naive approach results in a schema that is incompatible with what Grafana expects. For example, this will result in a field in the alert table, silenced, defined as a bigint rather than a boolean. We know this if we allow Grafana to create a schema by allowing it to run its own migrations again a clean PostgreSQL database. Below, we diff the schema that is created by pgloader versus the one created by Grafana. Notice differences in integer versus bigint fields, varchar versus text fields, and differences in NULLable fields.

2,15c2,15
<     id integer NOT NULL,
<     version bigint NOT NULL,
<     dashboard_id bigint NOT NULL,
<     panel_id bigint NOT NULL,
<     org_id bigint NOT NULL,
<     name character varying(255) NOT NULL,
<     message text NOT NULL,
<     state character varying(190) NOT NULL,
<     settings text NOT NULL,
<     frequency bigint NOT NULL,
<     handler bigint NOT NULL,
<     severity text NOT NULL,
<     silenced boolean NOT NULL,
<     execution_error text NOT NULL,
---
>     id bigint NOT NULL,
>     version bigint,
>     dashboard_id bigint,
>     panel_id bigint,
>     org_id bigint,
>     name text,
>     message text,
>     state text,
>     settings text,
>     frequency bigint,
>     handler bigint,
>     severity text,
>     silenced bigint,
>     execution_error text,
17,21c17,21
<     eval_date timestamp without time zone,
<     new_state_date timestamp without time zone NOT NULL,
<     state_changes integer NOT NULL,
<     created timestamp without time zone NOT NULL,
<     updated timestamp without time zone NOT NULL
---
>     eval_date timestamp with time zone,
>     new_state_date timestamp with time zone,
>     state_changes bigint,
>     created timestamp with time zone,
>     updated timestamp with time zone

Changing the Approach

This schema comparison suggests the correct procedure to follow. Instead of allowing pgloader to create the PostgreSQL schema, we need to export data from SQLite into the PostgreSQL schema generated by Grafana. The first step is to spin up an instance of Grafana pointed at some PostgreSQL database. One easy way to do this is to use docker-compose to spin up a set of linked containers. We can define a docker-compose.yml with the following contents that will spin up two containers on a shared network.

version: '3'
services:
  postgres:
    image: postgres:11
    environment:
      - POSTGRES_PASSWORD=dummy
  grafana:
    image: grafana/grafana:5.3.4
    container_name: grafana
    ports:
      - 3000:3000
    volumes:
      - ../volumes/grafana/data:/var/lib/grafana:z
    env_file:
      - ./grafana.env

In the ./grafana.env, we must configure Grafana to use the postgres service defined above.

GF_DATABASE_URL=postgres://postgres:dummy@postgres/grafana

Note that the Grafana container will initially fail because the PostgreSQL has not been initialized. Once the postgres service container intializes, we create the Grafana database. First, we initiate a psql prompt inside the postgres container:

docker-compose exec postgres psql -h 127.0.0.1 -U postgres

Then we issue the creation command:

psql> CREATE DATABASE grafana;

Next, we try restarting the Grafana container so that it will try to connect:

docker-compose restart grafana

The container should start and stay active, and a migration will create the schema and data in the grafana database. We can export that schema by running pg_dump in the postgres container.

  docker-compose exec postgres \
    pg_dump --schema-only -h 127.0.0.1 -U postgres grafana \
   | tee schema.sql

Retrying the Load

Then, we modify main.load to avoid recreating the schema.

load database
  from sqlite:///data/grafana.db
  into postgresql://username:password@hostname/grafana
  with data only, reset sequences
  set work_mem to '16MB', maintenance_work_mem to '512 MB';

Rerunning pgloader with this modified script, against a database initialized with the schema generated by Grafana completes the migration.

Conclusion

In this post, we discussed how to migrate a Grafana database from SQLite to PostgreSQL. While pgloader made the process easier, there were initial difficulties using the correct schema. Relying on Grafana’s migrations resolved the issues.

• postgresql
• sqlite
• grafana
• docker
• database
• sql