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 Kubernetes but the general approach would work for other kinds 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 database 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 PostgreSQL database that Grafana will use, in this case grafana, has been created and its tables 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 expects 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