Migration from sqlite3 to postgres

What happened?
I am trying to move data in tables from sqlite do postgres. The problem with when in table, for example dashboard is field in json format. In table dashboard column data describes dashboard which is written. In sqlite data in column named dats in written in diffrent way than in postgres. In sqlite column data starts with with “7B22…”. When I copy directly value of this column to postgres db grafana doesn’t read it and dashboard is not able to be opened. I have checked that in postgres column data is describerd as “{”".

What was expected to happen?
I am looking for a solution how to migate column which are described in json format in diffrent way depend on db. Why don’t use the same format of json in sqlite and postges?

Steps to reproduce the problem:

  1. Prepare any dashboard in sqlite.
  2. Generate INSERT from sliqte from dashboard tables
  3. Execute command in postgres databases.

Version numbers (grafana, prometheus, graphite, plugins, operating system, etc.):
grafana 9.0

What Grafana version and what operating system are you using?
9.0

What are you trying to achieve?
Migration grafana db from sqlite to postgres.

How are you trying to achieve it?
I am trying to prepare python scrip to change insert genereted by sqlite dump to postgres tables. Grafana schema in postgres is a little diffrent in sqlite and postgres.

What happened?
I am trying to move data in tables from sqlite do postgres. The problem with when in table, for example dashboard is field in json format. In table dashboard column data describes dashboard which is written. In sqlite data in column named dats in written in diffrent way than in postgres. In sqlite column data starts with with “7B22…”. When I copy directly value of this column to postgres db grafana doesn’t read it and dashboard is not able to be opened. I have checked that in postgres column data is describerd as “{”".

What did you expect to happen?
Migration of tables with json decribed in hex in sqlite to json described in ascii in postgres?

Did you follow any online instructions? If so, what is the URL?
All online method doesn’t work. Direct copy from sqlite to postges doesn’t work.

Have you looked at this ?

https://polyglot.jamie.ly/programming/2019/07/01/grafana-sqlite-to-postgres.html

and this

I have checked that and it doesn’t work. Main problem is that emended json in text column is in hex in sqlite. But in postgres grafana write the same json data in ascii. Finally I have done with extrenal tool PDI, when in easy way we can change format of data. But for sure grafana should deliver other simplier solution. I don’t why the same column in sqlite is written in hex, but in postgres in ascii.

1 Like

I think we can sort this out? which table are you having issues with?

For example we can do things via node

const sqlite3 = require('sqlite3').verbose()

const db = new sqlite3.Database('C:/migrate/grafana.db')

db.each("SELECT * FROM dashboard", (err, row) => {
  console.log(row.id + ": " + row.title);
});

Actually the initial part migration is simple

  1. Stop the grafana service
  2. change this in your defaults.ini
# Either "mysql", "postgres" or "sqlite3", it's your choice
type = postgres
host = 127.0.0.1:5432
name = grafana
user = YOUR_USER
password= YOUR_USER_PASSWORD
  1. Start your grafana service
  2. Postgres

Now on to bringing over the data from sqllite, the hard part!

Bringing the data hits a few snags primarily when starting the grafana service after pointing the ini to postgres, all is_* columns are converted to boolean ie

"is_public"	INTEGER NOT NULL DEFAULT 0,

So need to make sure the insert scripts account for that.