Error starting grafana with a new PostgreSQL 10.9 database

Somewhere around 18 June 2019 a new PostgreSQL package (version 10.9-0ubuntu0.18.04.1) was released for Ubuntu 18.04. I have no information about release dates for other distributions.

PostgreSQL 10.9 has introduced a number of changes, including Fix ALTER COLUMN TYPE failure with a partial exclusion constraint.

When Grafana (of any supported version) is installed and configured to use a PostgreSQL 10.9 database, the above commit makes Grafana migrator fail on migration “Update user table charset” (id 14), the relevant log entries are:

t=2019-06-24T16:20:09+0000 lvl=eror msg="Executing migration failed" logger=migrator id="Update user table charset" error="pq: relation \"UQE_user_login\" already exists"
t=2019-06-24T16:20:09+0000 lvl=eror msg="Exec failed" logger=migrator error="pq: relation \"UQE_user_login\" already exists" sql="ALTER TABLE \"user\" ALTER \"login\" TYPE VARCHAR(190), ALTER \"email\" TYPE VARCHAR(190), ALTER \"name\" TYPE VARCHAR(255), ALTER \"password\" TYPE VARCHAR(255), ALTER \"salt\" TYPE VARCHAR(50), ALTER \"rands\" TYPE VARCHAR(50), ALTER \"company\" TYPE VARCHAR(255), ALTER \"theme\" TYPE VARCHAR(255);"
t=2019-06-24T16:20:09+0000 lvl=eror msg="Server shutdown" logger=server reason="Service init failed: Migration failed err: pq: relation \"UQE_user_login\" already exists"

AFAIU, the error is thrown because because ‘UQE_user_email’ and ‘UQE_user_login’ are indexes with constraints (with the UNIQUE clause):

CREATE UNIQUE INDEX "UQE_user_email" ON public."user" USING btree (email);
CREATE UNIQUE INDEX "UQE_user_login" ON public."user" USING btree (login);

but I am not sure how to work this around… The respective migration query is:

ALTER TABLE "user"
ALTER "login" TYPE VARCHAR(190),
ALTER "email" TYPE VARCHAR(190),
ALTER "name" TYPE VARCHAR(255),
ALTER "password" TYPE VARCHAR(255),
ALTER "salt" TYPE VARCHAR(50),
ALTER "rands" TYPE VARCHAR(50),
ALTER "company" TYPE VARCHAR(255),
ALTER "theme" TYPE VARCHAR(255);

The error is thrown whenever fields “login” and “email” are present together in the same statement. So one option could be to break the statement into two:

ALTER TABLE "user"
ALTER "login" TYPE VARCHAR(190);

ALTER TABLE "user"
ALTER "email" TYPE VARCHAR(190),
ALTER "name" TYPE VARCHAR(255),
ALTER "password" TYPE VARCHAR(255),
ALTER "salt" TYPE VARCHAR(50),
ALTER "rands" TYPE VARCHAR(50),
ALTER "company" TYPE VARCHAR(255),
ALTER "theme" TYPE VARCHAR(255);

but I am not sure if it’s the right approach. Maybe it is better to first drop the indexes, do ALTER TABLE and then recreate the indexes (but I believe the same is implicitly done by ALTER TABLE).

The most annoying is that all supported Grafana versions have to be fixed as they all fail with PostgreSQL 10.9. I am surprised no one has reported up to this point.

You are not only one: Grafana install fails when recent version of PostgreSQL is used as backend

Oh, really nice to know, thanks! That thread is more useful than mine.

Interestingly, I tried to search for (a looser)

relation “UQE_user_login” already exists

and (a stricter)

pq: relation “UQE_user_login” already exists

on Friday (both are direct quotes from Grafana log), found nothing directly related.

Before posting I tried to search again for a stricter variant with same results. Also searched this forum, but obviously using some different expression.

I wonder if postgresql.org mailing lists are indexed at all (still not in Google results).