Hi Community,
I was unable to find any working solution for migrating Grafana 6 from sqlite3 to Postgres on Linux. After a while of pondering, it turned out to be quite easy. All you need is root access, sqlite3 and Postgres installed.
My environment is CentOs7.4 and Postgres 10.
I assume you have Postgres installed and you have new empty Postgres DB with full privileges (let’s call it grafana_psql) and you are logged on your machine as root. My sqlite3 db was simply called grafana.db and I am going to use the same name here.
Step 1: Dump all your tables from sqlite3 DB to csv files:
- create subdirectory for .csv files, cd to your sqlite grafana DB directory and type:
root@server: for t in $(sqlite3 grafana.db .tables); do echo -e “.header on \n.mode csv \n.out /path/to/grafana_dump_folder/$t.csv \nselect * from $t;”; done |sqlite3 grafana.db
You should see all .csv files in /path/to/grafana_dump_folder
Step 2: Stop grafana service (like systemctl stop grafana-server)
Step 3: Edit your grafana config file (like vi /etc/grafana/grafana.ini) and point grafana to new, empty Postgres DB. The following is an excerpt from my config:**
[database]
You can configure the database connection by specifying type, host, name, user and password
as seperate properties or as on string using the url propertie.
Either “mysql”, “postgres” or “sqlite3”, it’s your choice
type = postgres
host = 127.0.0.1:5432
name = grafana_psql
user = grafana_user
password =""“very_secure_password”""
Step 4: Start grafana service (like systemctl start grafana-server). Grafana is going to create structure in your database (create tables with proper columns and so on). Again stop grafana service.
Step 5: Delete all default entries from your new Postgres grafana_psql database.
5a: log in into your posgres:
root@server-1: su postgres -
bash-4.2$ psql -d grafana_psql
grafana_psql#: delete from "user";
grafana_psql#:\q
now get back to your root account
Step 5b: Delete the rest of newly-created grafana_psql database entries: cd into your grafana_dump_folder and do :
ATTENTION: I am using the full path to psql: /usr/pgsql-10/bin/psql because I do not have the psql command in my PATH. If on your machine command psql works, you can omit the path and use just psql command
root@server-1: for f in *.csv; do t=${f::-4}; /usr/pgsql-10/bin/psql -U postgres -d grafana_psql -c "delete from $t"; done
You will see a list of deletions and an error about user table. Ignore the error as you have already got rid of user table entries in step 5a
Step 6: Move your grafana_dump_folder to the location that is accessible by postgres and set permissions:
root@server-1: cp -r /grafana/dump/foler /var/lib/pgsql
root@server-1:chown -R postgres /var/lib/postgres/drafana_dump_folder
Step 7: Load your users to grafana-psql DB:
root@server-1: su postgres -
bash-4.2$ psql -d grafana_psql
grafana_psql#: copy “user” from ‘/var/lib/pgsql/grafana_dump_folder/user.csv’ delimiter ‘,’ csv header;
Check if users were loaded: select * from “user”;
Step 8: Load the contents of remaining tables:
log out from Postgres, switch to root and cd to /var/lib/pgsql/grafana_dump_folder you created in step 7.
move away user.csv file as you have already imported users into grafana_psql db:
root@server-1: mv /var/lib/pgsql/grafana_dump_folder/user.csv /home/yourfolder
Step 9: Import remaining tables:
root@server-1: cd /var/lib/pgsql/grafana_dump_folder
root@server-1: for f in *csv; do t=${f::-4};/usr/pgsql-10/bin/psql -U postgres -d grafana_psql -c "copy $t from ‘/var/lib/pgsql/grafana_dump_folder/$f’ delimiter ','csv header; "; done
At this point all your tables should be imported
Step 10: Start Grafana and debug. Type: systemctl start grafana-server. Most likely this operation will fail. Check server log ( like tail -100 /var/log/messages ). You will find lines like this:
grafana-server[135037]: t=2019-05-30T11:58:04+0200 lvl=eror msg=**"Exec failed"** logger=migrator error="pq: **column \"permission\**" of **relation \"team_member\"** already exists" sql="alter table \"team_member\" ADD COLUMN \"permission\" SMALLINT NULL "
Look for “Exec failed”. Check column (here ‘permission’) and table (here described as relation:
relation "team_member"
Log into your postgres DB:
root@server-1: su postgres -
bash-4.2$ psql -d grafana_psql
grafana_psql#:
Now check if column mentioned in the log exists in table:3
` grafana_psql#: select * from team_member;`
If positive, delete the column:
grafana_psql#:alter table team_member drop column permission;
Now try again to start grafana-server. Most likely you are going to encounter a similar problem with user_auth and user_auth_token tables. Just keep deleting columns and restarting grafana server after each deletion. You will have to delete about 8-12 columns in various tables and at some point, Grafana is going to kick-in:)