Как мигрировать БД с sqlite на PG

[OlegAnt736]

Решил перевести на PG и не совсем все получается, нет ли такого опыта или рекомендаций по этому поводу?

[impworks]

Само приложение поддерживает переход только в обратную сторону. На вашем месте я бы поступил примерно следующим образом:

• Взять Claude с MCP для работы со SQLite-базами или аналогичный ИИ-ассистент
• Скормить ему вашу БД из Bonsai, попросить нагенерировать эквивалентные запросы для PostgreSQL
• Закинуть данные в PostgreSQL
• Поправить в конфиге Bonsai - указать ConnectionStrings__UseEmbeddedDatabase=False, а также ConnectionStrings__Database=... со строкой подключения к серверу PostgreSQL
• Перезапустить Bonsai

[D10n1x]

Может кому нужно будет. Я переносил из SQLite в PG следующим образом при помощи DBeaver

1. Сначала создал базу данных в PG использую схему

-- DROP SCHEMA public;

CREATE SCHEMA public AUTHORIZATION bonsai;

COMMENT ON SCHEMA public IS 'standard public schema';

-- DROP SEQUENCE public."AspNetRoleClaims_Id_seq";

CREATE SEQUENCE public."AspNetRoleClaims_Id_seq"
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public."AspNetUserClaims_Id_seq";

CREATE SEQUENCE public."AspNetUserClaims_Id_seq"
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;-- public."AspNetRoles" определение

-- Drop table

-- DROP TABLE public."AspNetRoles";

CREATE TABLE public."AspNetRoles" (
	"Id" text NOT NULL,
	"Name" varchar(256) NULL,
	"NormalizedName" varchar(256) NULL,
	"ConcurrencyStamp" text NULL,
	CONSTRAINT "PK_AspNetRoles" PRIMARY KEY ("Id")
);
CREATE UNIQUE INDEX "RoleNameIndex" ON public."AspNetRoles" USING btree ("NormalizedName");


-- public."DynamicConfig" определение

-- Drop table

-- DROP TABLE public."DynamicConfig";

CREATE TABLE public."DynamicConfig" (
	"Id" uuid NOT NULL,
	"Value" text NULL,
	CONSTRAINT "PK_DynamicConfig" PRIMARY KEY ("Id")
);


-- public."IdentityPasskeyData" определение

-- Drop table

-- DROP TABLE public."IdentityPasskeyData";

CREATE TABLE public."IdentityPasskeyData" (
	"PublicKey" bytea NOT NULL,
	"Name" text NULL,
	"CreatedAt" timestamptz NOT NULL,
	"SignCount" int8 NOT NULL,
	"Transports" _text NULL,
	"IsUserVerified" bool NOT NULL,
	"IsBackupEligible" bool NOT NULL,
	"IsBackedUp" bool NOT NULL,
	"AttestationObject" bytea NOT NULL,
	"ClientDataJson" bytea NOT NULL
);


-- public."JobStates" определение

-- Drop table

-- DROP TABLE public."JobStates";

CREATE TABLE public."JobStates" (
	"Id" uuid NOT NULL,
	"Type" text NULL,
	"ArgumentsType" text NULL,
	"Arguments" text NULL,
	"StartDate" timestamp NOT NULL,
	"FinishDate" timestamp NULL,
	"Success" bool NULL,
	CONSTRAINT "PK_JobStates" PRIMARY KEY ("Id")
);


-- public."OpenIddictApplications" определение

-- Drop table

-- DROP TABLE public."OpenIddictApplications";

CREATE TABLE public."OpenIddictApplications" (
	"Id" text NOT NULL,
	"ApplicationType" varchar(50) NULL,
	"ClientId" varchar(100) NULL,
	"ClientSecret" text NULL,
	"ClientType" varchar(50) NULL,
	"ConcurrencyToken" varchar(50) NULL,
	"ConsentType" varchar(50) NULL,
	"DisplayName" text NULL,
	"DisplayNames" text NULL,
	"JsonWebKeySet" text NULL,
	"Permissions" text NULL,
	"PostLogoutRedirectUris" text NULL,
	"Properties" text NULL,
	"RedirectUris" text NULL,
	"Requirements" text NULL,
	"Settings" text NULL,
	CONSTRAINT "PK_OpenIddictApplications" PRIMARY KEY ("Id")
);
CREATE UNIQUE INDEX "IX_OpenIddictApplications_ClientId" ON public."OpenIddictApplications" USING btree ("ClientId");


