Migration using dblink

migration-postgresql.sql

create table feed_rss_website
(
  id       uuid                    not null
    constraint "PK_d401ce5af5a14a6c20e76f1ae78"
    primary key,
  created  timestamp default now() not null,
  updated  timestamp default now() not null,
  version  integer                 not null,
  url      varchar                 not null,
  name     varchar                 not null
    constraint "UQ_5a5f2e3a71f0ca91cde986cb6c2"
    unique,
  interval varchar                 not null,
  metadata jsonb                   not null
);

INSERT INTO feed_rss_website (id, created, updated, version, url, name, interval, metadata)
SELECT id,
       created,
       updated,
       version,
       url,
       name,
       interval,
       metadata
FROM dblink('host=host user=postgres password=postgres dbname=postgres',
            'SELECT id,
                    created,
                    updated,
                    version,
                    url,
                    name,
                    interval,
                    metadata
             FROM feed_rss_website')
         AS x (id uuid, created timestamp, updated timestamp, version integer, url varchar, name varchar, interval varchar, metadata jsonb);


alter table feed_rss_website
  owner to postgres;

create table feed_rss_website_entry
(
  id          uuid                    not null
    constraint "PK_c10d62d8d0895e4f21096a1bafa"
    primary key,
  created     timestamp default now() not null,
  updated     timestamp default now() not null,
  version     integer                 not null,
  "entryId"   varchar                 not null,
  data        jsonb                   not null,
  "websiteId" uuid
    constraint "FK_690095a475ccf157a466657fc34"
    references feed_rss_website
);


grant ALL PRIVILEGES ON TABLE feed_rss_website TO botrss_prod;
grant ALL PRIVILEGES ON TABLE feed_rss_website_entry TO botrss_prod;


INSERT INTO feed_rss_website_entry (id, created, updated, version, "entryId", data, "websiteId")
SELECT id, created, updated, version, "entryId", data, "websiteId"
FROM dblink('host=host user=postgres password=postgres dbname=postgres',
            'SELECT id, created, updated, version, "entryId" entryId, data, "websiteId" websiteId
             FROM feed_rss_website_entry')
         AS x (id uuid, created timestamp, updated timestamp, version integer, "entryId" varchar, data jsonb, "websiteId" uuid);


alter table feed_rss_website_entry
  owner to postgres;

create unique index uq_entryid_website
  on feed_rss_website_entry ("entryId", "websiteId");
CREATE EXTENSION dblink;