Migrating Grafana from SQLite to PostgreSQL database
Grafana supports SQLite3, PostgreSQL, and MySQL backend databases, SQLite being the default one. All of these have their own benefits, but migrating from one to another can be a daunting task. In this post, I'm going to show you how we managed to migrate from SQLite3 backend to PostgreSQL as our services started to scale up.
Take a copy of your current database instance. The default location is at
/var/lib/grafana/grafana.db. We will refer to this working copy of our database as
grafana.db. This is the one that we are going to migrate into our database.
You should have PostgreSQL database running at
<POSTGRES_HOST> listening on port
<POSTGRES_PASSWORD> as login username and password, respectively. We are doing this for PostgreSQL version 12. It should work for other versions as well.
Grafana version is 8.1.1
I'm going to use a great tool called
grafana-sqlite-to-postgres (release v2.0.1) to do all the heavy lifting in the migration process.
This tool greatly simplifies the migration process. All you need to do is make sure that your database has been properly initialized after which you are good to go.
We will initialize our database by using Grafana docker instance. First, we'll need need to create the database for Grafana. Use psql to connect to the PostgreSQL server
psql -h <POSTGRES_HOST> -p <POSTGRES_PORT> -U <POSTGRES_USER>
Create database for grafana
CREATE DATABASE grafana;
grafana.env to store the database login credentials with content
Next, use the following
docker-compose.yml to spin up the Grafana instance.
version: '3' services: grafana: image: grafana/grafana:8.1.1 container_name: grafana ports: - 3000:3000 env_file: - ./grafana.env
Start the instance with
docker-compose up. If everything goes without any errors, Grafana instance will initialize all required tables to the database.
grafana-sqlite-to-postgres requires that these tables exist.
Now, we have the database setup, but Grafana also has initialized default users and other content to the database that may interfere with the migrated data. We need to prune some of this data from the database. Use
psql to login to the newly initialize Grafana database
psql -h <POSTGRES_HOST> -p <POSTGRES_PORT> -U <POSTGRES_USER> grafana
and run the following to prune the default entries from the tables.
DELETE FROM server_lock; DELETE FROM dashboard_acl; DELETE FROM public.org_user; DELETE FROM public.user;
Finally, we are ready to migrate the database
/grafana-migrate_linux_amd64-v2.0.1 ./grafana.db "postgres://<POSTGRES_USER>:<POSTGRES_PASSWORD>@<POSTGRES_HOST>:<POSTGRES_PORT>/grafana?sslmode=disable"
If everything goes smoothly you should see
INFO[2021-09-13T16:29:16+03:00] 📁 Dump directory: /tmp INFO[2021-09-13T16:29:16+03:00] ✅ sqlite3 command exists INFO[2021-09-13T16:29:16+03:00] ✅ sqlite3 database dumped to /tmp/grafana.sql INFO[2021-09-13T16:29:17+03:00] ✅ CREATE statements removed from dump file INFO[2021-09-13T16:29:19+03:00] ✅ sqlite3 dump sanitized INFO[2021-09-13T16:29:20+03:00] ✅ migration_log statements removed INFO[2021-09-13T16:29:20+03:00] ✅ char keyword transformed INFO[2021-09-13T16:29:20+03:00] ✅ hex-encoded data decoded INFO[2021-09-13T16:29:45+03:00] ✅ Imported dump file to Postgres INFO[2021-09-13T16:29:45+03:00] 🎉 All done!
Most of the credit here goes to
for making tools to greatly simplify the migration process. Check more detailed instructions on running grafana-sqlite-to-postgres.