Thank you

[andsens]

Hi there. I just wanted to say thank you for making this tool available for everybody.
I don't know how long it would take to migrate without it, but with it I migrated our db in less than 5 minutes. So thank you very much!
I did run into one small issue with auth-proxy-sync-ttl:... entries in the cache_data table, but since it was called "cache_data" I decided to just clear it and move on. Here's the error:

INFO[2021-01-21T13:23:54+01:00] 📁 SQLlite file: grafana.db
INFO[2021-01-21T13:23:54+01:00] 📁 Dump directory: /tmp
INFO[2021-01-21T13:23:54+01:00] ✅ sqlite3 command exists
INFO[2021-01-21T13:23:54+01:00] ✅ sqlite3 database dumped to /tmp/grafana.sql
INFO[2021-01-21T13:23:54+01:00] ✅ CREATE statements removed from dump file
INFO[2021-01-21T13:23:55+01:00] ✅ sqlite3 dump sanitized
INFO[2021-01-21T13:23:56+01:00] ✅ migration_log statements removed
INFO[2021-01-21T13:23:56+01:00] ✅ char keyword transformed
INFO[2021-01-21T13:23:56+01:00] ✅ hex-encoded data decoded
FATAL[2021-01-21T13:24:27+01:00] ❌ pq: invalid byte sequence for encoding "UTF8": 0xff INSERT INTO "cache_data" VALUES('auth-proxy-sync-ttl:f00ca0902d33e16aab78c5380b18d49f','
cachedItemValint64"',3600,1611228158) - failed to import dump file to Postgres.

I still have the file, so if you would like for me to test a fix later on I'm up for it.

EDIT: Almost forgot to mention: This is on Grafana v7.3.2 and Postgres v12.2

[nevarsin]

I write here because I also would like to thank you. You saved me a lot of JSON copy/pasting. I had the same issue as OP (Grafana 7.4.3 + Postgres11 on Azure) but with a record on the dashboard_snapshot table. Deleted the record and everything went smooth. Thanks again!

[wbh1]

Thank you for reporting this @nevarsin. Did you get the same invalid byte sequence for encoding "UTF8" error?

[nevarsin]

Exactly.

[kevinnoel-be]

Hello, thanks for this tool!

We've had the exact same issue (Grafana v7.5.7 & Postgres 13.1):

INFO[2021-09-13T13:33:22Z] 📁 SQLlite file: /tmp/grafana.db              
INFO[2021-09-13T13:33:22Z] 📁 Dump directory: /tmp                       
INFO[2021-09-13T13:33:22Z] ✅ sqlite3 command exists                     
INFO[2021-09-13T13:33:33Z] ✅ sqlite3 database dumped to /tmp/grafana.sql 
INFO[2021-09-13T13:33:48Z] ✅ CREATE statements removed from dump file   
INFO[2021-09-13T13:34:41Z] ✅ sqlite3 dump sanitized                     
INFO[2021-09-13T13:35:09Z] ✅ migration_log statements removed           
INFO[2021-09-13T13:35:12Z] ✅ char keyword transformed                   
INFO[2021-09-13T13:36:03Z] ✅ hex-encoded data decoded                   
FATAL[2021-09-13T13:36:06Z] ❌ pq: invalid byte sequence for encoding "UTF8": 0xe1 0xfe 0x0e INSERT INTO "dashboard_snapshot" VALUES(
...

The migration works without issues when dropping the snapshots :tada: We'll see if we can find an easy fix for this otherwise we'll drop the snapshots.

[kevinnoel-be]

After investigating further, this seems to be caused by a new blob column dashboard_snapshot.dashboard_encrypted which contains an encrypted dashboard JSON (vs plain JSON in the dashboard column).

This has been introduced in version 7.3 of Grafana:

• https://grafana.com/docs/grafana/latest/installation/upgrading/#snapshots-database-migration
• https://grafana.com/docs/grafana/latest/administration/configuration/#secret_key
• https://github.com/grafana/grafana/blob/v8.1.3/pkg/models/dashboard_snapshot.go#L32
• https://github.com/grafana/grafana/blob/v8.1.3/pkg/util/encryption.go#L18

We're going to drop those during the migration and keep the sqlite database around if we eventually need those.

sqlite3 grafana.db "delete from dashboard_snapshot where dashboard_encrypted is not null"

[wbh1]

@kevinnoel-be thanks for digging into the history behind this! I'll look more into how to handle this case in the future.

[wbh1]

Closing this issue and tracking in https://github.com/wbh1/grafana-sqlite-to-postgres/issues/34