So you have a Grafana with the default storage backend (SQLite) and you want to move it to PostgreSQL ? You’ve come to the right place.
Preparing
First we need to dump the SQLite database, since it’s just a single file simply shut down Grafana to avoid any corruption and copy the grafana.db somewhere safe. It is usually located at /var/lib/grafana.
In your Postgres DB create a grafana user and database then edit your Grafana configuration file to enable postgres storage.
In my case since I am using the official Docker Image it’s a simple matter of adding environment variables :
GF_DATABASE_TYPE=postgres
GF_DATABASE_NAME=grafana
GF_DATABASE_USER=grafana
GF_DATABASE_PASSWORD=XXXXXXX
GF_DATABASE_HOST=hostname:port
If your DB enforces SSL you may want to set GF_DATABASE_SSL_MODE as well.
Fresh start
We will use pgloader to read our SQLite file and translate it into something Postgres can understand however it will not cut it for the initial schema creation. Instead we’ll let Grafana run a migration on our fresh DB so we have a blank canvas to work on.
Simply start your Grafana instance and wait for it to come up, as soon as the login form is reachable you should have an initialized database in your Postgres DB.
Shut down Grafana for now.
Importing data to Postgres
With our schemas created we’ll now use pgloader to read and load the SQLite data into the Postgres DB.
Create a grafana.load file next to your grafana.db with the following content :
load database
from sqlite:///path/to/grafana.prod.db
into postgresql://user:password@host:port/grafana?sslmode=prefer
with data only, reset sequences
set work_mem to '16MB', maintenance_work_mem to '512 MB';
Make sure to replace the path and connection strings on line 2 and 3.
Now we simply run pgloader grafana.load :
pgloader grafana.load
2023-05-29T15:24:09.004954-04:00 LOG pgloader version "3.6.9"
2023-05-29T15:24:09.006273-04:00 LOG Data errors in '/private/tmp/pgloader/'
2023-05-29T15:24:09.006308-04:00 LOG Parsing commands from file #P"/.../grafana.load"
2023-05-29T15:24:09.678189-04:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///.../grafana.db {700E9F0973}>
2023-05-29T15:24:09.678306-04:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://XXX>
2023-05-29T15:24:10.654083-04:00 WARNING Source column "public"."migration_log"."id" is casted to type "bigserial" which is not the same as "integer", the type of current target database column "public"."migration_log"."id".
2023-05-29T15:24:10.654547-04:00 WARNING Source column "public"."migration_log"."migration_id" is casted to type "text" which is not the same as "character varying", the type of current target database column "public"."migration_log"."migration_id".
2023-05-29T15:24:10.654616-04:00 WARNING Source column "public"."migration_log"."success" is casted to type "bigint" which is not the same as "boolean", the type of current target database column "public"."migration_log"."success".
[...]
2023-05-29T13:32:12.794507-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "org_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY org, line 1
2023-05-29T13:32:13.285749-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "user_pkey1"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY user, line 1
2023-05-29T13:32:13.286581-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "migration_log_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY migration_log, line 1
2023-05-29T13:32:14.479374-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "org_user_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY org_user, line 1
2023-05-29T13:32:16.676660-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "dashboard_acl_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY dashboard_acl, line 1
2023-05-29T13:32:17.304534-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "UQE_server_lock_operation_uid"
DETAIL: Key (operation_uid)=(cleanup expired auth tokens) already exists.
CONTEXT: COPY server_lock, line 1
2023-05-29T13:32:20.856544-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "kv_store_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY kv_store, line 1
2023-05-29T13:32:28.301681-04:00 LOG report summary reset
table name errors rows bytes total time
-------------------------- --------- --------- --------- --------------
fetch 0 0 0.000s
fetch meta data 0 61 0.044s
Drop Foreign Keys 0 0 0.000s
-------------------------- --------- --------- --------- --------------
[...]
-------------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 12.382s
Reset Sequences 0 53 2.523s
Create Foreign Keys 0 0 0.000s
Install Comments 0 0 0.000s
-------------------------- --------- --------- --------- --------------
Total import time 7 538 4.9 MB 14.905s
At first you’ll see warnings, those are expected as column types between SQLite and Postgres don’t match 1:1 but this is fine, types will be automatically casted to the nearest.
However you may also encounter errors like this one :
2023-05-29T13:32:12.794507-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "org_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY org, line 1
This occurs because Grafana, while building the database, also created some default entities like a default organization, a default admin user etc. You’ll want to make a list of all the tables that failed the migration.
Almost there
Since we now have a database in an unknown state (some tables migrated, some failed) we want to start again from a clean state.
Drop the grafana database from the Postgres DB and recreate-it, restart Grafana to let it build the schemas once again then shut it down.
On your Postgres DB you’ll want to TRUNCATE all the failing tables to get rid of all the auto-generated rows conflicting with your existing data.
TRUNCATE TABLE org;
TRUNCATE TABLE user;
TRUNCATE TABLE migration_log;
TRUNCATE TABLE org_user;
TRUNCATE TABLE dashboard_acl;
TRUNCATE TABLE server_lock;
TRUNCATE TABLE kv_store;
...
At this point you should be able to execute pgloader again without errors :
pgloader grafana.load
2023-05-29T15:24:09.004954-04:00 LOG pgloader version "3.6.9"
2023-05-29T15:24:09.006273-04:00 LOG Data errors in '/private/tmp/pgloader/'
2023-05-29T15:24:09.006308-04:00 LOG Parsing commands from file #P"/.../grafana.load"
2023-05-29T15:24:09.678189-04:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///.../grafana.db {700E9F0973}>
2023-05-29T15:24:09.678306-04:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://XXX>
2023-05-29T15:24:10.654083-04:00 WARNING Source column "public"."migration_log"."id" is casted to type "bigserial" which is not the same as "integer", the type of current target database column "public"."migration_log"."id".
2023-05-29T15:24:10.654547-04:00 WARNING Source column "public"."migration_log"."migration_id" is casted to type "text" which is not the same as "character varying", the type of current target database column "public"."migration_log"."migration_id".
2023-05-29T15:24:10.654616-04:00 WARNING Source column "public"."migration_log"."success" is casted to type "bigint" which is not the same as "boolean", the type of current target database column "public"."migration_log"."success".
[...]
2023-05-29T15:24:21.045861-04:00 LOG report summary reset
table name errors rows bytes total time
--------------------------- --------- --------- --------- --------------
fetch 0 0 0.000s
fetch meta data 0 63 0.044s
Drop Foreign Keys 0 0 0.000s
--------------------------- --------- --------- --------- --------------
"user" 0 20 4.2 kB 0.530s
migration_log 0 487 81.1 kB 0.562s
org 0 1 0.1 kB 0.546s
dashboard_tag 0 1 0.0 kB 1.023s
dashboard_provisioning 0 0 1.093s
api_key 0 0 1.517s
quota 0 0 1.585s
star 0 5 0.0 kB 0.814s
session 0 0 2.090s
org_user 0 20 1.1 kB 1.070s
playlist_item 0 0 2.058s
dashboard 0 36 1.0 MB 1.420s
alert 0 12 21.0 kB 2.549s
annotation 0 4490 902.4 kB 2.831s
data_source 0 2 0.5 kB 1.555s
dashboard_snapshot 0 1 6.1 kB 1.968s
plugin_setting 0 0 2.042s
playlist 0 0 2.493s
preferences 0 3 0.3 kB 2.600s
alert_notification 0 6 1.6 kB 2.944s
test_data 0 0 3.071s
team 0 6 0.3 kB 3.533s
dashboard_acl 0 12 0.7 kB 3.642s
login_attempt 0 0 4.112s
alert_notification_state 0 22 0.8 kB 4.214s
user_auth_token 0 12 3.8 kB 4.607s
temp_user 0 9 1.2 kB 4.703s
annotation_tag 0 0 5.115s
alert_instance 0 16 4.6 kB 5.137s
alert_rule_version 0 302 548.1 kB 5.674s
library_element 0 0 5.595s
ngalert_configuration 0 0 6.066s
data_keys 0 110 21.5 kB 6.111s
role 0 28 3.1 kB 6.533s
user_role 0 19 0.6 kB 6.532s
query_history 0 1 0.3 kB 7.007s
query_history_star 0 0 6.989s
secrets 0 2 0.4 kB 7.472s
entity_event 0 0 7.464s
file 0 0 8.040s
seed_assignment 0 0 7.973s
folder 0 0 8.446s
dashboard_version 0 406 14.8 MB 4.523s
team_member 0 22 1.2 kB 3.185s
tag 0 0 3.709s
user_auth 0 19 33.9 kB 4.238s
server_lock 0 4 0.2 kB 4.762s
cache_data 0 0 4.839s
alert_rule_tag 0 0 5.298s
short_url 0 2 0.3 kB 5.343s
alert_rule 0 15 26.0 kB 5.791s
alert_configuration 0 1 3.1 kB 5.826s
library_element_connection 0 0 6.253s
kv_store 0 5 0.5 kB 6.292s
permission 0 160 13.1 kB 6.720s
team_role 0 6 0.2 kB 6.786s
builtin_role 0 3 0.2 kB 7.238s
provenance_type 0 0 7.297s
alert_image 0 0 7.713s
correlation 0 0 7.783s
dashboard_public 0 0 8.211s
file_meta 0 0 8.265s
alert_configuration_history 0 19 78.7 kB 8.362s
--------------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 8.694s
Reset Sequences 0 55 0.837s
Create Foreign Keys 0 0 0.000s
Install Comments 0 0 0.000s
--------------------------- --------- --------- --------- --------------
Total import time ✓ 6285 17.5 MB 9.532s
Restart Grafana and you should now be able to login with your usual credentials and find all your dashboards, all backed by Postgres.