-- public."OpenIddictScopes" определение

-- Drop table

-- DROP TABLE public."OpenIddictScopes";

CREATE TABLE public."OpenIddictScopes" (
	"Id" text NOT NULL,
	"ConcurrencyToken" varchar(50) NULL,
	"Description" text NULL,
	"Descriptions" text NULL,
	"DisplayName" text NULL,
	"DisplayNames" text NULL,
	"Name" varchar(200) NULL,
	"Properties" text NULL,
	"Resources" text NULL,
	CONSTRAINT "PK_OpenIddictScopes" PRIMARY KEY ("Id")
);
CREATE UNIQUE INDEX "IX_OpenIddictScopes_Name" ON public."OpenIddictScopes" USING btree ("Name");


-- public."__EFMigrationsHistory" определение

-- Drop table

-- DROP TABLE public."__EFMigrationsHistory";

CREATE TABLE public."__EFMigrationsHistory" (
	"MigrationId" varchar(150) NOT NULL,
	"ProductVersion" varchar(32) NOT NULL,
	CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);


-- public."AspNetRoleClaims" определение

-- Drop table

-- DROP TABLE public."AspNetRoleClaims";

CREATE TABLE public."AspNetRoleClaims" (
	"Id" serial4 NOT NULL,
	"RoleId" text NOT NULL,
	"ClaimType" text NULL,
	"ClaimValue" text NULL,
	CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY ("Id"),
	CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES public."AspNetRoles"("Id") ON DELETE CASCADE
);
CREATE INDEX "IX_AspNetRoleClaims_RoleId" ON public."AspNetRoleClaims" USING btree ("RoleId");


-- public."OpenIddictAuthorizations" определение

-- Drop table

-- DROP TABLE public."OpenIddictAuthorizations";

CREATE TABLE public."OpenIddictAuthorizations" (
	"Id" text NOT NULL,
	"ApplicationId" text NULL,
	"ConcurrencyToken" varchar(50) NULL,
	"CreationDate" timestamptz NULL,
	"Properties" text NULL,
	"Scopes" text NULL,
	"Status" varchar(50) NULL,
	"Subject" varchar(400) NULL,
	"Type" varchar(50) NULL,
	CONSTRAINT "PK_OpenIddictAuthorizations" PRIMARY KEY ("Id"),
	CONSTRAINT "FK_OpenIddictAuthorizations_OpenIddictApplications_Application~" FOREIGN KEY ("ApplicationId") REFERENCES public."OpenIddictApplications"("Id")
);
CREATE INDEX "IX_OpenIddictAuthorizations_ApplicationId_Status_Subject_Type" ON public."OpenIddictAuthorizations" USING btree ("ApplicationId", "Status", "Subject", "Type");


-- public."OpenIddictTokens" определение

-- Drop table

-- DROP TABLE public."OpenIddictTokens";

CREATE TABLE public."OpenIddictTokens" (
	"Id" text NOT NULL,
	"ApplicationId" text NULL,
	"AuthorizationId" text NULL,
	"ConcurrencyToken" varchar(50) NULL,
	"CreationDate" timestamptz NULL,
	"ExpirationDate" timestamptz NULL,
	"Payload" text NULL,
	"Properties" text NULL,
	"RedemptionDate" timestamptz NULL,
	"ReferenceId" varchar(100) NULL,
	"Status" varchar(50) NULL,
	"Subject" varchar(400) NULL,
	"Type" varchar(50) NULL,
	CONSTRAINT "PK_OpenIddictTokens" PRIMARY KEY ("Id"),
	CONSTRAINT "FK_OpenIddictTokens_OpenIddictApplications_ApplicationId" FOREIGN KEY ("ApplicationId") REFERENCES public."OpenIddictApplications"("Id"),
	CONSTRAINT "FK_OpenIddictTokens_OpenIddictAuthorizations_AuthorizationId" FOREIGN KEY ("AuthorizationId") REFERENCES public."OpenIddictAuthorizations"("Id")
);
CREATE INDEX "IX_OpenIddictTokens_ApplicationId_Status_Subject_Type" ON public."OpenIddictTokens" USING btree ("ApplicationId", "Status", "Subject", "Type");
CREATE INDEX "IX_OpenIddictTokens_AuthorizationId" ON public."OpenIddictTokens" USING btree ("AuthorizationId");
CREATE UNIQUE INDEX "IX_OpenIddictTokens_ReferenceId" ON public."OpenIddictTokens" USING btree ("ReferenceId");


