Cannot Save Grafana Datasource - not-null Postgres Error

Question

I no longer seem to be able to save new Datasources in Grafana.

In particular I am trying to add new InfluxDB database as a datasource. When hitting the Add button it pop up an error of Problem! Failed to add datasource in the UI.

The Grafana logs show the following:

t=2018-07-17T09:59:32+0000 lvl=eror msg="Failed to add datasource" logger=context userId=0 orgId=1 uname= error="pq: null value in column \"id\" violates not-null constraint"

Checking the Database logs (PostgreSQL) there is a related error:

2018-07-19 07:12:46 UTC:10.204.145.134(36768):admin@grafana:[477]:DETAIL: Failing row contains (null, 1, 0, influxdb, jenkins, proxy, http://localhost:8086, root, root, jenkins, f, , , f, {}, 2018-07-19 07:12:46, 2018-07-19 07:12:46, f, {}). 2018-07-19 07:12:46 UTC:10.204.145.134(36768):admin@grafana:[477]:STATEMENT: INSERT INTO "data_source"

As you can see the UI seems to be trying to insert null as the index which produces the error.

Although we recently migrated databases (from one PG to another, same version) the config did not change and there don't appear to have been any other ill effects.

EDIT: Seems this actually affects any database operation Grafana tries to perform when it comes to adding new resources. I just had a dev try to import a new dashboard and the PostgreSQL logs show a similar error:

2018-07-19 08:05:07 UTC:10.204.25.220(34412):sharedadmin@grafana:[14263]:DETAIL: Failing row contains (null, 1, pcs-again, PCS-AGAIN, {"__requires":[{"id":"grafana","name":"Grafana","type":"grafana"..., 1, 2018-07-19 08:05:07, 2018-07-19 08:05:07, -1, -1, 0, ).

Danny Roberts Danny Roberts · Accepted Answer · 2018-07-19T09:49:16

After much delving we managed to find the answer. The issues lies within the AWS Database Migration Service (DMS) we used to migrate from one RDS instance to another. It would seems that DMS does not handle PostgreSQL to PostgreSQL well, some caveats can be found in the docs here.

In the case of Grafana the streaming replication did not pick up the column modifiers. One of the migrated tables:

grafana-> \d data_source
                    Table "public.data_source"
       Column        |              Type              | Modifiers
---------------------+--------------------------------+-----------
 id                  | integer                        | not null
 org_id              | bigint                         | not null
 version             | integer                        | not null
 type                | character varying(255)         | not null
 name                | character varying(190)         | not null
 access              | character varying(255)         | not null
 url                 | character varying(255)         | not null
 password            | character varying(255)         |
 user                | character varying(255)         |
 database            | character varying(255)         |
 basic_auth          | boolean                        | not null
 basic_auth_user     | character varying(255)         |
 basic_auth_password | character varying(255)         |
 is_default          | boolean                        | not null
 json_data           | text                           |
 created             | timestamp(6) without time zone | not null
 updated             | timestamp(6) without time zone | not null
 with_credentials    | boolean                        | not null
 secure_json_data    | text                           |
Indexes:
    "data_source_pkey" PRIMARY KEY, btree (id)

and the corresponding table from a non-migrated instance:

grafana=> \d data_source
                                          Table "public.data_source"
       Column        |            Type             |                         Modifiers
---------------------+-----------------------------+-----------------------------------------------------------
 id                  | integer                     | not null default nextval('data_source_id_seq1'::regclass)
 org_id              | bigint                      | not null
 version             | integer                     | not null
 type                | character varying(255)      | not null
 name                | character varying(190)      | not null
 access              | character varying(255)      | not null
 url                 | character varying(255)      | not null
 password            | character varying(255)      |
 user                | character varying(255)      |
 database            | character varying(255)      |
 basic_auth          | boolean                     | not null
 basic_auth_user     | character varying(255)      |
 basic_auth_password | character varying(255)      |
 is_default          | boolean                     | not null
 json_data           | text                        |
 created             | timestamp without time zone | not null
 updated             | timestamp without time zone | not null
 with_credentials    | boolean                     | not null default false
 secure_json_data    | text                        |
Indexes:
    "data_source_pkey1" PRIMARY KEY, btree (id)
    "UQE_data_source_org_id_name" UNIQUE, btree (org_id, name)
    "IDX_data_source_org_id" btree (org_id)

The moral of the story is that DMS is not suitable to all databases, read the documentation thoroughly and in some cases using the native PostgreSQL tools is better.

In order to fix this particular issue, we dropped the database (after making sure we had exports of all the dashboards), re-created it then restarted Grafana.

Cannot Save Grafana Datasource - not-null Postgres Error

2 Answers