Grafana database migration from sqlite to postgresql

By default, Grafana stores its settings in an SQLite database but it can be configured to use PostgreSQL instead.

This article explains how to migrate settings from an existing Grafana instance from SQLite to PostgreSQL using pgloader

This approach is based on this article.

Here, both Grafana and PostgreSQL are deployed in Kubernete but the general approach would would for other kind of installations as well.

Getting the SQLite DB

Getting the SQLite database from the existing Grafana instance can be achieved by simply copying it over locally. Here, we are putting it in a folder named pgloader for reasons that will become clear later.

mkdir pgloader
kubectl cp -n grafana <grafana pod>:/var/lib/grafana/grafana.db ./pgloader/grafana.db

Create a dummy PostgresQL DB to get the Grafana schema

pgLoader will be used to load the SQLite data into PostgreSQL. However, minor schema differences make it impossible to load the data as it is. Instead, the data must be loaded into existing tables with correct columns and their respective types.

In order to create all the necessary tables yet keep them empty so as to load our own data into those, one can simply copy the schema from a dummy PostgreSQL database populated by Grafana into the dastabase that Grafana will ultimately use.

Configuring Grafana to use PostgreSQL can be achieved with environment variables

GF_DATABASE_TYPE: postgres
GF_DATABASE_URL: postgres://grafana:<password>@postgres.postgresql:5432/grafana-dummy

Getting the DB schema

With our dummy database populated by Grafana, we can extract the schema using pg_dump and store the output as an .sql script

kubectl exec -n postgresql <postgresql-pod> -it -- \
	pg_dump --schema-only -h 127.0.0.1 -U postgres grafana-dummy | tee schema.sql

This generates an SQL file which can be run using a tool like DBeaver in order to create the tables in the PostgreSQL database that Grafana will use.

Using pgloader to load the SQLite data into PostgreSQL

Once the PostresQL database that Grafana will use, in this case grafana has been created and its generated by running the schema’s .sql script, pgloader can be used to load data into it.

pgloader will automatically adapt the data to the schema existing in the grafana database

pgloader expcts a folder structure as follows:

|-pgloader
	|-grafana.db
	|-main.load

This explains why the grafana.db file has been saved in the pgloader folder

main.load contains the data loading configuration. Note the with data only setting.

load database
  from sqlite:///pgloader/grafana.db
  into postgresql://postgres:<password>@192.168.1.6:32032/grafana
  with data only, reset sequences
  set work_mem to '16MB', maintenance_work_mem to '512 MB';

pgloader itself can be run as a Docker container

docker run --rm -it  \
	-v ./pgloader:/pgloader \
	dimitri/pgloader:latest \
	pgloader /pgloader/main.load

Starting Grafana with its new PostgreSQL database

Grafana can now be started, now configured to use its PostgreSQL database for storage

GF_DATABASE_URL: postgres://grafana:<password>@postgres.postgresql:5432/grafana