Database Migration

At 04:38 a Grafana 11.2 host refuses to start after the operator runs an apt upgrade that bumps to 11.3.0. The error reads:

WARN[11-15 04:33:12] Migrating database...
ERRO[11-15 04:38:55] SQL Error: Error 1062: Duplicate entry
'rule-7e2a' for key 'alert_rule.PRIMARY'
FATAL[11-15 04:38:55] Server shutdown

The schema migration step has failed on a uniqueness constraint introduced by the new Grafana version. The database has rows that violate the constraint — leftovers from a provisioning mistake six weeks ago that the team never cleaned up. Grafana cannot start, and the team cannot roll back to 11.2 because the schema is now half-migrated.

A Grafana upgrade is, before anything else, a database operation. The binary can start in seconds; the database can take minutes to hours. When the database part goes wrong, the whole upgrade is stuck.

What a Grafana database migration is

A Grafana database migration has two distinct shapes, and the team must know which one it is dealing with before the upgrade starts:

1. Backend migration. The Grafana deployment moves from sqlite to MySQL or Postgres (or between MySQL and Postgres). This is a one-time move that is not tied to a Grafana version. The team does this because the deployment is growing past what sqlite can carry.
2. Schema migration. The Grafana binary introduces changes to the schema of the existing database. This happens automatically as part of a Grafana upgrade (the binary runs migrations against the database on startup). Schema migrations are tied to Grafana versions.

The two shapes overlap. A schema migration can be the trigger that forces a backend migration. A Grafana major version may introduce a schema that is too large or too relational for sqlite to carry, in which case the team has to migrate the backend before the major upgrade can land.

Why a sysadmin cares

Three operational pains the discipline prevents:

• sqlite lock contention at scale. Grafana’s default database is sqlite. A single sqlite file serialises writes across the whole process. A Grafana instance serving hundreds of dashboards and thousands of alert rules starts to see database is locked errors under load. Migrating to MySQL or Postgres removes the serialisation point.
• Schema migration failure on dirty data. A schema migration that adds a uniqueness constraint fails if the existing data violates the constraint. The failure stops Grafana from starting, which stops the alert evaluator, which stops the team from being paged. The discipline of pre-migration database inspection catches this before the binary ever starts.
• HA deployments without a shared backend. A team that scales Grafana horizontally (two or more Grafana instances behind a load balancer) cannot share a sqlite file. The instances will overwrite each other. Migrating to MySQL or Postgres is what makes HA possible.

The cost of the discipline is roughly half a day for the backend migration, ten minutes for the schema inspection. The cost of skipping either is a Grafana that will not start.

How it works: the sqlite -> MySQL / Postgres path

The migration has three stages:

   Stage 1                  Stage 2                 Stage 3
+-------------+         +--------------+         +--------------+
|  Old        |         |  Old Grafana |         |  New Grafana |
|  Grafana    |  ---->  |  running     |  ---->  |  running     |
|  on sqlite  |  export |  with mysql  |  switch |  on mysql    |
|             |         |  / postgres  |         |              |
+-------------+         +--------------+         +--------------+
        |                       |                      |
        v                       v                      v
   grafana.db             grafana.db               grafana.db
   on local disk          + mysql/postgres        reads mysql
                          backend, both           / postgres
                          still being             backend only
                          written to

Stage 1 is the export. Grafana 11.x has no built-in sqlite-to-mysql migration tool. The team uses mysqldump-shape tools or pgloader to move the rows. The Grafana project documents the supported schema but the conversion itself is the operator’s responsibility.

Stage 2 is the dual-write window. Grafana is configured to write to both the sqlite database and the new MySQL/Postgres backend. This is the moment to verify that the two databases stay in sync — Grafana does not do this automatically; the team has to inspect both and confirm row counts match.

Stage 3 is the cutover. Grafana is restarted with the sqlite backend removed and the MySQL/Postgres backend as the sole source of truth. The sqlite file is kept for the rollback window and then archived.

How to configure it: the migration

