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.
The schemas differ just enough that you can’t directly load the sqlite database with pgloader. Roughly what you’d want to do:
- Point any instance of Grafana to any Postgres instance so it initializes the database.
- Dump the schema:
pg_dump --schema-only -h 127.0.0.1 -U postgres grafana > schema.sql
- 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
- 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';```
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
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
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
Thanks
(grafana DEFINITLY needs documentation for that)