How to change/migrate database backend from sqlite3 to postgres

In grafana.conf i changed [database] type=sqlite3 to type=postgres

Producing following the output of starting grafana-server,

mail% grafana-server -config /usr/local/etc/grafana.conf

INFO[07-08|19:04:51] Starting Grafana logger=server version=7.5.1 commit=4dffb87 branch=master compiled=2021-07-08T19:04:51+0200
INFO[07-08|19:04:51] Config loaded from logger=settings file=/usr/home/grafana/grafana/conf/defaults.ini
INFO[07-08|19:04:51] Config loaded from logger=settings file=/usr/local/etc/grafana.conf
INFO[07-08|19:04:51] Path Home logger=settings path=/usr/home/grafana/grafana
INFO[07-08|19:04:51] Path Data logger=settings path=/var/db/grafana
INFO[07-08|19:04:51] Path Logs logger=settings path=/var/log/grafana
INFO[07-08|19:04:51] Path Plugins logger=settings path=/var/db/grafana/plugins
INFO[07-08|19:04:51] Path Provisioning logger=settings path=/var/db/grafana/provisioning
INFO[07-08|19:04:51] App mode development logger=settings
INFO[07-08|19:04:51] Connecting to DB logger=sqlstore dbtype=postgres
EROR[07-08|19:04:51] ShowSQL logger=sqlstore.xorm show=show
INFO[07-08|19:04:51] Starting DB migrations logger=migrator
INFO[07-08|19:04:51] [SQL] SELECT tablename FROM pg_tables WHERE tablename = $1 []interface {}{“migration_log”} - took: 57.595848ms logger=sqlstore.xorm
service init failed: failed to check table existence: pq: password authentication failed for user “telegraf”

Did i missed a step or is there a written procedure to follow ?
Is there an implicit or explicit migration function available ?
I have an empty telegraf database and telegraf superuser configured in postgresql,
so telegraf should be able to create all necesarry databases and tables.
Or maybe I must disable some logging ?

Maybe interesting detail.
My postgresql version is the stable,
postgresql12-server-12.7_1
[PS: Offcourse I’m not going to downgrade the postgresql version]

