I am in the process of migrating my sqlite3 data (Grafana In-built) into my newly created external postgres database.
I used this URL as reference - https://medium.com/@yashraj45dighe/migrate-grafana-sqlite-db-to-postgresql-for-high-availability-c7303c3750ff
After restoring the schema from into postgres, I am using pgloader to copy data from my sqlite to postgres
I am using pgloader main.load to perform this.
Below is the content of my main.load
load database
from sqlite:/home/XXXXXXX/grafana.db
into postgresql://grafana:[email protected]:5432/grafana_db
with data only, reset sequences
set work_mem to '16MB', maintenance_work_mem to '512 MB';
I am getting the below error where it says it is not able to open my grafana.db file
Note: I took the grafana.db file from the server running the grafana instance /var/lib/grafana directory
# pgloader main.load
2024-03-28T06:04:42.010000Z LOG pgloader version "3.6.7~devel"
2024-03-28T06:04:42.101001Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///home/XXXXXXXXXXX/sqlite:/home/XXXXXXXXXXX/grafana.db {100673D7C3}>
2024-03-28T06:04:42.101001Z LOG Migrating into #<PGSQL-CONNECTION pgsql://[email protected]:5432/grafana_db {100673D9A3}>
2024-03-28T06:04:42.115001Z ERROR sqlite: Failed to open sqlite file #P"/home/XXXXXXXXXXX/sqlite:/home/XXXXXXXXXX/grafana.db": Could not open sqlite3 database /home/XXXXXXXXXXX/sqlite:/home/XXXXXXXXXXX/grafana.db
Code CANTOPEN: no message.
2024-03-28T06:04:42.115001Z LOG report summary reset
table name errors rows bytes total time
----------------- --------- --------- --------- --------------
fetch 0 0 0.000s
fetch meta data 0 0 0.000s
----------------- --------- --------- --------- --------------
----------------- --------- --------- --------- --------------
What am I missing?
I looked at the permissions of the Grafana.db file and set it to 777. The path is also correct.
Looks like it is the absolute path that was the problem.. It did not need an absolute path. I removed the absolute path and just mentioned the filename since I was running it from the directory where the grafana.db was present.