“Pro, ṣugbọn kii ṣe iṣupọ” tabi bii a ṣe rọpo DBMS ti a ko wọle

(t) Awọn aworan Yandex

Gbogbo awọn ohun kikọ jẹ airotẹlẹ, awọn ami-iṣowo jẹ ti awọn oniwun wọn, eyikeyi ibajọra jẹ laileto ati ni gbogbogbo, eyi ni “idajọ iye koko-ọrọ, jọwọ maṣe fọ ilẹkun…”.

A ni iriri akude ni gbigbe awọn ọna ṣiṣe alaye pẹlu ọgbọn sinu aaye data lati DBMS kan si ekeji. Ni ipo ti aṣẹ ijọba No. 1236 ti Oṣu kọkanla 16.11.2016, XNUMX, eyi jẹ igbagbogbo gbigbe lati Oracle si Postgresql. A le sọ fun ọ lọtọ bi o ṣe le ṣeto ilana naa daradara ati laisi irora bi o ti ṣee; loni a yoo sọrọ nipa awọn ẹya ara ẹrọ ti lilo iṣupọ kan ati awọn iṣoro wo ni o le ba pade nigbati o ba n kọ awọn eto pinpin kaakiri pupọ pẹlu oye idiju ni awọn ilana ati awọn iṣẹ.

Spoiler – bẹẹni, fila, RAC ati pg multimaster jẹ awọn solusan ti o yatọ pupọ.

Jẹ ki a sọ pe o ti gbe gbogbo imọ-ọrọ tẹlẹ lati plsql si pgsql. Ati pe awọn idanwo ipadasẹhin rẹ dara, ni bayi dajudaju o n ronu nipa iwọn, nitori… awọn idanwo fifuye ko jẹ ki inu rẹ dun pupọ, paapaa lori ohun elo ti o wa ninu iṣẹ akanṣe akọkọ, fun DBMS ti o yatọ pupọ. Jẹ ki a sọ pe o rii ojutu kan lati ọdọ olutaja ile “Postgres Professional” pẹlu aṣayan ti a pe ni “multimaster”, eyiti o wa nikan ni ẹya “o pọju” ti “Postgres Pro Enterprise” ati ni ibamu si apejuwe naa - o jọra pupọ si kini kini o nilo, ati pẹlu iwadi akọkọ akọkọ yoo wa ero wa sinu ori mi: “Oh! Iyẹn dipo RAC! Ati paapaa pẹlu opo gigun ti imọ-ẹrọ ni ilẹ-ile wa! ”

Ṣugbọn maṣe yara lati yọ, ati siwaju sii a yoo ṣe apejuwe idi ti o nilo lati mọ awọn nuances wọnyi, nitori ... wọn nira lati ṣe asọtẹlẹ, paapaa lẹhin kika iwe-ipamọ ọja daradara. Ṣe ayẹwo boya o ti ṣetan lati ṣe imudojuiwọn awọn ẹya DBMS nigbagbogbo taara lori aaye iṣelọpọ, nitori Diẹ ninu awọn abawọn ko ni ibaramu pẹlu lilo ile-iṣẹ ati pe o nira lati rii lakoko idanwo.
Bẹrẹ nipa kika farabalẹ ni apakan “multimaster” - “awọn idiwọn” lori oju opo wẹẹbu olupese.

Ohun akọkọ ti o le ba pade ni awọn iyasọtọ ti bii awọn iṣowo ṣe n ṣiṣẹ, ninu eyiti a pe. Ipo "meji-meji", ati nigba miiran ko si ọna lati ṣatunṣe eyi ayafi nipa atunkọ gbogbo ọgbọn ti ilana rẹ. Eyi ni apẹẹrẹ ti o rọrun:

create table test1 (id integer, id1 integer);
insert into test1 values (1, 1),(1, 2);
 
ALTER TABLE test1 ADD CONSTRAINT test1_uk UNIQUE (id,id1) DEFERRABLE INITIALLY DEFERRED;
 
update test1
           set id1 =
               case id1
                 when 1
                 then 2
                 else id1 - sign(2 - 1)
               end
         where id1 between 1 and 2;

Aṣiṣe kan waye:

ОШИБКА:  [MTM] Transaction MTM-1-2435-10-605783555137701 (10654) is aborted on node 3. Check its log to see error details.

