Grafana 11.4.0 DB mis-matches

[EdToro74]

Grafana: 11.4.0

1. The data_source table uses an INTEGER for the is_prunable field in Sqlite, but a BOOLEAN in Postgres. This causes the import to fail with:

   pq: column "is_prunable" is of type boolean but expression is of type integer INSERT INTO "data_source" VALUES...

I assume that Postgres wants the field to be true or false instead of 1 or 0.

2. The playlist table has the fields in a different order. In Sqlite it is: uid, created_at, updated_at while in postgres it is created_at, updated_at, uid. Can column names be used in the dump/import to get around this?

[EdToro74]

I was able to get around these two issues by doing the following:

BEFORE RUNNING MIGRATION

In the target postgres server (with schema already created):

ALTER TABLE public.data_source ALTER COLUMN is_prunable DROP DEFAULT;
ALTER TABLE public.data_source ALTER COLUMN is_prunable TYPE integer USING CASE WHEN is_prunable THEN 1 ELSE 0 END;
ALTER TABLE public.data_source ALTER COLUMN is_prunable SET DEFAULT 0;

DROP TABLE public.playlist;

CREATE TABLE public.playlist
(
    id         serial
        PRIMARY KEY,
    name       varchar(255)          NOT NULL,
    interval   varchar(255)          NOT NULL,
    org_id     bigint                NOT NULL,
    uid        varchar(80) DEFAULT 0 NOT NULL,
    created_at bigint      DEFAULT 0 NOT NULL,
    updated_at bigint      DEFAULT 0 NOT NULL
);

ALTER TABLE playlist
    OWNER TO grafana_admin;

CREATE UNIQUE INDEX "UQE_playlist_org_id_uid"
    ON playlist (org_id, uid);

AFTER RUNNING MIGRATION

ALTER TABLE public.data_source ALTER COLUMN is_prunable DROP DEFAULT;
ALTER TABLE public.data_source ALTER COLUMN is_prunable TYPE boolean USING CASE WHEN is_prunable=1 THEN TRUE ELSE FALSE END;
ALTER TABLE public.data_source ALTER COLUMN is_prunable SET DEFAULT FALSE;

This will let the import work, and then revert the tables back to what the postgres Grafana connector expects. Note that we don't try to put the playlist table back in order, as the postgres connector doesn't care about that (as it shouldn't).

[wbh1]

Glad you were able to get it working, @EdToro74 !

If you'd like, you could PR this repo to add that column as one that should be updated here:

grafana-sqlite-to-postgres/pkg/postgresql/tablechanges.go

Lines 105 to 122 in d5ca6fa

	{
	Table: "data_source",
	Columns: []Column{
	{
	Name: "basic_auth",
	},
	{
	Name: "is_default",
	},
	{
	Name: "read_only",
	},
	{
	Name: "with_credentials",
	Default: "false",
	},
	},
	},

[nikitakurtash]

Similarly for version 11.6.1 these requests worked:

ALTER TABLE public.user ALTER COLUMN is_provisioned DROP DEFAULT;
ALTER TABLE public.user ALTER COLUMN is_provisioned TYPE integer USING CASE WHEN is_provisioned THEN 1 ELSE 0 END;
ALTER TABLE public.user ALTER COLUMN is_provisioned SET DEFAULT 0;
ALTER TABLE public.permission ALTER COLUMN identifier TYPE character varying(255);

DROP TABLE public.playlist;

CREATE TABLE public.playlist
(
    id         serial
        PRIMARY KEY,
    name       varchar(255)          NOT NULL,
    interval   varchar(255)          NOT NULL,
    org_id     bigint                NOT NULL,
    uid        varchar(80) DEFAULT 0 NOT NULL,
    created_at bigint      DEFAULT 0 NOT NULL,
    updated_at bigint      DEFAULT 0 NOT NULL
);

ALTER TABLE playlist
    OWNER TO grafana_user;

CREATE UNIQUE INDEX "UQE_playlist_org_id_uid"
    ON playlist (org_id, uid);


ALTER TABLE public.data_source ALTER COLUMN is_prunable DROP DEFAULT;
ALTER TABLE public.data_source ALTER COLUMN is_prunable TYPE integer USING CASE WHEN is_prunable THEN 1 ELSE 0 END;
ALTER TABLE public.data_source ALTER COLUMN is_prunable SET DEFAULT 0;

DROP TABLE public.playlist;

CREATE TABLE public.playlist
(
    id         serial
        PRIMARY KEY,
    name       varchar(255)          NOT NULL,
    interval   varchar(255)          NOT NULL,
    org_id     bigint                NOT NULL,
    uid        varchar(80) DEFAULT 0 NOT NULL,
    created_at bigint      DEFAULT 0 NOT NULL,
    updated_at bigint      DEFAULT 0 NOT NULL
);

ALTER TABLE playlist
    OWNER TO grafana_user;

CREATE UNIQUE INDEX "UQE_playlist_org_id_uid"
    ON playlist (org_id, uid);

ALTER TABLE public.resource_migration_log ALTER COLUMN success DROP DEFAULT;
ALTER TABLE public.resource_migration_log ALTER COLUMN success TYPE integer USING CASE WHEN success THEN 1 ELSE 0 END;
ALTER TABLE public.resource_migration_log ALTER COLUMN success SET DEFAULT 0;

DROP TABLE public.playlist;

CREATE TABLE public.playlist
(
    id         serial
        PRIMARY KEY,
    name       varchar(255)          NOT NULL,
    interval   varchar(255)          NOT NULL,
    org_id     bigint                NOT NULL,
    uid        varchar(80) DEFAULT 0 NOT NULL,
    created_at bigint      DEFAULT 0 NOT NULL,
    updated_at bigint      DEFAULT 0 NOT NULL
);

ALTER TABLE playlist
    OWNER TO grafana_user;

CREATE UNIQUE INDEX "UQE_playlist_org_id_uid"
    ON playlist (org_id, uid);


--- after migration ---

ALTER TABLE public.data_source ALTER COLUMN is_prunable DROP DEFAULT;
ALTER TABLE public.data_source ALTER COLUMN is_prunable TYPE boolean USING CASE WHEN is_prunable=1 THEN TRUE ELSE FALSE END;
ALTER TABLE public.data_source ALTER COLUMN is_prunable SET DEFAULT FALSE;

ALTER TABLE public.user ALTER COLUMN is_provisioned  DROP DEFAULT;
ALTER TABLE public.user ALTER COLUMN is_provisioned  TYPE boolean USING CASE WHEN is_provisioned =1 THEN TRUE ELSE FALSE END;
ALTER TABLE public.user ALTER COLUMN is_provisioned  SET DEFAULT FALSE;

ALTER TABLE public.resource_migration_log ALTER COLUMN success DROP DEFAULT;
ALTER TABLE public.resource_migration_log ALTER COLUMN success TYPE boolean USING CASE WHEN success=1 THEN TRUE ELSE FALSE END;
ALTER TABLE public.resource_migration_log ALTER COLUMN success SET DEFAULT FALSE;

[mateuszdrab]

I have made some changes to this tool and migrated my database without any manual workarounds - the column ordering issue is now addressed.