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.

Copy

[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:

Copy

[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.

Copy

[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.

Copy

[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.)

Copy

[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!

Copy

[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!