How to change/migrate database backend from sqlite3 to postgres

Grafana PostgreSQL
1

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 ?

2

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]

3

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

4

pq: operator does not exist: bigint = boolean

5

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

6

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.

7

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

8

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
9

This topic was automatically closed after 365 days. New replies are no longer allowed.