Репликация PostgreSQL+Bucardo. Первые рабочие изменение структуры БД.

Вот первые рабочие изменение структуры БД.
То, что дали программисты:


-- ################################################################

-- ПРОВЕРИТЬ!!!!! должно уже быть
DROP INDEX "u_cntrgn0";
CREATE UNIQUE INDEX "u_cntrinvnmbr" ON countergen
USING btree ("invnumber");
////////////////////////////////////////////////////////


update countergen set ownershiptypecod = 1
where invnumber is not null;

-- нормы льготы для тарифов ЭлектроОбогрев
update tariff set extralimit = 25, maxlimit = 200,
usagelimit = 100, diflimit = 200 where code in (5,6,9);

-- 73 кат СIЛЬСЬКИЙ ПРАЦIВНИК КРИМIНАЛЬНО-ВИКОНАВЧОI СИСТЕМИ НА ПЕНСII
-- 50% без лимита
update benefitcategory set coeff = 50, islimitcode = 0 where code = 73;


CREATE TABLE "judicialdebt" (
"code" DOUBLE PRECISION NOT NULL,
"name" VARCHAR(50),
"dategen" DATE,
"contractnumber" VARCHAR(50),
"contractdate" DATE,
"courtnumber" VARCHAR(50),
"courtdate" DATE,
"isincourt" DOUBLE PRECISION NOT NULL,
"operationdate" DATE,
"courtsum" NUMERIC(16,2),
"courtverdict" VARCHAR(100),
"profitsdate" DATE,
"typegen" DOUBLE PRECISION NOT NULL,
"recordpontrfcode" DOUBLE PRECISION NOT NULL,
"periodrefcode" DOUBLE PRECISION,
"modify_time" DOUBLE PRECISION,
"domain_info" VARCHAR(100) NOT NULL,
CONSTRAINT "pk_juddebtcd" PRIMARY KEY("code"),
CONSTRAINT "fk_juddebtrpcd" FOREIGN KEY ("recordpontrfcode")
REFERENCES "recordpoint"("code")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;

ALTER TABLE judicialdebt
ADD CONSTRAINT "fk_jdbtprdcd" FOREIGN KEY ("periodrefcode")
REFERENCES "currentperiod"("code")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

CREATE INDEX "ind_jdbtprdcd" ON "judicialdebt"
USING btree ("periodrefcode");

CREATE TABLE "juddebtstatus" (
"code" DOUBLE PRECISION NOT NULL,
"name" VARCHAR(50),
CONSTRAINT "pk_juddebtstts" PRIMARY KEY("code")
) WITHOUT OIDS;

INSERT INTO "juddebtstatus" ("code", "name")
VALUES (0, 'Отсутствует');

INSERT INTO "juddebtstatus" ("code", "name")
VALUES (1, 'Не погашено');

INSERT INTO "juddebtstatus" ("code", "name")
VALUES (2, 'Погашено');

CREATE TABLE "judicialdebtentry" (
"code" DOUBLE PRECISION NOT NULL,
"judicialdebtcode" DOUBLE PRECISION NOT NULL,
"dataenddate" DATE NOT NULL,
"datasumtopay" NUMERIC(16,2) NOT NULL,
"datapaiddate" DATE,
"datapaidsum" NUMERIC(16,2) NOT NULL,
"datastatuscode" DOUBLE PRECISION NOT NULL,
"modify_time" DOUBLE PRECISION,
"domain_info" VARCHAR(100) NOT NULL,
CONSTRAINT "pk_juddebtntrcd" PRIMARY KEY("code"),
CONSTRAINT "fk_judnttrdebtcd" FOREIGN KEY ("judicialdebtcode")
REFERENCES "judicialdebt"("code")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX "juddebtentrydebtcd" ON "judicialdebtentry"
USING btree ("judicialdebtcode");
CREATE INDEX "judicialdebtrpcode" ON "klres"."judicialdebt"
USING btree ("recordpontrfcode");

INSERT INTO "invstatus" ("code", "name")
VALUES (4, 'Исковый');

INSERT INTO "saldotype" ("code", "name")
VALUES (5, 'По исковой (электричество)');

INSERT INTO "saldotype" ("code", "name")
VALUES (6, 'По исковой (акты)');

CREATE TABLE "sordebttobill" (
"code" DOUBLE PRECISION NOT NULL,
"sumgen" NUMERIC(16,2),
"entrycode" DOUBLE PRECISION NOT NULL,
"billrefcode" DOUBLE PRECISION NOT NULL,
"modify_time" DOUBLE PRECISION,
"domain_info" VARCHAR(100) NOT NULL,
CONSTRAINT "pk_srdbttbll" PRIMARY KEY("code")
) WITHOUT OIDS;

CREATE TABLE "paymentjd" (
"code" DOUBLE PRECISION NOT NULL,
"paydate" DATE NOT NULL,
"value" NUMERIC(16,2) NOT NULL,
"pymntdcmntrfcode" DOUBLE PRECISION NOT NULL,
"statuscode" DOUBLE PRECISION NOT NULL,
"modify_time" DOUBLE PRECISION,
"domain_info" VARCHAR(100) NOT NULL,
"jdtype" DOUBLE PRECISION,
"entrycode" DOUBLE PRECISION,
CONSTRAINT "pk_pmntjd" PRIMARY KEY("code"),
CONSTRAINT "fk_pmntjdsttscd" FOREIGN KEY ("statuscode")
REFERENCES "paymentstatus"("code")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX "paymentjd_idx" ON "paymentjd"
USING btree ("pymntdcmntrfcode");

INSERT INTO "paymenttype" ("code", "name")
VALUES (16, 'По ИЗ');

INSERT INTO "paymenttype" ("code", "name")
VALUES (17, 'По ИЗ (акты)');

INSERT INTO "paymentdocmnttyp" ("code", "name")
VALUES (8, 'Корректировка иска судом');

-- #################################################################

Вот что делаем мы:

-- #################################################################

0. Останавливаем репликацию

-- #################################################################

1. Под res на на главной БД:

CREATE TABLE "judicialdebt" (
"code" DOUBLE PRECISION NOT NULL,
"name" VARCHAR(50),
"dategen" DATE,
"contractnumber" VARCHAR(50),
"contractdate" DATE,
"courtnumber" VARCHAR(50),
"courtdate" DATE,
"isincourt" DOUBLE PRECISION NOT NULL,
"operationdate" DATE,
"courtsum" NUMERIC(16,2),
"courtverdict" VARCHAR(100),
"profitsdate" DATE,
"typegen" DOUBLE PRECISION NOT NULL,
"recordpontrfcode" DOUBLE PRECISION NOT NULL,
"periodrefcode" DOUBLE PRECISION,
"modify_time" DOUBLE PRECISION,
"domain_info" VARCHAR(100) NOT NULL,
CONSTRAINT "pk_juddebtcd" PRIMARY KEY("code"),
CONSTRAINT "fk_juddebtrpcd" FOREIGN KEY ("recordpontrfcode")
REFERENCES "recordpoint"("code")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;

ALTER TABLE judicialdebt
ADD CONSTRAINT "fk_jdbtprdcd" FOREIGN KEY ("periodrefcode")
REFERENCES "currentperiod"("code")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

CREATE INDEX "ind_jdbtprdcd" ON "judicialdebt"
USING btree ("periodrefcode");

CREATE TABLE "juddebtstatus" (
"code" DOUBLE PRECISION NOT NULL,
"name" VARCHAR(50),
CONSTRAINT "pk_juddebtstts" PRIMARY KEY("code")
) WITHOUT OIDS;

CREATE TABLE "judicialdebtentry" (
"code" DOUBLE PRECISION NOT NULL,
"judicialdebtcode" DOUBLE PRECISION NOT NULL,
"dataenddate" DATE NOT NULL,
"datasumtopay" NUMERIC(16,2) NOT NULL,
"datapaiddate" DATE,
"datapaidsum" NUMERIC(16,2) NOT NULL,
"datastatuscode" DOUBLE PRECISION NOT NULL,
"modify_time" DOUBLE PRECISION,
"domain_info" VARCHAR(100) NOT NULL,
CONSTRAINT "pk_juddebtntrcd" PRIMARY KEY("code"),
CONSTRAINT "fk_judnttrdebtcd" FOREIGN KEY ("judicialdebtcode")
REFERENCES "judicialdebt"("code")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX "juddebtentrydebtcd" ON "judicialdebtentry"
USING btree ("judicialdebtcode");
CREATE INDEX "judicialdebtrpcode" ON "judicialdebt"
USING btree ("recordpontrfcode");

CREATE TABLE "sordebttobill" (
"code" DOUBLE PRECISION NOT NULL,
"sumgen" NUMERIC(16,2),
"entrycode" DOUBLE PRECISION NOT NULL,
"billrefcode" DOUBLE PRECISION NOT NULL,
"modify_time" DOUBLE PRECISION,
"domain_info" VARCHAR(100) NOT NULL,
CONSTRAINT "pk_srdbttbll" PRIMARY KEY("code")
) WITHOUT OIDS;

CREATE TABLE "paymentjd" (
"code" DOUBLE PRECISION NOT NULL,
"paydate" DATE NOT NULL,
"value" NUMERIC(16,2) NOT NULL,
"pymntdcmntrfcode" DOUBLE PRECISION NOT NULL,
"statuscode" DOUBLE PRECISION NOT NULL,
"modify_time" DOUBLE PRECISION,
"domain_info" VARCHAR(100) NOT NULL,
"jdtype" DOUBLE PRECISION,
"entrycode" DOUBLE PRECISION,
CONSTRAINT "pk_pmntjd" PRIMARY KEY("code"),
CONSTRAINT "fk_pmntjdsttscd" FOREIGN KEY ("statuscode")
REFERENCES "paymentstatus"("code")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX "paymentjd_idx" ON "paymentjd"
USING btree ("pymntdcmntrfcode");

-- #################################################################

2. Под bucardo на главной БД:

INSERT INTO goat (db,schemaname,tablename,pkey,pkeytype,analyze_after_copy)
VALUES ('skres','res','judicialdebt','code','bigint','false');
INSERT INTO goat (db,schemaname,tablename,pkey,pkeytype,analyze_after_copy)
VALUES ('skres','res','juddebtstatus','code','bigint','false');
INSERT INTO goat (db,schemaname,tablename,pkey,pkeytype,analyze_after_copy)
VALUES ('skres','res','judicialdebtentry','code','bigint','false');
INSERT INTO goat (db,schemaname,tablename,pkey,pkeytype,analyze_after_copy)
VALUES ('skres','res','sordebttobill','code','bigint','false');
INSERT INTO goat (db,schemaname,tablename,pkey,pkeytype,analyze_after_copy)
VALUES ('skres','res','paymentjd','code','bigint','false');

INSERT INTO herdmap (herd, goat)
SELECT 'herd_skres', id
FROM goat WHERE db = 'skres'
AND tablename IN ('judicialdebt','juddebtstatus',
'judicialdebtentry','sordebttobill','paymentjd');

UPDATE sync SET synctype = 'fullcopy' WHERE name = 'sync_skres';
UPDATE sync SET synctype = 'pushdelta' WHERE name = 'sync_skres';

-- #################################################################

3. На подчиненной БД в РЭС под res:

update judicialdebt set code = code;
update juddebtstatus set code = code;
update judicialdebtentry set code = code;
update sordebttobill set code = code;
update paymentjd set code = code;

-- #################################################################

4. Запускаем репликацию

-- #################################################################