-- public."AspNetUserClaims" определение

-- Drop table

-- DROP TABLE public."AspNetUserClaims";

CREATE TABLE public."AspNetUserClaims" (
	"Id" serial4 NOT NULL,
	"UserId" text NOT NULL,
	"ClaimType" text NULL,
	"ClaimValue" text NULL,
	CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY ("Id")
);
CREATE INDEX "IX_AspNetUserClaims_UserId" ON public."AspNetUserClaims" USING btree ("UserId");


-- public."AspNetUserLogins" определение

-- Drop table

-- DROP TABLE public."AspNetUserLogins";

CREATE TABLE public."AspNetUserLogins" (
	"LoginProvider" text NOT NULL,
	"ProviderKey" text NOT NULL,
	"ProviderDisplayName" text NULL,
	"UserId" text NOT NULL,
	CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey")
);
CREATE INDEX "IX_AspNetUserLogins_UserId" ON public."AspNetUserLogins" USING btree ("UserId");


-- public."AspNetUserRoles" определение

-- Drop table

-- DROP TABLE public."AspNetUserRoles";

CREATE TABLE public."AspNetUserRoles" (
	"UserId" text NOT NULL,
	"RoleId" text NOT NULL,
	CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY ("UserId", "RoleId")
);
CREATE INDEX "IX_AspNetUserRoles_RoleId" ON public."AspNetUserRoles" USING btree ("RoleId");


-- public."AspNetUserTokens" определение

-- Drop table

-- DROP TABLE public."AspNetUserTokens";

CREATE TABLE public."AspNetUserTokens" (
	"UserId" text NOT NULL,
	"LoginProvider" text NOT NULL,
	"Name" text NOT NULL,
	"Value" text NULL,
	CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name")
);


-- public."AspNetUsers" определение

-- Drop table

-- DROP TABLE public."AspNetUsers";

CREATE TABLE public."AspNetUsers" (
	"Id" text NOT NULL,
	"UserName" varchar(256) NULL,
	"NormalizedUserName" varchar(256) NULL,
	"Email" varchar(256) NULL,
	"NormalizedEmail" varchar(256) NULL,
	"EmailConfirmed" bool NOT NULL,
	"PasswordHash" text NULL,
	"SecurityStamp" text NULL,
	"ConcurrencyStamp" text NULL,
	"PhoneNumber" text NULL,
	"PhoneNumberConfirmed" bool NOT NULL,
	"TwoFactorEnabled" bool NOT NULL,
	"LockoutEnd" timestamptz NULL,
	"LockoutEnabled" bool NOT NULL,
	"AccessFailedCount" int4 NOT NULL,
	"IsValidated" bool NOT NULL,
	"FirstName" varchar(256) NULL,
	"LastName" varchar(256) NULL,
	"MiddleName" varchar(256) NULL,
	"Birthday" varchar(10) NULL,
	"PageId" uuid NULL,
	"AuthType" int4 DEFAULT 0 NOT NULL,
	CONSTRAINT "PK_AspNetUsers" PRIMARY KEY ("Id")
);
CREATE INDEX "EmailIndex" ON public."AspNetUsers" USING btree ("NormalizedEmail");
CREATE INDEX "IX_AspNetUsers_PageId" ON public."AspNetUsers" USING btree ("PageId");
CREATE UNIQUE INDEX "UserNameIndex" ON public."AspNetUsers" USING btree ("NormalizedUserName");


-- public."Changes" определение

-- Drop table

-- DROP TABLE public."Changes";

