Migrating Grafana's database from sqlite to postgres

Monitoring and Observability Grafana
1

Hello everyone, is there any documentation on how to migrate grafana’s database from sqlite to postgres?
I’m in the last version of grafana 8, and all the tools I see online are 3rd party and don’t work correctly.

2

The schemas differ just enough that you can’t directly load the sqlite database with pgloader. Roughly what you’d want to do:

  1. Point any instance of Grafana to any Postgres instance so it initializes the database.
  2. Dump the schema: pg_dump --schema-only -h 127.0.0.1 -U postgres grafana > schema.sql
  3. Stop Grafana. Drop the grafana database from Postgres, create a fresh empty grafana database, then restore the schema-only backup: psql -d grafana -h 127.0.0.1 -U postgres < schema.sql
  4. Run a pgloader script to write the data only and not the schema:
  from sqlite:///path/to/your/grafana.db
  into [postgresql://username:password@hostname/grafana](postgresql://username:password@hostname/grafana)
  with data only, reset sequences
  set work_mem to '16MB', maintenance_work_mem to '512 MB';```
3

Hm… Okay, I’ll try.
How did you come with this solution though? Is it guaranteed to work with any version of Grafana? I’m not too familiar with pgloader

4

It’s a paraphrasing of this blog post minus the docker-specific things, and my own experiences with Postgres.

https://polyglot.jamie.ly/programming/2019/07/01/grafana-sqlite-to-postgres.html

5

It worked! I’ve had a few problems with pgloader though, I had to change the authent method to the database and Ubuntu’s version of pgloader is just old enough to connection problems with newer postgres deployments

6

Thanks :slightly_smiling_face:

7

(grafana DEFINITLY needs documentation for that)