Migrating Grafana's database from sqlite to postgres

Monitoring and Observability Grafana
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';```