CREATE TABLE public."Changes" (
	"Id" uuid NOT NULL,
	"GroupId" uuid NULL,
	"Date" timestamptz NOT NULL,
	"AuthorId" text NOT NULL,
	"EntityType" int4 NOT NULL,
	"RevertedChangesetId" uuid NULL,
	"EditedPageId" uuid NULL,
	"EditedMediaId" uuid NULL,
	"EditedRelationId" uuid NULL,
	"UpdatedState" text NULL,
	"AppUserId" text NULL,
	"ChangeType" int4 DEFAULT 0 NOT NULL,
	"IsAIGenerated" bool DEFAULT false NOT NULL,
	CONSTRAINT "PK_Changes" PRIMARY KEY ("Id")
);
CREATE INDEX "IX_Changes_AppUserId" ON public."Changes" USING btree ("AppUserId");
CREATE INDEX "IX_Changes_AuthorId" ON public."Changes" USING btree ("AuthorId");
CREATE INDEX "IX_Changes_EditedMediaId" ON public."Changes" USING btree ("EditedMediaId");
CREATE INDEX "IX_Changes_EditedPageId" ON public."Changes" USING btree ("EditedPageId");
CREATE INDEX "IX_Changes_EditedRelationId" ON public."Changes" USING btree ("EditedRelationId");


-- public."LivingBeingOverviews" определение

-- Drop table

-- DROP TABLE public."LivingBeingOverviews";

CREATE TABLE public."LivingBeingOverviews" (
	"PageId" uuid NOT NULL,
	"Gender" bool NULL,
	"BirthDate" text NULL,
	"DeathDate" text NULL,
	"IsDead" bool NOT NULL,
	"ShortName" text NULL,
	"MaidenName" text NULL,
	CONSTRAINT "PK_LivingBeingOverview" PRIMARY KEY ("PageId")
);


-- public."Media" определение

-- Drop table

-- DROP TABLE public."Media";

CREATE TABLE public."Media" (
	"Id" uuid NOT NULL,
	"Key" varchar(30) NOT NULL,
	"Type" int4 NOT NULL,
	"MimeType" varchar(100) NOT NULL,
	"FilePath" varchar(300) NOT NULL,
	"Date" varchar(30) NULL,
	"Title" text NULL,
	"Description" text NULL,
	"UploadDate" timestamptz NOT NULL,
	"UploaderId" text NULL,
	"IsProcessed" bool NOT NULL,
	"IsDeleted" bool NOT NULL,
	"NormalizedTitle" text NULL,
	CONSTRAINT "PK_Media" PRIMARY KEY ("Id")
);
CREATE INDEX "IX_Media_IsDeleted" ON public."Media" USING btree ("IsDeleted");
CREATE UNIQUE INDEX "IX_Media_Key" ON public."Media" USING btree ("Key");
CREATE INDEX "IX_Media_UploaderId" ON public."Media" USING btree ("UploaderId");


-- public."MediaTags" определение

-- Drop table

-- DROP TABLE public."MediaTags";

CREATE TABLE public."MediaTags" (
	"Id" uuid NOT NULL,
	"MediaId" uuid NOT NULL,
	"ObjectId" uuid NULL,
	"ObjectTitle" text NULL,
	"Type" int4 NOT NULL,
	"Coordinates" varchar(100) NULL,
	CONSTRAINT "PK_MediaTags" PRIMARY KEY ("Id")
);
CREATE INDEX "IX_MediaTags_MediaId" ON public."MediaTags" USING btree ("MediaId");
CREATE INDEX "IX_MediaTags_ObjectId" ON public."MediaTags" USING btree ("ObjectId");


-- public."PageAliases" определение

-- Drop table

-- DROP TABLE public."PageAliases";

CREATE TABLE public."PageAliases" (
	"Id" uuid NOT NULL,
	"Title" varchar(200) NOT NULL,
	"Key" varchar(200) NOT NULL,
	"Order" int4 NOT NULL,
	"PageId" uuid NOT NULL,
	"NormalizedTitle" text NULL,
	CONSTRAINT "PK_PageAliases" PRIMARY KEY ("Id")
);
CREATE UNIQUE INDEX "IX_PageAliases_Key" ON public."PageAliases" USING btree ("Key");
CREATE INDEX "IX_PageAliases_PageId" ON public."PageAliases" USING btree ("PageId");


