Migration from sqlite3 to postgres

Grafana Configuration
1

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.

2

Have you looked at this ?

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

and this

3

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
4

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);
});

image
image921×706 66.5 KB

5

DISCLAIMER: Make sure you fully test this migration in a test environment with actual users logging in and actual user identity management you use.
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
    image
    image560×1599 91.7 KB

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.

6

To account for the int to boolean cast, I ran this command inside PostgreSQL:

update pg_cast set castcontext=‘a’ where casttarget = ‘boolean’::regtype;

Then after importing data:

**update pg_cast set castcontext='e' where casttarget = 'boolean'::regtype;**

This is where I got it from:

I was in Ubuntu 20.04 and there is an error with pgloader version 3.6.1 when migrating data only

1 Like
7

Hi,
This is unrelated to the question asked above.

I am using Default Sqlite DB for my Grafana. I am worried as the number of dashboard grows will the performance impacted if yes should I change to mysql. Can someone please suggest me which one is better mysql or sqlite ?

3 Likes
8

Definitely mysql