How to migrate your configuration database
Grafana by default uses sqlite3 as a local database to hold the configuration information (such as users, dashboards, alerts, etc.). But did you know you can also use other databases for this purpose?
Many large customers prefer to use either Postgresql or MySQL/MariaDB, and we recently had a request from a company wanting some help to migrate their configuration data from Postgresql to MySQL. This is not a common request, so we didn’t have any pre-existing tooling to do it.
If you find yourself in this situation, here’s an approach that should make it quite simple. Grafana does not make overly complex demands of a database – which is why it can use sqlite3 – so this is mostly a problem of getting the database records from each table in the Postgres database over to the MySQL/MariaDB database.
This walkthrough will make some assumptions for the sake of simplicity: Neither the source or target databases are protected by passwords or other security. Both are running on the same server as the Grafana server. All the commands are run as root.
In production, none of these are likely to be the case, so you would need to adjust the commands as needed. All references to MySQL apply equally to MariaDB.
Step-by-Step Instructions
Firstly, make sure you don’t change the version of Grafana you are running when you change databases. We’re going to use Grafana to create the new (empty) database, and then restore the data from the old database into it.
1. Let’s stop Grafana, and create our empty MySQL database.
[root@al-g1 ~]# systemctl stop grafana-server
[root@al-g1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
….
MariaDB [(none)]> create database grafana;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> ^D
[root@al-g1 ~]#
2. Next, we want to edit the grafana.ini
file to connect to this new database, and restart grafana. Make sure the database
section of your grafana.ini has the relevant sections to connect to this database. Here’s how it should look in this simplified example:
[database]
type = mysql
host = localhost:3306
name = grafana
user = [mysql database username]
password = [mysql database password]
Remember to comment out any lines referring to the postgres database.
3. Now, start Grafana and check that you can log in as admin/admin, then stop it.
[root@al-g1 ~]# systemctl start grafana-server
[root@al-g1 ~]# systemctl #check you can log into grafana.
[root@al-g1 ~]# systemctl stop grafana-server
4. Next, we want to connect to the postgres database and list the tables we want to export.
[root@al-g1 ~]# sudo -u postgres psql -d grafana
could not change directory to "/root"
psql (9.2.24)
Type "help" for help.
grafana=# SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public';
migration_log
user
temp_user
star
org
org_user
dashboard_tag
dashboard
dashboard_provisioning
data_source
api_key
dashboard_snapshot
quota
plugin_setting
session
playlist
playlist_item
preferences
alert_rule_tag
alert
alert_notification_state
alert_notification
annotation
dashboard_version
annotation_tag
test_data
team
dashboard_acl
team_member
tag
login_attempt
cache_data
user_auth
server_lock
user_auth_token
data_source_acl
team_group
report
(38 rows)
grafana=# \q
5. Save those table names in your favorite text editor.
6. We’re now going to use the postgres backup command to dump those tables, and use SED to perform a bit of post-processing on the output. Here’s a simple example for the first table, Alert. This needs to be done against each of the 38 tables above. (There’s a full example running against all 38 tables at the end of this article.)
[root@al-g1 ~]# pg_dump -U postgres -a --inserts --disable-dollar-quoting --no-tablespaces --no-security-labels -h localhost -w -t alert grafana | sed -r -e '/^SET/d' -e '/^SELECT pg_catalog/d' -e 's/-- Data for Name: ([a-zA-Z0-9_]+).*/TRUNCATE \1;/' -e 's/"user"/user/g' -e 's/INSERT INTO public./INSERT INTO /' -e 's/\\\"/\\\\\"/g' |less
The six sed commands are worth explaining in some more detail in case you run into problems due to specifics in your data.
'/^SET/d'
& '/^SELECT pg_catalog/d'
remove some SET statements, and a SELECT statement that are postgres specific.
's/-- Data for Name: ([a-zA-Z0-9_]+).*/TRUNCATE \1;/'
changes a header that is printed ahead of each set of INSERT statements into a TRUNCATE [table-name] statement. This ensures that any data that might be in the new table (from the first time Grafana ran against it) is discarded.
's/"user"/user/g'
removes quoting around the table name “user,” which postgres requires but mysql does not. This might catch you out if any of your queries have the string “user” in them. You may need to add that back in.
's/INSERT INTO public./INSERT INTO /'
Newer versions of Postgres have prepended the schema onto every table name on an export. This removes them.
's/\\\"/\\\\\"/g'
This ensures that any escaped quote is double-escaped, as this is needed to pass through the MySQL import so the escaped quotes make it through to MySQL.
7. Next, we want to be able to read that data into MySQL. If you are running the same system, you can just use a pipe – |mysql grafana
– at the end of that above command,. But more likely you are on a different system, so redirect the output to a file – >grafana.psql
– and copy that file to the target system, where you can import it with ‘[root@al-g1 ~]# mysql grafana < grafana.psql`.
Script to Run Export Against All Tables at Once
To run that same export against all the tables at once, you can run this script, but PLEASE make sure to check the table names are all correct. Things might change between when this is written and when you read it!
[root@al-g1 ~]# pg_dump -U postgres -a --inserts --disable-dollar-quoting --no-tablespaces --no-security-labels -h localhost -w -t alert -t alert_notification -t alert_notification_state -t alert_rule_tag -t annotation -t annotation_tag -t api_key -t cache_data -t dashboard -t dashboard_acl -t dashboard_provisioning -t dashboard_snapshot -t dashboard_tag -t dashboard_version -t data_source -t data_source_acl -t login_attempt -t migration_log -t org -t org_user -t playlist -t playlist_item -t plugin_setting -t preferences -t quota -t report -t server_lock -t session -t star -t tag -t team -t team_group -t team_member -t temp_user -t test_data -t user -t user_auth -t user_auth_token grafana | sed -r -e '/^SET/d' -e '/^SELECT pg_catalog/d' -e 's/-- Data for Name: ([a-zA-Z0-9_]+).*/TRUNCATE \1;/' -e 's/"user"/user/g' -e 's/INSERT INTO public./INSERT INTO /' -e 's/\\\"/\\\\\"/g' > grafana.psql
Once you have successfully imported the data into MySQL, you can re-start the Grafana server, and verify that all your configuration has been imported successfully.
We hope this how-to blog post was helpful. Check back for more tips, tutorials, and solutions – and if you have any questions or requests, let us know!