Lẹhinna o le ja fun igba pipẹ pẹlu titiipa ti o ku ni awọn ẹya 10.5, 10.6, ati pe ojutu ti a mọ nikan ti o pa gbogbo idi ti iṣupọ ni lati yọ awọn tabili “iṣoro” kuro ninu iṣupọ, ie. ṣe make_table_local, ṣugbọn eyi yoo gba laaye o kere lati ṣiṣẹ, ati pe kii yoo fi ohun gbogbo si idaduro nitori idaduro duro fun awọn adehun idunadura. O dara, tabi fi imudojuiwọn sori ẹrọ si ẹya 11.2, eyiti o yẹ ki o ṣe iranlọwọ, ṣugbọn boya kii ṣe, maṣe gbagbe lati ṣayẹwo.

Ni diẹ ninu awọn ẹya o le gba titiipa aramada paapaa diẹ sii:

username= mtm и backend_type = background worker

Ati ni ipo yii, mimu imudojuiwọn ẹya DBMS nikan si 11.2 ati ga julọ yoo ran ọ lọwọ, tabi boya kii yoo ṣe iranlọwọ.

Diẹ ninu awọn iṣẹ ṣiṣe pẹlu awọn atọka le ja si awọn aṣiṣe, eyiti o fihan ni kedere pe iṣoro naa wa ni Atunse Bi-itọnisọna; iwọ yoo rii BDR taara ninu awọn akọọlẹ MTM. Ṣe o jẹ 2ndQuadrant looto? Rara... a ra multimaster, o kan lasan ni, orukọ imọ-ẹrọ ni.

[MTM] bdr doesn't support index rechecks
[MTM] 12124: REMOTE begin abort transaction 4083
[MTM] 12124: send ABORT notification for transaction  (5467) local xid=4083 to coordinator 3
[MTM] Receive ABORT_PREPARED logical message for transaction MTM-3-25030-83-605694076627780 from node 3
[MTM] Abort prepared transaction MTM-3-25030-83-605694076627780 status InProgress from node 3 originId=3
[MTM] MtmLogAbortLogicalMessage node=3 transaction=MTM-3-25030-83-605694076627780 lsn=9fff448 

Ti o ba n lo awọn tabili igba diẹ, laibikita awọn iṣeduro: “Afikun multimaster n ṣe atunṣe data ni ọna aifọwọyi patapata. O le ṣe awọn iṣowo kikọ nigbakanna ati ṣiṣẹ pẹlu awọn tabili igba diẹ lori ipade eyikeyi ninu iṣupọ. ”

Lẹhinna ni otitọ iwọ yoo gba pe atunkọ ko ṣiṣẹ kọja gbogbo awọn tabili ti a lo ninu ilana naa, ti koodu naa ba ni awọn ẹda tabili igba diẹ, ati paapaa lilo multimaster.remote_functions kii yoo ṣe iranlọwọ, iwọ yoo ni lati ṣe imudojuiwọn tabi tun kọ ọgbọn rẹ sinu. ilana. Ti o ba nilo lati lo multimaster amugbooro meji ati pg_pathman nigbakanna laarin Postgres Pro Enterprise v 10.5, lẹhinna ṣayẹwo iyẹn pẹlu apẹẹrẹ ti o rọrun yii:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2019m06 PARTITION OF measurement FOR VALUES FROM ('2019-06-01') TO ('2019-07-01');
insert into measurement values (1, to_date('27.06.2019', 'dd.mm.yyyy'), 1, 1);
insert into measurement values (2, to_date('28.06.2019', 'dd.mm.yyyy'), 1, 1);
insert into measurement values (3, to_date('29.06.2019', 'dd.mm.yyyy'), 1, 1);
insert into measurement values (4, to_date('30.06.2019', 'dd.mm.yyyy'), 1, 1);

Awọn aṣiṣe wọnyi bẹrẹ lati han ninu awọn akọọlẹ lori awọn apa DBMS:

…
 PATHMAN_CONFIG doesn't contain relation 23245
> find_in_dynamic_libpath: trying "/opt/…/ent-10/lib/pg_pathman"
> find_in_dynamic_libpath: trying "/opt//…/ent-10/lib/pg_pathman.so"
> ОТЛАДКА:  find_in_dynamic_libpath: trying "/opt/…/ent-10/lib/pg_pathman"
> find_in_dynamic_libpath: trying "/opt/…/ent-10/lib/pg_pathman.so"
> PrepareTransaction(1) name: unnamed; blockState: PREPARE; state: INPROGR, xid/subid/cid: 6919/1/40
> StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0
> switched to timeline 1 valid until 0/0
…
Transaction MTM-1-13604-7-612438856339841 (6919) is aborted on node 2. Check its log to see error details.
...
[MTM] 28295: REMOTE begin abort transaction 7017
…
[MTM] 28295: send ABORT notification for transaction  (6919) local xid=7017 to coordinator 1