Additionals logs,
mail% /usr/home/grafana/startserver
INFO[07-09|02:32:21] Starting Grafana logger=server version=7.5.1 commit=4dffb87 branch=master compiled=2021-07-09T02:32:21+0200
INFO[07-09|02:32:21] Config loaded from logger=settings file=/usr/home/grafana/grafana/conf/defaults.ini
INFO[07-09|02:32:21] Config loaded from logger=settings file=/usr/local/etc/grafana.conf
INFO[07-09|02:32:21] Path Home logger=settings path=/usr/home/grafana/grafana
INFO[07-09|02:32:21] Path Data logger=settings path=/var/db/grafana
INFO[07-09|02:32:21] Path Logs logger=settings path=/var/log/grafana
INFO[07-09|02:32:21] Path Plugins logger=settings path=/var/db/grafana/plugins
INFO[07-09|02:32:21] Path Provisioning logger=settings path=/var/db/grafana/provisioning
INFO[07-09|02:32:21] App mode development logger=settings
INFO[07-09|02:32:21] Connecting to DB logger=sqlstore dbtype=postgres
EROR[07-09|02:32:21] ShowSQL logger=sqlstore.xorm show=show
INFO[07-09|02:32:21] Starting DB migrations logger=migrator
INFO[07-09|02:32:21] [SQL] SELECT tablename FROM pg_tables WHERE tablename = $1 []interface {}{“migration_log”} - took: 7.464462ms logger=sqlstore.xorm
INFO[07-09|02:32:21] [SQL] SELECT “id”, “migration_id”, “sql”, “success”, “error”, “timestamp” FROM “migration_log” - took: 884.715µs logger=sqlstore.xorm
INFO[07-09|02:32:21] migrations completed logger=migrator performed=0 skipped=279 duration=1.080878ms
INFO[07-09|02:32:21] [SQL] SELECT tablename FROM pg_tables logger=sqlstore.xorm
INFO[07-09|02:32:21] [SQL] SELECT COUNT(id) AS Count FROM “user” - took: 594.453µs logger=sqlstore.xorm
INFO[07-09|02:32:21] [SQL] SELECT tablename FROM pg_tables logger=sqlstore.xorm
INFO[07-09|02:32:21] Starting plugin search logger=plugins
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184144+02:00”,“queryType”:“exponential_heatmap_bucket_data”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184198+02:00”,“queryType”:“linear_heatmap_bucket_data”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184215+02:00”,“queryType”:“random_walk”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184228+02:00”,“queryType”:“predictable_pulse”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184241+02:00”,“queryType”:“predictable_csv_wave”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184253+02:00”,“queryType”:“random_walk_table”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184265+02:00”,“queryType”:“slow_query”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184278+02:00”,“queryType”:“no_data_points”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184293+02:00”,“queryType”:“datapoints_outside_range”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184308+02:00”,“queryType”:“manual_entry”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184321+02:00”,“queryType”:“csv_metric_values”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184333+02:00”,“queryType”:“streaming_client”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184345+02:00”,“queryType”:“live”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184360+02:00”,“queryType”:“grafana_api”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184374+02:00”,“queryType”:“arrow”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184395+02:00”,“queryType”:“annotations”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184409+02:00”,“queryType”:“table_static”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184421+02:00”,“queryType”:“random_walk_with_error”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184432+02:00”,“queryType”:“server_error_500”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184443+02:00”,“queryType”:“logs”}
{"@level":“debug”,"@message":“datasource: registering query type handler”,"@timestamp":“2021-07-09T02:32:22.184454+02:00”,“queryType”:“node_graph”}
{"@level":“debug”,"@message":“datasource: registering query type fallback handler”,"@timestamp":“2021-07-09T02:32:22.184468+02:00”}
INFO[07-09|02:32:22] [SQL] SELECT “id”, “operation_uid”, “last_execution”, “version” FROM “server_lock” WHERE (operation_uid = $1) []interface {}{“cleanup expired auth tokens”} - took: 1.033955ms logger=sqlstore.xorm
INFO[07-09|02:32:22] HTTP Server Listen logger=http.server address=127.0.0.1:3000 protocol=http subUrl= socket=
INFO[07-09|02:32:22] [SQL] SELECT org_id, plugin_id, enabled, pinned, plugin_version
FROM plugin_setting - took: 6.208157ms logger=sqlstore.xorm
INFO[07-09|02:32:22] [SQL] SELECT “id”, “dashboard_id”, “name”, “external_id”, “check_sum”, “updated” FROM “dashboard_provisioning” - took: 6.448882ms logger=sqlstore.xorm
INFO[07-09|02:32:22] [SQL]
SELECT role AS bitrole, active, COUNT(role) AS count FROM
(SELECT last_seen_at>$1 AS active, SUM(role) AS role
FROM (SELECT
u.id,
CASE org_user.role
WHEN ‘Admin’ THEN 4
WHEN ‘Editor’ THEN 2
ELSE 1
END AS role,
u.last_seen_at
FROM “user” AS u INNER JOIN org_user ON org_user.user_id = u.id
GROUP BY u.id, u.last_seen_at, org_user.role) AS t2
GROUP BY id, last_seen_at) AS t1
GROUP BY active, role; []interface {}{time.Time{wall:0xc02820e58b1795ee, ext:-2591999676398434, loc:(time.Location)(0x39821e0)}} - took: 9.916762ms logger=sqlstore.xorm
INFO[07-09|02:32:22] [SQL] SELECT (SELECT COUNT(
) FROM “user”) AS users,(SELECT COUNT() FROM “org”) AS orgs,(SELECT COUNT() FROM “data_source”) AS datasources,(SELECT COUNT() FROM “star”) AS stars,(SELECT COUNT() FROM “playlist”) AS playlists,(SELECT COUNT() FROM “alert”) AS alerts,(SELECT COUNT() FROM “user” WHERE last_seen_at > $1) AS active_users,(SELECT COUNT(id) FROM “dashboard” WHERE is_folder = $2) AS dashboards,(SELECT COUNT(id) FROM “dashboard” WHERE is_folder = $3) AS folders,(
SELECT COUNT(acl.id)
FROM “dashboard_acl” AS acl
INNER JOIN “dashboard” AS d
ON d.id = acl.dashboard_id
WHERE d.is_folder = $4
) AS dashboard_permissions,(
SELECT COUNT(acl.id)
FROM “dashboard_acl” AS acl
INNER JOIN “dashboard” AS d
ON d.id = acl.dashboard_id
WHERE d.is_folder = $5
) AS folder_permissions,(SELECT COUNT(id) FROM “dashboard_provisioning”) AS provisioned_dashboards,(SELECT COUNT(id) FROM “dashboard_snapshot”) AS snapshots,(SELECT COUNT(id) FROM “dashboard_version”) AS dashboard_versions,(SELECT COUNT(id) FROM “annotation”) AS annotations,(SELECT COUNT(id) FROM “team”) AS teams,(SELECT COUNT(id) FROM “user_auth_token”) AS auth_tokens,1 AS admins, 0 AS editors, 0 AS viewers, 1 AS active_admins, 0 AS active_editors, 0 AS active_viewers []interface {}{time.Time{wall:0xc02820e58b164542, ext:-2591999676484690, loc:(*time.Location)(0x39821e0)}, “false”, “true”, “false”, “true”} - took: 1.717017ms logger=sqlstore.xorm
EROR[07-09|02:32:22] Failed to get system stats logger=metrics error=“pq: invalid input syntax for type bigint: “false””
INFO[07-09|02:32:23] [SQL] select * from alert - took: 1.062489ms logger=sqlstore.xorm
INFO[07-09|02:32:33] [SQL] select * from alert - took: 924.258µs logger=sqlstore.xorm
INFO[07-09|02:32:43] [SQL] select * from alert - took: 463.952µs logger=sqlstore.xorm
INFO[07-09|02:32:53] [SQL] select * from alert - took: 17.229185ms logger=sqlstore.xorm
INFO[07-09|02:33:03] [SQL] select * from alert - took: 359.787µs logger=sqlstore.xorm
INFO[07-09|02:33:13] [SQL] select * from alert - took: 474.25µs logger=sqlstore.xorm

pq: operator does not exist: bigint = boolean

[ Sidenote, using mariadb105-server as backend works fine]

After upgrade of postgresql to version postgresql12-server-12.7_1 and grafana to version grafana8-8.0.2 the issue is fixed and everything is functional.

No it does not work. The graphs are not visible. Reverting back to working mysql backend.

Problem was fixed by dropping the influx database and re-creating it.
By dropping the postgresql database and re-creating it.
It seems the migrator did not update the schema but could work with an empty database.

1 Like