The configuration changes for the new backend. The MySQL section in /etc/grafana/grafana.ini:

[database]
# Production should never run on sqlite. The default is sqlite3
# for development convenience; the production deployment must
# override it.
type = mysql
host = mysql.internal:3306
name = grafana
user = grafana
# Password comes from /etc/grafana/grafana-secrets.ini,
# mounted from a secret store. Never commit this.
password = ${MYSQL_PASSWORD}
# Grafana's MySQL driver requires explicit SSL settings in
# 11.x. The defaults are NOT secure.
ssl_mode = require
# Connection pool: tune for the deployment size. The defaults
# are too small for HA.
max_open_conn = 50
max_idle_conn = 10
conn_max_lifetime = 300

The Postgres section in /etc/grafana/grafana.ini:

[database]
type = postgres
host = postgres.internal:5432
name = grafana
user = grafana
password = ${POSTGRES_PASSWORD}
ssl_mode = require
max_open_conn = 50
max_idle_conn = 10
conn_max_lifetime = 300

The Grafana binary will run the schema migrations on startup. The operator can also run them manually:

# CONFIGURATION: trigger the schema migrations without
# starting the HTTP server. The binary exits after the
# migrations complete.
grafana-server -config=/etc/grafana/grafana.ini \
               -homepath=/usr/share/grafana \
               migrations

How to validate it

The minimum validation set for a database migration. Every command is READ-ONLY unless flagged otherwise:

# READ-ONLY: pre-migration. Confirm the sqlite database is
# reachable and has rows in the expected tables. Write these
# numbers down; the team will compare them after the cutover.
sqlite3 /var/lib/grafana/grafana.db <<'SQL'
select 'dashboard', count(*) from dashboard
union all
select 'data_source', count(*) from data_source
union all
select 'alert_rule', count(*) from alert_rule
union all
select 'annotation', count(*) from annotation
union all
select 'user', count(*) from "user";
SQL
# dashboard|214
# data_source|12
# alert_rule|87
# annotation|8431
# user|18

# READ-ONLY: confirm the new backend is reachable and has the
# same tables after the migration.
mysql -h mysql.internal -u grafana -p grafana \
  -e "select table_name, table_rows
      from information_schema.tables
      where table_schema='grafana'
      order by table_name;"
# alert_instance|0
# alert_rule|87
# annotation|8431
# dashboard|214
# data_source|12
# user|18

# READ-ONLY: confirm the schema version matches the binary's
# expectation. The Grafana migration log line is the
# authoritative number.
journalctl -u grafana-server -n 100 | grep -i migrat
# Nov 15 04:33:12 grafana-01 grafana-server[1234]:
#   INFO[11-15 04:33:12] Migrating database v0 to v41
# Nov 15 04:33:13 grafana-01 grafana-server[1234]:
#   INFO[11-15 04:33:13] DB Migration v41 successful

# READ-ONLY: confirm Grafana reports a healthy database.
curl -fsS http://grafana-canary-01:3000/api/health
# {"database":"ok","version":"11.3.0"}

The validation order matters. Row counts first, schema version second, application health third. The team should not consider the migration complete until the row counts on the new backend match the pre-migration snapshot.

How it can fail

Five failure modes recur in Grafana database migrations.

1. Schema migration fails on dirty data. A new Grafana version introduces a constraint the existing data violates. Symptom: grafana-server refuses to start with an SQL error; the team has to manually deduplicate the offending rows before the upgrade can proceed.
2. sqlite lock contention at scale. A Grafana deployment that has grown past ~50k dashboard rows starts seeing database is locked errors under alert traffic. Symptom: alert rule evaluations fail with backend errors; the UI reports “failed to save dashboard” intermittently.
3. HA deployment on sqlite. Two Grafana instances behind a load balancer share the same sqlite file via NFS. Symptom: dashboards appear and disappear as the load balancer round-robins; alert rules fire from one instance and disappear when the other instance serves the API call.
4. Password in the configuration file. The MySQL/Postgres password is committed to grafana.ini in the config repo. Symptom: the secret is leaked via the repo’s history and has to be rotated; the audit trail shows the leak.
5. Dual-write drift. Grafana is configured to write to both sqlite and MySQL during the migration window, but the two databases diverge because of a provisioning error. Symptom: the cutover reveals that some dashboards exist only in sqlite and some alert rules exist only in MySQL.