O le wa kini awọn aṣiṣe wọnyi wa ni atilẹyin imọ-ẹrọ, kii ṣe asan pe o ra.

Kin ki nse? Ọtun! Igbesoke si “Postgres Pro Enterprise” v 11.2

Lọtọ, o nilo lati mọ ọkọọkan yẹn, ti o jẹ nkan ti ibi ipamọ data ti o tun ṣe, ko ni iye opin-si-opin jakejado iṣupọ, ọkọọkan jẹ agbegbe fun ipade kọọkan ati ti o ba ni awọn aaye pẹlu awọn ihamọ alailẹgbẹ ati lo ọkọọkan, lẹhinna o le ṣe afikun deede si nọmba ipade ni iṣupọ, nitori Bii ọpọlọpọ awọn apa inu iṣupọ bi o ti ṣee ṣe, ọkọọkan ati int yoo dagba ni iyara ju ti o nireti lọ. Lati ṣe simplify ṣiṣẹ pẹlu ọkọọkan ninu ọja iwọ yoo paapaa rii iṣẹ alter_sequences, eyiti yoo ṣe awọn afikun pataki fun ọkọọkan lori gbogbo awọn apa, ṣugbọn mura silẹ pe iṣẹ naa kii yoo ṣiṣẹ ni gbogbo awọn ẹya. Nitoribẹẹ, o le kọ funrararẹ, ni lilo koodu lati github bi ipilẹ tabi ṣe atunṣe funrararẹ taara ni DBMS. Ni idi eyi, awọn aaye pẹlu iru serialbigserial yoo ṣiṣẹ diẹ sii bi o ti tọ, ṣugbọn lati lo wọn, o ṣeese o yoo nilo lati tun kọ koodu ti awọn ilana ati awọn iṣẹ rẹ. Boya ẹnikan yoo rii iṣẹ monotonic_sequences wulo.

Ṣaaju si ẹya 11.2 ti Postgres Pro Enterprise, atunkọ yoo ṣiṣẹ nikan ti awọn bọtini akọkọ ba wa, ṣe akiyesi eyi nigbati o ndagbasoke.

Lọtọ, Emi yoo fẹ lati mẹnuba awọn abuda ti bii npgsql ṣe n ṣiṣẹ ni ojutu iṣupọ kan; awọn iṣoro wọnyi ko dide lori ipade kan, ṣugbọn wọn wa ni pupọ ninu multimaster kan.
Ni diẹ ninu awọn ẹya o le ba pade aṣiṣe:

Exception Details: Npgsql.PostgresException: 25001: команда SET TRANSACTION ISOLATION LEVEL 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Kini o le ṣee ṣe? O kan ni lati ma lo diẹ ninu awọn ẹya. O nilo lati mọ wọn, nitori ... Aṣiṣe naa han ni diẹ ẹ sii ju ẹyọkan lọ, ati paapaa lẹhin atunṣe akọkọ rẹ, o le ba pade nigbamii. O tun nilo lati mura silẹ fun eyi ati pe o dara julọ lati bo gbogbo awọn abawọn DBMS ti a mọ ti o jẹ atunṣe nipasẹ olupese pẹlu awọn idanwo ifasilẹyin lọtọ. Nitorinaa lati sọrọ, gbẹkẹle, ṣugbọn rii daju.

Ti ohun elo naa ba lo npgsql ati awọn iyipada laarin awọn apa ti o ro pe gbogbo wọn jẹ kanna, lẹhinna o le gba aṣiṣe:

EXCEPTION:Npgsql.PostgresException (0x80004005): XX000: cache lookup failed for type ...

Aṣiṣe yii yoo waye nitori mimuuṣiṣẹpọ wa ni ilọsiwaju

(NpgsqlConnection.GlobalTypeMapper.MapComposite<SomeType>("some_composite_type");) 

Awọn oriṣi akojọpọ ni ibẹrẹ ohun elo fun gbogbo awọn asopọ. Bi abajade, a gba idamo kan lati oju ipade kan, ati nigbati o ba beere lori ipade miiran, ko baramu, nitori abajade eyi ti aṣiṣe kan pada, i.e. Ṣiṣẹ ni gbangba pẹlu awọn oriṣi akojọpọ ninu iṣupọ kii yoo ṣee ṣe fun diẹ ninu awọn ohun elo laisi awọn atunkọ-ẹgbẹ afikun ohun elo (ti o ba ṣakoso lati ṣe bẹ).

