Grafana incompatable with postgresql 9.6.14

[jhg03a]

What happened:
You can't install Grafana with a Postgres backend when it's running the latest minor revision of Postgresql.

What you expected to happen:
Grafana to install and start properly.

How to reproduce it (as minimally and precisely as possible):
Install postgresql 9.6.14
Install Grafana and configure it to leverage a postgresql session backend
Observe that the systemd service won't start

Anything else we need to know?:

Jun 25 23:27:24 grafana-01 grafana-server[26397]: t=2019-06-25T23:27:24+0000 lvl=info msg="Starting DB migration" logger=migrator
Jun 25 23:27:24 grafana-01 grafana-server[26397]: t=2019-06-25T23:27:24+0000 lvl=info msg="Executing migration" logger=migrator id="Update user table charset"
Jun 25 23:27:24 grafana-01 grafana-server[26397]: t=2019-06-25T23:27:24+0000 lvl=eror msg="Executing migration failed" logger=migrator id="Update user table charset" error="pq: relation \"UQE_user_login\" already exists"
Jun 25 23:27:24 grafana-01 systemd[1]: grafana-server.service: main process exited, code=exited, status=1/FAILURE

Also referenced in the community forums: https://community.grafana.com/t/grafana-install-fails-when-recent-version-of-postgresql-is-used-as-backend/18590

Environment:

• Grafana version: 5.1.3-1 through 6.2.5-1 at least
• Data source type & version: N/A
• OS Grafana is installed on: CentOS7 (also on Postgresql server as well)
• User OS & Browser: N/A
• Grafana plugins: N/A
• Others: N/A

[jhg03a]

Upon further review this doesn't appear to be an issue with Grafana itself from the postgresql bugs mailing list.

[allenleung86]

Upon further review this doesn't appear to be an issue with Grafana itself from the postgresql bugs mailing list.

So what's the solution? thx!

[jhg03a]

In theory a change could be made by grafana to not execute code in the particularly affected path. However because this is in a migration, I don't think that would help. For now the best, unfortunate answer I can think of is just not to use a broken version of Postgresql.

[allenleung86]

ok,so how can i find the appropriate version of postgresql? cause i was forced to use pgsql other than mysql haha，though i found the version of mysql 5.6.44 and 8.0.16 work. londalonda@qq.com From: Jonathan G Date: 2019-07-02 03:16 To: grafana/grafana CC: AllenLeung; Comment Subject: Re: [grafana/grafana] Grafana incompatable with postgresql 9.6.14 (#17771) In theory a change could be made by grafana to not execute code in the particularly affected path. However because this is in a migration, I don't think that would help. For now the best, unfortunate answer I can think of is just not to use a broken version of Postgresql. — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

[jhg03a]

This has nothing to do with mysql. Just don't use the very latest 9.6.14/10.9/11.4. Prior versions within the supported major revisions are fine.

[phumpal]

You can't install Grafana with a Postgres backend when it's running the latest minor revision

AFAICT most package managers don't seem to include previous minor versions (10, 11, 12) w/the exception of the 9.x releases.

Just don't use the very latest 9.6.14/10.9/11.4

I dumped the schema from an older Grafana (4.6.3) install, imported it in PG 11.4 and restarted grafana-server.service (6.2.5). The migrations ran successfully but this could be some weird edge case.

The PG thread you linked to seems to confirm there is a bug. I'm wondering if there isn't something awry w/the migration but have yet to check the code.

BTW, thanks for opening the issue. Glad I'm not the only one.

[jhg03a]

The official yum repository has the last 3-4 minor revisions I believe. I was able to just lock my postgresql version to 9.6.13 for now and it's working again.

[Keshav0690]

Hi,

I am facing the same issue with Grafana 5.3.0 & PostgreSQL 10.9.
Using RPM package for installation, but it seems that RPM package contains the latest release.
Can anyone please guide me how to install 10.8 using RPM packages.

Note: I don't want to build it from source as during the start-up I have shell script that is making updates in conf files & also installing certs to run PostgreSQL on 443 port.

Thank You !!!

[phumpal]

@Keshav0690 there's probably a simpler way to do this (haven't used CentOS/RHEL in years) however you can pin the version in say CentOS 7 similar to