-- public."PageDrafts" определение

-- Drop table

-- DROP TABLE public."PageDrafts";

CREATE TABLE public."PageDrafts" (
	"Id" uuid NOT NULL,
	"PageId" uuid NULL,
	"UserId" text NOT NULL,
	"Content" text NOT NULL,
	"LastUpdateDate" timestamptz NOT NULL,
	CONSTRAINT "PK_PageDrafts" PRIMARY KEY ("Id")
);
CREATE INDEX "IX_PageDrafts_PageId" ON public."PageDrafts" USING btree ("PageId");
CREATE INDEX "IX_PageDrafts_UserId" ON public."PageDrafts" USING btree ("UserId");


-- public."PageReferences" определение

-- Drop table

-- DROP TABLE public."PageReferences";

CREATE TABLE public."PageReferences" (
	"Id" uuid NOT NULL,
	"SourceId" uuid NOT NULL,
	"DestinationId" uuid NOT NULL,
	CONSTRAINT "PK_PageReferences" PRIMARY KEY ("Id")
);
CREATE INDEX "IX_PageReferences_DestinationId" ON public."PageReferences" USING btree ("DestinationId");
CREATE INDEX "IX_PageReferences_SourceId" ON public."PageReferences" USING btree ("SourceId");


-- public."Pages" определение

-- Drop table

-- DROP TABLE public."Pages";

CREATE TABLE public."Pages" (
	"Id" uuid NOT NULL,
	"Title" varchar(200) NOT NULL,
	"Key" varchar(200) NOT NULL,
	"Type" int4 NOT NULL,
	"Description" text NULL,
	"Facts" text NULL,
	"MainPhotoId" uuid NULL,
	"CreationDate" timestamptz NOT NULL,
	"LastUpdateDate" timestamptz NOT NULL,
	"IsDeleted" bool NOT NULL,
	"TreeLayoutId" uuid NULL,
	"NormalizedTitle" text NULL,
	CONSTRAINT "PK_Pages" PRIMARY KEY ("Id")
);
CREATE INDEX "IX_Pages_IsDeleted" ON public."Pages" USING btree ("IsDeleted");
CREATE UNIQUE INDEX "IX_Pages_Key" ON public."Pages" USING btree ("Key");
CREATE INDEX "IX_Pages_MainPhotoId" ON public."Pages" USING btree ("MainPhotoId");
CREATE INDEX "IX_Pages_TreeLayoutId" ON public."Pages" USING btree ("TreeLayoutId");


-- public."Relations" определение

-- Drop table

-- DROP TABLE public."Relations";

CREATE TABLE public."Relations" (
	"Id" uuid NOT NULL,
	"SourceId" uuid NOT NULL,
	"DestinationId" uuid NOT NULL,
	"EventId" uuid NULL,
	"Type" int4 NOT NULL,
	"IsComplementary" bool NOT NULL,
	"IsDeleted" bool NOT NULL,
	"Duration" varchar(30) NULL,
	CONSTRAINT "PK_Relations" PRIMARY KEY ("Id")
);
CREATE INDEX "IX_Relations_DestinationId" ON public."Relations" USING btree ("DestinationId");
CREATE INDEX "IX_Relations_EventId" ON public."Relations" USING btree ("EventId");
CREATE INDEX "IX_Relations_IsComplementary" ON public."Relations" USING btree ("IsComplementary");
CREATE INDEX "IX_Relations_IsDeleted" ON public."Relations" USING btree ("IsDeleted");
CREATE INDEX "IX_Relations_SourceId" ON public."Relations" USING btree ("SourceId");


-- public."TreeLayouts" определение

-- Drop table

-- DROP TABLE public."TreeLayouts";

CREATE TABLE public."TreeLayouts" (
	"Id" uuid NOT NULL,
	"GenerationDate" timestamptz NOT NULL,
	"LayoutJson" text NULL,
	"Kind" int4 DEFAULT 0 NOT NULL,
	"PageId" uuid NULL,
	CONSTRAINT "PK_TreeLayouts" PRIMARY KEY ("Id")
);
CREATE INDEX "IX_TreeLayouts_PageId" ON public."TreeLayouts" USING btree ("PageId");