How to troubleshoot it

When a database migration goes wrong, the diagnostic order matters. Start at the database view and move toward the application view.

1. What does the database say? Run the row-count query against both the old and new backends. Are the numbers the same?
2. What does the migration log say? Read journalctl -u grafana-server (or the container’s stdout). Look for INFO[date] Migrating database vX to vY and INFO[date] DB Migration vY successful. If the log shows an ERRO line, the migration has failed and the binary has exited.
3. What does the schema say? Run SHOW CREATE TABLE alert_rule (MySQL) or \d+ alert_rule (Postgres). Is the schema the version the new binary expects?
4. Form a hypothesis. Pin the failure to one of the five failure modes above. The most common is “schema migration failed on dirty data”.
5. Find evidence. Inspect the rows that violate the new constraint. For the opening incident, this would be SELECT uid, COUNT(*) FROM alert_rule GROUP BY uid HAVING COUNT(*) > 1;.
6. Test the hypothesis. Deduplicate the offending rows, restart the binary, and confirm the migration completes.
7. Validate the fix. Re-run the row-count query. Confirm the row counts match the pre-migration snapshot.

The diagnostic order is “did the schema migrate correctly before asking whether the application is working.”

Security implications

Three security implications are specific to Grafana database work:

• Password in grafana.ini. The MySQL/Postgres password must come from a secret store, not from the committed configuration. Grafana 11.x supports ${ENV_VAR} substitution in grafana.ini; the team should use this and source the environment from a secret manager.
• TLS to the database. The Grafana MySQL and Postgres drivers support ssl_mode=require. The default is prefer, which is silent on TLS failure. A production deployment must set ssl_mode=require (or verify-full for Postgres).
• Database user privileges. The Grafana database user needs SELECT, INSERT, UPDATE, DELETE on the schema, plus CREATE, ALTER, INDEX, DROP for the initial migration. The team should not grant Grafana a superuser role. The Grafana project documents the minimum privilege set.

Performance implications

Performance implications of a Grafana database migration are not symmetric with the migration’s risk:

• sqlite serialises writes. A deployment that has grown past a few hundred dashboards will see write contention on the sqlite file. Migrating to MySQL or Postgres removes the serialisation point but introduces network latency on every query. For most deployments the trade-off is correct; for deployments under ~10k dashboard rows it is not.
• Connection pool sizing. The default max_open_conn=10 is too small for a Grafana instance that serves hundreds of dashboards concurrently. The team must size the pool to match the Grafana instance’s concurrency. Too small: query latency spikes. Too large: the database runs out of connections from other clients.
• Dashboard query latency. A dashboard that issues multiple datasource queries in parallel will issue multiple database queries in parallel. The team must ensure the database server has enough CPU and IOPS to handle the burst.

The release note will not call out performance implications of database work specifically. The validation step is where the team notices.

Production guidance

• Plan the migration as its own change. A database migration is not a sub-step of a Grafana upgrade. It is a separate change with its own rollback, its own snapshot, its own canary, and its own post-mortem.
• Snapshot the database first. The database snapshot is the rollback artefact. It is useless if it is taken after the bad state has been written.
• Verify the schema before starting the binary. Run grafana-server -migrations against a copy of the production database in staging. If the migration fails in staging, it will fail in production.
• Confirm row counts after the cutover. The cutover is not complete until the row counts match the pre-migration snapshot.

Verification

You should now be able to answer:

• What three situations force a backend migration from sqlite to MySQL or Postgres?
• What is the difference between a backend migration and a schema migration?
• Why is the pre-migration row count the single most reliable check the team has?
• Why does a failed schema migration stop Grafana from starting?

Quiz