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"
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
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â
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
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;
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;
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.