-- public."AspNetUserClaims" внешние включи

ALTER TABLE public."AspNetUserClaims" ADD CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES public."AspNetUsers"("Id") ON DELETE CASCADE;


-- public."AspNetUserLogins" внешние включи

ALTER TABLE public."AspNetUserLogins" ADD CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES public."AspNetUsers"("Id") ON DELETE CASCADE;


-- public."AspNetUserRoles" внешние включи

ALTER TABLE public."AspNetUserRoles" ADD CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES public."AspNetRoles"("Id") ON DELETE CASCADE;
ALTER TABLE public."AspNetUserRoles" ADD CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES public."AspNetUsers"("Id") ON DELETE CASCADE;


-- public."AspNetUserTokens" внешние включи

ALTER TABLE public."AspNetUserTokens" ADD CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES public."AspNetUsers"("Id") ON DELETE CASCADE;


-- public."AspNetUsers" внешние включи

ALTER TABLE public."AspNetUsers" ADD CONSTRAINT "FK_AspNetUsers_Pages_PageId" FOREIGN KEY ("PageId") REFERENCES public."Pages"("Id") ON DELETE RESTRICT;


-- public."Changes" внешние включи

ALTER TABLE public."Changes" ADD CONSTRAINT "FK_Changes_AspNetUsers_AppUserId" FOREIGN KEY ("AppUserId") REFERENCES public."AspNetUsers"("Id") ON DELETE RESTRICT;
ALTER TABLE public."Changes" ADD CONSTRAINT "FK_Changes_AspNetUsers_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES public."AspNetUsers"("Id") ON DELETE CASCADE;
ALTER TABLE public."Changes" ADD CONSTRAINT "FK_Changes_Media_EditedMediaId" FOREIGN KEY ("EditedMediaId") REFERENCES public."Media"("Id") ON DELETE RESTRICT;
ALTER TABLE public."Changes" ADD CONSTRAINT "FK_Changes_Pages_EditedPageId" FOREIGN KEY ("EditedPageId") REFERENCES public."Pages"("Id") ON DELETE RESTRICT;
ALTER TABLE public."Changes" ADD CONSTRAINT "FK_Changes_Relations_EditedRelationId" FOREIGN KEY ("EditedRelationId") REFERENCES public."Relations"("Id") ON DELETE RESTRICT;


-- public."LivingBeingOverviews" внешние включи

ALTER TABLE public."LivingBeingOverviews" ADD CONSTRAINT "FK_LivingBeingOverview_Pages_PageId" FOREIGN KEY ("PageId") REFERENCES public."Pages"("Id");


-- public."Media" внешние включи

ALTER TABLE public."Media" ADD CONSTRAINT "FK_Media_AspNetUsers_UploaderId" FOREIGN KEY ("UploaderId") REFERENCES public."AspNetUsers"("Id") ON DELETE RESTRICT;


-- public."MediaTags" внешние включи

ALTER TABLE public."MediaTags" ADD CONSTRAINT "FK_MediaTags_Media_MediaId" FOREIGN KEY ("MediaId") REFERENCES public."Media"("Id") ON DELETE CASCADE;
ALTER TABLE public."MediaTags" ADD CONSTRAINT "FK_MediaTags_Pages_ObjectId" FOREIGN KEY ("ObjectId") REFERENCES public."Pages"("Id") ON DELETE RESTRICT;


-- public."PageAliases" внешние включи

ALTER TABLE public."PageAliases" ADD CONSTRAINT "FK_PageAliases_Pages_PageId" FOREIGN KEY ("PageId") REFERENCES public."Pages"("Id") ON DELETE CASCADE;


-- public."PageDrafts" внешние включи

ALTER TABLE public."PageDrafts" ADD CONSTRAINT "FK_PageDrafts_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES public."AspNetUsers"("Id") ON DELETE CASCADE;


-- public."PageReferences" внешние включи

