Migrating Grafana from SQLite to PostgreSQL database

Jake

Sep 15, 2021 • 3 min read

Grafana supports SQLite3, PostgreSQL, and MySQL backend databases, SQLite being the default one. All of these have their own benefits, but migrating from one to another can be a daunting task. In this post, I'm going to show you how we managed to migrate from SQLite3 backend to PostgreSQL as our services started to scale up.

Setup

Take a copy of your current database instance. The default location is at /var/lib/grafana/grafana.db. We will refer to this working copy of our database as grafana.db. This is the one that we are going to migrate into our database.

You should have PostgreSQL database running at <POSTGRES_HOST> listening on port <POSTGRES_PORT> with <POSTGRES_USER> and <POSTGRES_PASSWORD> as login username and password, respectively. We are doing this for PostgreSQL version 12. It should work for other versions as well.

Grafana version is 8.1.1

Tools

I'm going to use a great tool called grafana-sqlite-to-postgres (release v2.0.1) to do all the heavy lifting in the migration process.

This tool greatly simplifies the migration process. All you need to do is make sure that your database has been properly initialized after which you are good to go.

Database initialization

We will initialize our database by using Grafana docker instance. First, we'll need need to create the database for Grafana. Use psql to connect to the PostgreSQL server

psql -h <POSTGRES_HOST> -p <POSTGRES_PORT> -U <POSTGRES_USER>

Create database for grafana

CREATE DATABASE grafana;

Create file grafana.env to store the database login credentials with content

GF_DATABASE_URL=postgres://<POSTGRES_USER>:<POSTGRES_PASSWORD>@<POSTGRES_HOST>:<POSTGRES_PORT>/grafana

Next, use the following docker-compose.yml to spin up the Grafana instance.

version: '3'
services:
  grafana:
    image: grafana/grafana:8.1.1
    container_name: grafana
    ports:
      - 3000:3000
    env_file:
      - ./grafana.env

Start the instance with docker-compose up. If everything goes without any errors, Grafana instance will initialize all required tables to the database. grafana-sqlite-to-postgres requires that these tables exist.

Now, we have the database setup, but Grafana also has initialized default users and other content to the database that may interfere with the migrated data. We need to prune some of this data from the database. Use psql to login to the newly initialize Grafana database

psql -h <POSTGRES_HOST> -p <POSTGRES_PORT> -U <POSTGRES_USER> grafana

and run the following to prune the default entries from the tables.

DELETE FROM server_lock;
DELETE FROM dashboard_acl;
DELETE FROM public.org_user;
DELETE FROM public.user;

Finally, we are ready to migrate the database

/grafana-migrate_linux_amd64-v2.0.1 ./grafana.db "postgres://<POSTGRES_USER>:<POSTGRES_PASSWORD>@<POSTGRES_HOST>:<POSTGRES_PORT>/grafana?sslmode=disable"

If everything goes smoothly you should see

INFO[2021-09-13T16:29:16+03:00] 📁 Dump directory: /tmp
INFO[2021-09-13T16:29:16+03:00] ✅ sqlite3 command exists
INFO[2021-09-13T16:29:16+03:00] ✅ sqlite3 database dumped to /tmp/grafana.sql
INFO[2021-09-13T16:29:17+03:00] ✅ CREATE statements removed from dump file
INFO[2021-09-13T16:29:19+03:00] ✅ sqlite3 dump sanitized
INFO[2021-09-13T16:29:20+03:00] ✅ migration_log statements removed
INFO[2021-09-13T16:29:20+03:00] ✅ char keyword transformed
INFO[2021-09-13T16:29:20+03:00] ✅ hex-encoded data decoded
INFO[2021-09-13T16:29:45+03:00] ✅ Imported dump file to Postgres
INFO[2021-09-13T16:29:45+03:00] 🎉 All done!

Most of the credit here goes to

for making tools to greatly simplify the migration process. Check more detailed instructions on running grafana-sqlite-to-postgres.