Database migration error

shruti · December 27, 2022, 8:17am

After migrating the database grafana.db to postgres .not able to connect to grafana UI .Below are the logs .

logger=ngalert.scheduler t=2022-12-27T05:52:30.001205349Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=ngalert.scheduler t=2022-12-27T05:52:40.001988867Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=ngalert.scheduler t=2022-12-27T05:52:50.00117366Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=ngalert.scheduler t=2022-12-27T05:53:00.001425147Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=ngalert.scheduler t=2022-12-27T05:53:10.001816858Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=ngalert.scheduler t=2022-12-27T05:53:20.001094075Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=ngalert.scheduler t=2022-12-27T05:53:30.00109153Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=ngalert.scheduler t=2022-12-27T05:53:40.001873823Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=ngalert.scheduler t=2022-12-27T05:53:50.002591052Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=ngalert.scheduler t=2022-12-27T05:54:00.001314188Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=ngalert.scheduler t=2022-12-27T05:54:10.00109185Z level=error msg="Failed to update alert rules" error="failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint"
logger=server t=2022-12-27T05:54:19.416745688Z level=info msg="Shutdown started" reason="System signal: terminated"
logger=tracing t=2022-12-27T05:54:19.417063469Z level=info msg="Closing tracing"
logger=ticker t=2022-12-27T05:54:19.418242915Z level=info msg=stopped last_tick=2022-12-27T05:54:10Z
logger=settings t=2022-12-27T05:54:19.722925962Z level=info msg="Starting Grafana" version=9.3.2 commit=21c1d14e91 branch=HEAD compiled=2022-12-14T10:40:18Z
logger=settings t=2022-12-27T05:54:19.723887172Z level=info msg="Config loaded from" file=/usr/share/grafana/conf/defaults.ini
logger=settings t=2022-12-27T05:54:19.724148235Z level=info msg="Config loaded from" file=/etc/grafana/grafana.ini
logger=settings t=2022-12-27T05:54:19.724398472Z level=info msg="Config overridden from command line" arg="default.paths.data=/var/lib/grafana"
logger=settings t=2022-12-27T05:54:19.724692883Z level=info msg="Config overridden from command line" arg="default.paths.logs=/var/log/grafana"
logger=settings t=2022-12-27T05:54:19.724800886Z level=info msg="Config overridden from command line" arg="default.paths.plugins=/var/lib/grafana/plugins"
logger=settings t=2022-12-27T05:54:19.724924943Z level=info msg="Config overridden from command line" arg="default.paths.provisioning=/etc/grafana/provisioning"
logger=settings t=2022-12-27T05:54:19.725037041Z level=info msg="Path Home" path=/usr/share/grafana
logger=settings t=2022-12-27T05:54:19.725507939Z level=info msg="Path Data" path=/var/lib/grafana
logger=settings t=2022-12-27T05:54:19.725743058Z level=info msg="Path Logs" path=/var/log/grafana
logger=settings t=2022-12-27T05:54:19.725977697Z level=info msg="Path Plugins" path=/var/lib/grafana/plugins
logger=settings t=2022-12-27T05:54:19.726183013Z level=info msg="Path Provisioning" path=/etc/grafana/provisioning
logger=settings t=2022-12-27T05:54:19.726253432Z level=info msg="App mode production"
logger=sqlstore t=2022-12-27T05:54:19.726367383Z level=info msg="Connecting to DB" dbtype=postgres
logger=migrator t=2022-12-27T05:54:19.75039776Z level=info msg="Starting DB migrations"
logger=migrator t=2022-12-27T05:54:19.758028715Z level=info msg="migrations completed" performed=0 skipped=464 duration=1.36509ms
logger=plugin.loader t=2022-12-27T05:54:19.842013249Z level=info msg="Plugin registered" pluginID=input
logger=plugin.loader t=2022-12-27T05:54:19.84406579Z level=warn msg="Skipping loading plugin due to problem with signature" pluginID=grafana-image-renderer status=unsigned
logger=secrets t=2022-12-27T05:54:19.844625285Z level=info msg="Envelope encryption state" enabled=true currentprovider=secretKey.v1
logger=query_data t=2022-12-27T05:54:19.848171436Z level=info msg="Query Service initialization"
logger=live.push_http t=2022-12-27T05:54:19.853243701Z level=info msg="Live Push Gateway initialization"
logger=infra.usagestats.collector t=2022-12-27T05:54:20.246270617Z level=info msg="registering usage stat providers" usageStatsProvidersLen=2
logger=server t=2022-12-27T05:54:20.246836042Z level=info msg="Writing PID file" path=/run/grafana/grafana-server.pid pid=113347
logger=provisioning.alerting t=2022-12-27T05:54:20.247828931Z level=info msg="starting to provision alerting"
logger=provisioning.alerting t=2022-12-27T05:54:20.24795689Z level=info msg="finished to provision alerting"
logger=http.server t=2022-12-27T05:54:20.252746414Z level=info msg="HTTP Server Listen" address=[::]:3000 protocol=http subUrl= socket=
logger=ngalert.state.manager t=2022-12-27T05:54:20.253157084Z level=info msg="Warming state cache for startup"
logger=grafanaStorageLogger t=2022-12-27T05:54:20.262415611Z level=info msg="storage starting"
logger=ngalert.state.manager t=2022-12-27T05:54:20.30627936Z level=info msg="State cache has been initialized" states=1 duration=53.120545ms
logger=ticker t=2022-12-27T05:54:20.30675273Z level=info msg=starting first_tick=2022-12-27T05:54:30Z
logger=ngalert.multiorg.alertmanager t=2022-12-27T05:54:20.30705766Z level=info msg="starting MultiOrg Alertmanager"
logger=infra.usagestats.collector t=2022-12-27T05:54:20.314234465Z level=error msg="Failed to get system stats" error="pq: operator does not exist: bigint = boolean"