ALTER TABLE public."PageReferences" ADD CONSTRAINT "FK_PageReferences_Pages_DestinationId" FOREIGN KEY ("DestinationId") REFERENCES public."Pages"("Id") ON DELETE CASCADE;
ALTER TABLE public."PageReferences" ADD CONSTRAINT "FK_PageReferences_Pages_SourceId" FOREIGN KEY ("SourceId") REFERENCES public."Pages"("Id") ON DELETE CASCADE;


-- public."Pages" внешние включи

ALTER TABLE public."Pages" ADD CONSTRAINT "FK_Pages_Media_MainPhotoId" FOREIGN KEY ("MainPhotoId") REFERENCES public."Media"("Id") ON DELETE RESTRICT;
ALTER TABLE public."Pages" ADD CONSTRAINT "FK_Pages_TreeLayouts_TreeLayoutId" FOREIGN KEY ("TreeLayoutId") REFERENCES public."TreeLayouts"("Id") ON DELETE RESTRICT;


-- public."Relations" внешние включи

ALTER TABLE public."Relations" ADD CONSTRAINT "FK_Relations_Pages_DestinationId" FOREIGN KEY ("DestinationId") REFERENCES public."Pages"("Id") ON DELETE CASCADE;
ALTER TABLE public."Relations" ADD CONSTRAINT "FK_Relations_Pages_EventId" FOREIGN KEY ("EventId") REFERENCES public."Pages"("Id") ON DELETE RESTRICT;
ALTER TABLE public."Relations" ADD CONSTRAINT "FK_Relations_Pages_SourceId" FOREIGN KEY ("SourceId") REFERENCES public."Pages"("Id") ON DELETE CASCADE;


-- public."TreeLayouts" внешние включи

ALTER TABLE public."TreeLayouts" ADD CONSTRAINT "FK_TreeLayouts_Pages_PageId" FOREIGN KEY ("PageId") REFERENCES public."Pages"("Id");


-- public."ChangesGrouped" исходный текст

CREATE OR REPLACE VIEW public."ChangesGrouped"
AS SELECT cc."GroupKey",
    string_agg(cc."Id"::character varying::text, ','::text ORDER BY cc."Date" DESC) AS "Ids",
    max(cc."Date") AS "Date"
   FROM ( SELECT cg."Id",
            cg."GroupId",
            cg."Date",
            cg."AuthorId",
            cg."EntityType",
            cg."RevertedChangesetId",
            cg."EditedPageId",
            cg."EditedMediaId",
            cg."EditedRelationId",
            cg."UpdatedState",
            cg."AppUserId",
            cg."ChangeType",
            concat(cg."AuthorId", '__', cg."EntityType", '__',
                CASE cg."ChangeType"
                    WHEN 3 THEN 'Restored'::text
                    WHEN 0 THEN 'Created'::text
                    WHEN 2 THEN 'Removed'::text
                    ELSE 'Updated'::text
                END,
                CASE
                    WHEN cg."EditedMediaId" IS NOT NULL THEN NULL::text
                    ELSE concat('__', COALESCE(cg."EditedPageId", cg."EditedRelationId") || ''::text)
                END, '__', (date_part('epoch'::text, cg."Date") / 3600::double precision)::integer) AS "GroupKey"
           FROM "Changes" cg) cc
  GROUP BY cc."GroupKey"
  ORDER BY (max(cc."Date")) DESC;


-- public."PagesScored" исходный текст

