Migrating Grafana from SQLite to PostgreSQL
The easiest-to-use DB backend for Grafana is SQLite, which is fine for small deployments and testing. There might come a time, when a migration to a more robust DB engine becomes necessary. In this case, PostgreSQL was picked. This is a short write-up about what was done to make the migration possible.
In general, the migration consisted of 3 parts:
- Getting data out of SQLite
- Transforming it, so that importing it into PostgreSQL was possible
- Importing it into PostgreSQL
It would be tedious to do all that manually. Fortunately, a while back someone had already created a migration script for converting SQLite DB dumps to PostgreSQL compatible format and importing it into PostgreSQL. However, it was not that simple this time, and required a few adjustments and extra code to work for migrating Grafana data.
To begin, a source Grafana data dump of SQLite DB was needed. Getting data out of SQLite is quite easy. Just need to do a backup of the Grafana database:
echo '.dump' | sqlite3 /var/lib/grafana/grafana.db > grafana.db.backup
The biggest challenge proved to be converting the data in SQLite DB dump into format that could be successfully imported into a PostgreSQL instance and actually used by Grafana.
Some of the fields in SQLite dump were hex-encoded, which was not directly compatible with Grafana, when it was configured to use a PostgreSQL DB. Converting it back to plain text was the most difficult part. Initially it was attempted to replace the text in-place within the dump using standard shell utilities like sed, awk and the like. It proved to be unreliable and cumbersome. Instead, a small python helper script was written (hex_to_str.py). It took care of finding and converting hex-encoded fields back to plain text strings.
Another small adjustment was needed in data with fields of boolean data type. SQLite used binary values (1/0), but PostgreSQL required true/false. For that, the migration script was adjusted to change relevant columns to integer-type, and, after data was imported, convert those columns back into true/false based on integer values.
Some tables were removed from the dumped data before importing, because they were deemed non-essential, contained unneeded data or data would be imported into these tables another way. These actions were also added to the migration script.
The adjusted scripts, which did most of the migration work, are available on GitHub. In practice it took a couple of minutes to successfully migrate our Grafana DB from SQLite to PostgreSQL.
After the data had been migrated, one last step was needed - adjust Grfana configuration to work with the newly created PostgreSQL database.