Gẹgẹbi gbogbo wa ṣe mọ, igbelewọn gbogbogbo ti ipo iṣupọ jẹ pataki pupọ fun awọn iwadii aisan ati awọn iwọn iṣiṣẹ lakoko iṣẹ, ninu ọja naa o le rii diẹ ninu awọn iṣẹ ti o yẹ ki o jẹ ki igbesi aye rẹ rọrun, ṣugbọn nigbakan wọn le fun nkan ti o yatọ patapata si kini iwọ ati paapaa olupese funrararẹ nireti lati ọdọ wọn ti o n reti.

Fun apere:

select mtm.collect_cluster_info();
на каждой ноде выдает одинаковый результат:
(1,Online,0,0,0,2,3,0,0,0,1,0,0,1,1,3,7,0,0,0,"2018-10-31 05:33:06")
(2,Online,0,0,0,2,3,0,0,0,1,0,0,1,1,3,7,0,0,0,"2018-10-31 05:33:06")
(3,Online,0,0,0,2,3,0,0,0,1,0,0,1,1,3,7,0,0,0,"2018-10-31 05:33:09")

Ṣugbọn kilode ti aaye LiveNodes ni nọmba 2 nibi gbogbo, botilẹjẹpe gẹgẹ bi ijuwe ti iṣiṣẹ multimaster o yẹ ki o baamu si nọmba AllNodes=3? Idahun: o nilo lati ṣe imudojuiwọn ẹya DBMS naa.

Ki o si mura lati gba awọn iwe-ipamọ fun gbogbo awọn apa, nitori… nigbagbogbo iwọ yoo rii "aṣiṣe naa wa ninu akọọlẹ ti ipade miiran." Atilẹyin imọ-ẹrọ yoo gba gbogbo awọn abawọn ti o ṣe idanimọ ati sọ fun ọ pe ẹya atẹle ti ṣetan, eyiti yoo nilo nigbakan lati fi sii pẹlu iṣẹ duro, nigbakan fun igba pipẹ (da lori iwọn DBMS rẹ). O yẹ ki o ko nireti pe awọn iṣoro iṣiṣẹ yoo ṣe idamu ataja naa, ati pe imudojuiwọn nitori awọn abawọn ti a mọ yoo ṣee ṣe pẹlu ikopa ti awọn aṣoju olutaja, tabi dipo, iwọ ko paapaa nilo lati kan awọn aṣoju olutaja, nitori ninu opin o le pari soke pẹlu kan disassembled iṣupọ ni gbóògì lai afẹyinti.

Lootọ, ninu iwe-aṣẹ fun ọja iṣowo kan, olupese naa kilọ nitootọ: “A pese sọfitiwia yii lori “bi o ti ri” ati Postgres Professional Limited Layabiliti Company ko ni ọranyan lati pese itọju, atilẹyin, awọn imudojuiwọn, awọn amugbooro tabi awọn ayipada.”

Ti o ko ba ti gboju iru ọja wo ni a n sọrọ nipa rẹ, lẹhinna gbogbo iriri yii ni a gba bi abajade iṣẹ ṣiṣe ọdun ti Postgres Pro Enterprise database. O le fa ipari ti ara rẹ, o jẹ ọririn ti awọn olu dagba.

Ṣugbọn eyi kii yoo buru bẹ ti a ba ṣe ni akoko ti o tọ ati ki o mu awọn iṣoro ti n yọ jade ni kiakia.

Ṣugbọn eyi ni pato ohun ti ko ṣẹlẹ. Nkqwe olupese ko ni awọn orisun to lati yọkuro awọn idun ti a mọ ni kiakia.

Awọn olumulo ti o forukọsilẹ nikan le kopa ninu iwadi naa. wọle, Jowo.

Ṣe o ni iriri ni yi pada lati ajeji / ohun-ini DBMS si ọfẹ/ti inu ile?

• 21,3%Bẹẹni, rere10

• 10,6%Bẹẹni, odi5

• 21,3%Rara, DBMS ko yipada10

• 4,3%DBMS ti yipada, ṣugbọn ko si ohun ti o yipada2

• 42,6%Wo esi20

47 olumulo dibo. 12 olumulo abstained.

orisun: www.habr.com