CREATE OR REPLACE VIEW public."PagesScored"
AS SELECT pp."Id",
    pp."Title",
    pp."NormalizedTitle",
    pp."Key",
    pp."Type",
    pp."MainPhotoId",
    pp."CreationDate",
    pp."LastUpdateDate",
    pp."IsDeleted",
    pp."HasText",
    pp."HasPhoto",
    pp."HasRelations",
    pp."HasHumanName",
    pp."HasBirthday",
    pp."HasBirthPlace",
    pp."HasGender",
    pp."HasAnimalName",
    pp."HasAnimalSpecies",
    pp."HasEventDate",
    pp."HasLocationAddress",
        CASE
            WHEN pp."Type" = 0 THEN
            CASE
                WHEN pp."HasText" THEN 30
                ELSE 0
            END +
            CASE
                WHEN pp."HasPhoto" THEN 15
                ELSE 0
            END +
            CASE
                WHEN pp."HasRelations" THEN 15
                ELSE 0
            END +
            CASE
                WHEN pp."HasHumanName" THEN 10
                ELSE 0
            END +
            CASE
                WHEN pp."HasBirthday" THEN 10
                ELSE 0
            END +
            CASE
                WHEN pp."HasBirthPlace" THEN 10
                ELSE 0
            END +
            CASE
                WHEN pp."HasGender" THEN 10
                ELSE 0
            END
            WHEN pp."Type" = 1 THEN
            CASE
                WHEN pp."HasText" THEN 30
                ELSE 0
            END +
            CASE
                WHEN pp."HasPhoto" THEN 20
                ELSE 0
            END +
            CASE
                WHEN pp."HasRelations" THEN 20
                ELSE 0
            END +
            CASE
                WHEN pp."HasBirthday" THEN 10
                ELSE 0
            END +
            CASE
                WHEN pp."HasAnimalName" THEN 10
                ELSE 0
            END +
            CASE
                WHEN pp."HasAnimalSpecies" THEN 10
                ELSE 0
            END
            WHEN pp."Type" = 2 THEN
            CASE
                WHEN pp."HasText" THEN 80
                ELSE 0
            END +
            CASE
                WHEN pp."HasEventDate" THEN 20
                ELSE 0
            END
            WHEN pp."Type" = 3 THEN
            CASE
                WHEN pp."HasText" THEN 70
                ELSE 0
            END +
            CASE
                WHEN pp."HasPhoto" THEN 20
                ELSE 0
            END +
            CASE
                WHEN pp."HasLocationAddress" THEN 10
                ELSE 0
            END
            ELSE
            CASE
                WHEN pp."HasText" THEN 100
                ELSE 0
            END
        END AS "CompletenessScore"
   FROM ( SELECT p."Id",
            p."Title",
            p."Key",
            p."Type",
            p."Description",
            p."Facts",
            p."MainPhotoId",
            p."CreationDate",
            p."LastUpdateDate",
            p."IsDeleted",
            p."TreeLayoutId",
            p."NormalizedTitle",
            character_length(p."Description") > 0 AS "HasText",
            p."MainPhotoId" IS NOT NULL AS "HasPhoto",
            (( SELECT count(*) AS count
                   FROM "Relations" r
                  WHERE r."SourceId" = p."Id" OR r."DestinationId" = p."Id")) > 0 AS "HasRelations",
            json_extract_path_text(p."Facts"::json, VARIADIC ARRAY['Main.Name'::text, 'Values'::text, '0'::text, 'LastName'::text]) IS NOT NULL AS "HasHumanName",
            json_extract_path_text(p."Facts"::json, VARIADIC ARRAY['Bio.Gender'::text, 'IsMale'::text]) IS NOT NULL AS "HasGender",
            json_extract_path_text(p."Facts"::json, VARIADIC ARRAY['Birth.Date'::text, 'Value'::text]) IS NOT NULL AS "HasBirthday",
            json_extract_path_text(p."Facts"::json, VARIADIC ARRAY['Birth.Place'::text, 'Value'::text]) IS NOT NULL AS "HasBirthPlace",
            json_extract_path_text(p."Facts"::json, VARIADIC ARRAY['Main.Name'::text, 'Value'::text]) IS NOT NULL AS "HasAnimalName",
            json_extract_path_text(p."Facts"::json, VARIADIC ARRAY['Bio.Species'::text, 'Value'::text]) IS NOT NULL AS "HasAnimalSpecies",
            json_extract_path_text(p."Facts"::json, VARIADIC ARRAY['Main.Location'::text, 'Value'::text]) IS NOT NULL AS "HasLocationAddress",
            json_extract_path_text(p."Facts"::json, VARIADIC ARRAY['Main.Date'::text, 'Value'::text]) IS NOT NULL AS "HasEventDate"
           FROM "Pages" p) pp;

2. Экспортировал данные из таблиц SQLite в только что созданные таблицы PG после чего запустил Bonsai и все заработало