yosiasz · December 27, 2022, 12:57pm

How did you migrate from sqlite to postgres?

shruti · December 28, 2022, 4:36am

using pgloader ,
below is the script used for migrating db

load database.
from sqlite:///var/lib/grafana/grafana.db
into postgresql://grafana_user2:grafana2@127.0.0.1/grafana2
with include drop, create tables, create indexes, reset sequences
set work_mem to ‘16MB’, maintenance_work_mem to ‘512 MB’;

Accordingly the changes is made in grafana.ini file .
type = postgres
host = 127.0.0.1:5432
name = grafana
user = postgres
password = postgres

yosiasz · December 28, 2022, 5:35am

The above might be your issue, some boolean columns are bigint rather than boolean. You will need to fix those

shruti · December 28, 2022, 5:37am

how to solve this issue, what needs to done to get solved kindly let me know plz

shruti · December 28, 2022, 6:47am

kindly let me know please

yosiasz · December 28, 2022, 7:04am

Find all the columns in sqllite that were TRUE FALSE and change them to bit or boolean in postgres

shruti · December 28, 2022, 7:15am

can you please share me the command please

shruti · December 28, 2022, 7:45am

share me the command please …

yosiasz · December 28, 2022, 8:14am

There is no command. It’s a change in table schema data types.

I can’t help you with that sorry

shruti · December 28, 2022, 3:42pm

i have changed all the datatypes to boolean but still the same error logs shows ,.

logger=ngalert.scheduler t=2022-12-28T15:39:50.001100434Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:40:00.002103176Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:40:10.001740013Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:40:20.001823686Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:40:30.001114653Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:40:40.001133874Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:40:50.001144621Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:41:00.001288257Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:41:10.001072101Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:41:20.001127539Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:41:30.001773852Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:41:40.001758634Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:41:50.00109368Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:42:00.003281803Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:42:10.001875772Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:42:20.001042977Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”
logger=ngalert.scheduler t=2022-12-28T15:42:30.001833969Z level=error msg=“Failed to update alert rules” error=“failed to get alert rules: failed to fetch a list of folders that contain alert rules: pq: argument of IS TRUE must be type boolean, not type bigint”

yosiasz · December 28, 2022, 3:53pm

Pleqse share what exactly you chamged?

pooh · December 28, 2022, 3:55pm

Please show us your database table schema so we can see what fields you have
and what datatypes they are.

Antony.

yosiasz · December 28, 2022, 7:55pm

try this approach

Spin up a new grafana
Change ini to point to postgres server
Start/Restart grafana service. Grafana automatically scaffolds postgres for you
Using DB Browser for SQLLite or some other tool export the data only as csv
Then through some script (python or other scripting language) generate one sql file that has the following postgres command for each table based on the exported csv files

COPY dashboard
FROM 'C:\ExportGrafana\dashboard.csv' 
DELIMITER ',' 
CSV HEADER;

After the above I can check by going to the new grafan instance and see my dashboards were migrated

shruti · December 29, 2022, 4:59am

During migration it shows below error

2022-12-28T08:20:18.617000Z ERROR PostgreSQL Database error 42P16: multiple primary keys for table “cache_data” are not allowed
QUERY: ALTER TABLE ADD PRIMARY KEY USING INDEX idx_17388_sqlite_autoindex_cache_data_1;s
2022-12-28T08:20:18.633000Z ERROR PostgreSQL Database error 42P16: multiple primary keys for table “dashboard_public” are not allowed
QUERY: ALTER TABLE dashboard_public ADD PRIMARY KEY USING INDEX idx_17578_sqlite_autoindex_dashboard_public_1;

yosiasz · December 29, 2022, 5:07am

Which migration? Your own or grafana’s.

shruti · December 29, 2022, 5:08am

migration from grafanadb to postgres

yosiasz · December 29, 2022, 5:09am

Yes, but when does this happen? When you start grafana service with a new postgres database or when you run your own script?

shruti · December 29, 2022, 5:10am

when we run the script this below error occurs

2022-12-28T08:20:18.617000Z ERROR PostgreSQL Database error 42P16: multiple primary keys for table “cache_data” are not allowed
QUERY: ALTER TABLE cache_data ADD PRIMARY KEY USING INDEX idx_17388_sqlite_autoindex_cache_data_1;s
2022-12-28T08:20:18.633000Z ERROR PostgreSQL Database error 42P16: multiple primary keys for table “dashboard_public” are not allowed
QUERY: ALTER TABLE dashboard_public ADD PRIMARY KEY USING INDEX idx_17578_sqlite_autoindex_dashboard_public_1;

yosiasz · December 29, 2022, 5:12am

Because you already have that primary key.

I think at this point it would be best to stop and rethink your approach. You have reached a point where you are just hacking hoping it would work, and that is hard to support.