Configuring your YUM repository

https://wiki.postgresql.org/wiki/YUM_Installation#Configure_your_YUM_repository

then

wget https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum localinstall pgdg-redhat-repo-latest.noarch.rpm
yum check-update
yum list available postgresql11-server --showduplicates
yum install postgresql-server-11.3-1PGDG.rhel7 yum-plugin-versionlock
yum versionlock postgresql11*

You could also try creating the Grafana DB and role manually then importing this older schema before running the install or restarting the service.

FWIW this is not official advice; just trying to assist).

[publysher]

Since the migrations seem to be actual no-ops for PostgreSQL, this is a work-around if you're stuck on a broken PostgreSQL instance:

1. Start Grafana. It will fail on the migration "Update user table charset".
2. Run the following:

BEGIN;

INSERT INTO "migration_log" ("migration_id", "sql", "success", "error", "timestamp")
VALUES
    ('Update user table charset', 'ignored', TRUE, '', now()),
    ('Update temp_user table charset', 'ignored', TRUE, '', now()),
    ('Update api_key table charset', 'ignored', TRUE, '', now()),
    ('Update dashboard table charset', 'ignored', TRUE, '', now()),
    ('Update dashboard_snapshot table charset', 'ignored', TRUE, '', now())
;

COMMIT;

3. Start Grafana again. And all should be well.

[phumpal]

Nice and concise @publysher! Thanks :)

[markelog]

Well, it's not a no-op.

If you run this workaround you first start grafana, let it fail on the alter chartset migration, then tell the migration engine that all migrations were successful.

Effectively, this means grafana will be working with corrupted persistent state, since -

1. All subsequent migrations, which follow the length changes, will not run
2. Grafana code might depend on length of those fields, that's why we introduced this specific to mysql change to postgres as well (utf8mb4 encoding #7959).

So yes, grafana will start, but would it work properly? Well, it might, or it could unexpectedly and indescribably fail (in certain cases it will definitely fail).

We talked about it internally and decided to not change the schema but treat this as documentation issue since postgres already landed a fix for it.

However, if it would cause a lot of confusion or there would be great need to use grafana with these postgres versions we might reconsider

[jhg03a]

since postgres already landed a fix for it

Do you have a reference for this? I don't see a resolution on the mailing list or a new minor release as of this date yet.

[Keshav0690]

Hi jhg03a,

You can try installing older version of PostgreSQL i.e 10.8

Install postgresql and run InitDB

RUN rpm -vih https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
RUN yum install yum-plugin-versionlock postgresql10-server-10.8-1PGDG.rhel7 -y
RUN yum versionlock postgresql10-10.8*
RUN yum install -y sudo
postgresql10-10.8-1PGDG.rhel7
RUN yum clean all

Regards,
Keshav

[jhg03a]

Ok. I was verifying I hadn't missed something. So the answer is still downgrade for now.

[markelog]

Do you have a reference for this? I don't see a resolution on the mailing list or a new minor release as of this date yet.

I don't believe they released the new version with the fix, however, the patch already in master -
postgres/postgres@f946a40

So the answer is still downgrade for now.

Yep

[keithf4]

The latest minor release of PostgreSQL came out today (11.5, 10.10, 9.6.15, 9.5.19, 9.4.24) and contains a fix to the ALTER TABLE statement to allow the install of Grafana to proceed without errors anymore.

Updating to the latest minor release only requires a restart and installing the package on most systems will automatically do that for you (Debian/Ubuntu/RHEL/CentOS).

[DouglasHeriot]

Amazon RDS doesn't yet have support for Postgres 11.5, so I'm still interested in a solution that works on 11.4 for now.
If I install on an 11.5 database elsewhere, do a pg_dump and then load it into my 11.4 database, should everything work?

[publysher]

@DouglasHeriot yes it should; just make sure all the migrations have run against your 11.5 database by inspecting the migration_log table.