"Pro, tab sis tsis yog pawg" lossis yuav ua li cas peb hloov DBMS imported

(ts) Yandex.Images

Tag nrho cov cim yog qhov tseeb, cov cim lag luam yog lawv tus tswv, txhua qhov zoo sib xws yog random thiab feem ntau, qhov no yog kuv "kev txiav txim siab tus nqi, thov tsis txhob rhuav lub qhov rooj ...".

Peb muaj kev paub ntau yam hauv kev hloov cov ntaub ntawv nrog cov logic rau hauv cov ntaub ntawv los ntawm ib qho DBMS mus rau lwm qhov. Nyob rau hauv cov ntsiab lus ntawm tsoom fwv tsab cai No. 1236 ntawm lub Kaum Ib Hlis 16.11.2016, XNUMX, qhov no feem ntau yog hloov los ntawm Oracle mus rau Postgresql. Peb tuaj yeem qhia koj cais yuav ua li cas los npaj cov txheej txheem kom ua tau zoo thiab tsis mob siab npaum li qhov ua tau; hnub no peb yuav tham txog cov yam ntxwv ntawm kev siv cov pawg thiab cov teeb meem dab tsi tuaj yeem ntsib thaum tsim cov tshuab xa khoom hnyav nrog cov logic nyuaj hauv cov txheej txheem thiab kev ua haujlwm.

Spoiler - yog, cap, RAC thiab pg multimaster yog cov kev daws teeb meem sib txawv heev.

Cia peb hais tias koj twb pauv tag nrho cov logic ntawm plsql rau pgsql. Thiab koj qhov kev ntsuam xyuas rov qab zoo heev, tam sim no koj tab tom xav txog kev ntsuas, vim ... load xeem tsis ua rau koj zoo siab heev, tshwj xeeb tshaj yog nyob rau hauv lub hardware uas yog Ameslikas muaj nyob rau hauv lub project, rau qhov txawv heev DBMS. Cia peb hais tias koj pom ib qho kev daws teeb meem los ntawm cov neeg muag khoom hauv tsev "Postgres Professional" nrog rau kev xaiv hu ua "multimaster", uas tsuas yog muaj nyob rau hauv "siab tshaj" version ntawm "Postgres Pro Enterprise" thiab raws li cov lus piav qhia - nws zoo ib yam li dab tsi. koj xav tau, thiab nrog rau thawj qhov kev tshawb fawb superficial nws yuav tuaj qhov kev xav los rau hauv kuv lub taub hau: "Au! Qhov ntawd yog nws hloov RAC! Thiab txawm tias muaj cov raj xa dej hauv peb lub tebchaws! "

Tab sis tsis txhob maj kom zoo siab, thiab ntxiv peb yuav piav qhia vim li cas koj yuav tsum paub cov nuances, vim ... lawv nyuaj rau twv, txawm tias tom qab nyeem cov ntaub ntawv ntawm cov khoom tiav. Ntsuam xyuas seb koj puas npaj txhij los hloov kho DBMS versions ncaj qha rau ntawm qhov chaw tsim khoom, vim Qee qhov tsis xws luag tsis sib haum nrog kev siv hauv kev lag luam thiab nyuaj rau kev kuaj xyuas thaum kuaj.
Pib los ntawm kev ua tib zoo nyeem cov "multimaster" - "kev txwv" ntu ntawm cov chaw tsim khoom lub vev xaib.

Thawj qhov uas koj yuav ntsib yog qhov peculiarities ntawm yuav ua li cas kev lag luam ua haujlwm, hauv qhov hu ua. "ob-theem" ​​hom, thiab qee zaum tsis muaj txoj hauv kev los kho qhov no tsuas yog los ntawm kev rov sau tag nrho cov logic ntawm koj cov txheej txheem. Nov yog ib qho piv txwv yooj yim:

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;

Ib qho yuam kev tshwm sim:

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

Tom qab ntawd koj tuaj yeem sib ntaus rau lub sijhawm ntev nrog lub xauv tuag nyob rau hauv version 10.5, 10.6, thiab tsuas yog paub daws teeb meem uas tua tag nrho cov ntsiab lus ntawm pawg yog tshem tawm "teeb ​​meem" cov ntxhuav los ntawm pawg, piv txwv li. ua make_table_local, tab sis qhov no tsawg kawg yuav tso cai rau nws ua hauj lwm, thiab yuav tsis muab txhua yam rau tuav vim yog dai tos rau kev sib pauv commits. Zoo, lossis nruab qhov hloov tshiab rau version 11.2, uas yuav tsum pab, tab sis tej zaum tsis yog, tsis txhob hnov ​​​​qab xyuas.

Nyob rau hauv ib co versions koj yuav tau txais ib tug txawm ntau mysterious xauv:

username= mtm и backend_type = background worker

Thiab hauv qhov xwm txheej no, tsuas yog hloov kho DBMS version rau 11.2 thiab siab dua yuav pab tau koj, lossis tej zaum nws yuav tsis pab.

Qee qhov kev ua haujlwm nrog cov ntsuas ntsuas tuaj yeem ua rau muaj qhov yuam kev, uas qhia meej tias qhov teeb meem yog nyob rau hauv Bi-Directional Replication; koj yuav ncaj qha pom BDR hauv MTM cav. Puas yog 2ndQuadrant tiag tiag? Tsis yog... peb yuav ib tug multimaster, nws yog ib tug coincidence, nws yog lub npe ntawm lub tshuab.

[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 

Yog tias koj siv cov ntxhuav ib ntus, txawm tias muaj kev lees paub: "Tus multimaster txuas ntxiv ua cov ntaub ntawv rov ua dua tshiab hauv qhov tsis siv neeg kiag li. Koj tuaj yeem ua ib txhij sau cov ntawv pauv thiab ua haujlwm nrog cov ntxhuav ib ntus ntawm txhua qhov hauv pawg. "

Tom qab ntawd qhov tseeb koj yuav tau txais qhov kev rov ua dua tsis ua haujlwm thoob plaws txhua lub rooj siv hauv cov txheej txheem, yog tias cov cai muaj cov kev tsim ntawm lub rooj ib ntus, thiab txawm tias siv multimaster.remote_functions yuav tsis pab, koj yuav tau hloov kho lossis rov sau koj cov logic hauv. tus txheej txheem. Yog tias koj xav siv ob qhov txuas ntxiv multimaster thiab pg_pathman ib txhij hauv Postgres Pro Enterprise v 10.5, tom qab ntawd xyuas qhov piv txwv yooj yim no:

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);

Cov teeb meem hauv qab no pib tshwm hauv cov ntawv teev npe ntawm DBMS nodes:

…
 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

Koj tuaj yeem pom tias qhov yuam kev no nyob rau hauv kev txhawb nqa, nws tsis yog qhov tsis muaj txiaj ntsig uas koj yuav nws.

Yuav ua li cas? Txoj cai! Hloov kho mus rau "Postgres Pro Enterprise" v 11.2

Cais, koj yuav tsum paub tias qhov sib lawv liag, ua ib yam khoom ntawm cov ntaub ntawv rov ua dua, tsis muaj qhov kawg-rau-kawg thoob plaws hauv pawg, txhua qhov sib lawv liag hauv zos rau txhua lub ntawm thiab yog tias koj muaj thaj chaw nrog cov kev txwv tshwj xeeb thiab siv cov kab ke, tom qab ntawd koj tsuas tuaj yeem ua qhov nce sib npaug rau ntawm tus lej ntawm pawg hauv pawg, vim Raws li ntau cov nodes hauv pawg li ua tau, ntu thiab int yuav loj hlob sai dua li qhov koj xav tau. Txhawm rau ua kom yooj yim ua haujlwm nrog cov kab ke hauv cov khoom koj tseem yuav pom qhov kev ua haujlwm alter_sequences, uas yuav ua rau qhov tsim nyog nce ntxiv rau txhua ntu ntawm txhua qhov, tab sis npaj siab tias txoj haujlwm yuav tsis ua haujlwm hauv txhua lub versions. Tau kawg, koj tuaj yeem sau koj tus kheej, siv cov cai los ntawm github ua lub hauv paus lossis kho nws tus kheej ncaj qha hauv DBMS. Hauv qhov no, cov teb nrog hom serialbigserial yuav ua haujlwm kom raug, tab sis siv lawv, feem ntau koj yuav tsum rov sau cov cai ntawm koj cov txheej txheem thiab kev ua haujlwm. Tej zaum ib tug neeg yuav pom cov monotonic_sequences ua haujlwm pab tau.

Ua ntej version 11.2 ntawm Postgres Pro Enterprise, replication tsuas yog ua haujlwm yog tias muaj cov yuam sij tshwj xeeb, coj qhov no mus rau hauv tus account thaum tsim.

Cais, kuv xav hais txog qhov peculiarities li cas npgsql ua hauj lwm nyob rau hauv ib pawg kev daws teeb meem; cov teeb meem no tsis tshwm sim ntawm ib leeg, tab sis yog tam sim no nyob rau hauv ib tug multimaster.
Hauv qee qhov versions koj yuav ntsib qhov yuam kev:

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. 

Yuav ua li cas? Koj tsuas yog yuav tsum tsis txhob siv qee qhov versions. Koj yuav tsum paub lawv, vim ... Qhov yuam kev tshwm nyob rau hauv ntau tshaj ib tug version, thiab txawm tias tom qab nws thawj kho, koj yuav ntsib nws tom qab. Koj kuj yuav tsum tau npaj rau qhov no thiab nws yog qhov zoo dua los npog tag nrho cov teeb meem DBMS uas tau raug kho los ntawm cov chaw tsim khoom nrog cov kev ntsuam xyuas sib txawv. Yog li hais lus, ntseeg, tab sis txheeb xyuas.

Yog tias daim ntawv thov siv npgsql thiab hloov pauv ntawm cov nodes xav tias lawv zoo ib yam, ces koj yuav tau txais qhov yuam kev:

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

Qhov kev ua yuam kev no yuav tshwm sim vim qhov kev sib txuas tau ua tiav

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

hom sib xyaw ntawm daim ntawv thov pib rau txhua qhov kev sib txuas. Raws li qhov tshwm sim, peb tau txais tus lej cim los ntawm ib qho, thiab thaum thov ntawm lwm qhov, nws tsis sib xws, vim tias qhov yuam kev raug xa rov qab, i.e. Ua haujlwm pob tshab nrog cov hom sib xyaw hauv pawg yuav ua tsis tau rau qee daim ntawv thov yam tsis muaj daim ntawv thov ntxiv-sab rov sau dua (yog tias koj tswj ua li ntawd).

Raws li peb txhua tus paub, kev ntsuam xyuas tag nrho ntawm lub xeev ntawm pawg yog qhov tseem ceeb heev rau kev kuaj mob thiab kev ntsuas kev ua haujlwm thaum lub sijhawm ua haujlwm, hauv cov khoom koj tuaj yeem pom qee yam haujlwm uas yuav tsum ua rau koj lub neej yooj yim dua, tab sis qee zaum lawv tuaj yeem muab qee yam txawv ntawm qhov. koj thiab txawm tias cov chaw tsim khoom lawv tus kheej xav tau los ntawm lawv koj xav tau.

Piv txwv li:

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")

Tab sis vim li cas LiveNodes teb muaj tus lej 2 nyob txhua qhov chaw, txawm hais tias raws li cov lus piav qhia ntawm multimaster txoj haujlwm nws yuav tsum sib haum rau tus lej AllNodes = 3? Teb: koj yuav tsum tau hloov kho DBMS version.

Thiab npaj los sau cov cav rau tag nrho cov nodes, vim ... feem ntau koj yuav pom "qhov yuam kev yog nyob rau hauv lub cav ntawm lwm lub node." Kev pab txhawb nqa yuav lees txais txhua qhov tsis xws luag uas koj txheeb xyuas thiab qhia rau koj tias cov ntawv txuas ntxiv yog npaj txhij, uas qee zaum yuav tsum tau nruab nrog cov kev pabcuam nres, qee zaum ntev (nyob ntawm qhov loj ntawm koj DBMS). Koj yuav tsum tsis txhob cia siab tias cov teeb meem kev ua haujlwm yuav cuam tshuam rau tus neeg muag khoom, thiab qhov kev hloov kho tshiab vim muaj qhov tsis xws luag yuav raug ua nrog kev koom tes ntawm tus neeg muag khoom cov neeg sawv cev, los yog, koj tsis tas yuav koom nrog cov neeg muag khoom cov neeg sawv cev, txij li thaum nyob rau hauv kawg koj tuaj yeem xaus nrog pawg disassembled hauv ntau lawm yam tsis muaj thaub qab.

Qhov tseeb, hauv daim ntawv tso cai rau cov khoom lag luam, cov chaw tsim khoom ncaj ncees ceeb toom: "Qhov software no yog muab los ntawm "raws li yog" thiab Postgres Professional Limited Liability Company tsis yog lub luag haujlwm los muab kev saib xyuas, txhawb nqa, hloov tshiab, txuas ntxiv lossis hloov pauv.

Yog tias koj tseem tsis tau twv seb cov khoom twg peb tab tom tham txog, ces tag nrho cov kev paub no tau txais los ntawm kev ua haujlwm ntev xyoo ntawm Postgres Pro Enterprise database. Koj tuaj yeem kos koj tus kheej qhov kev txiav txim, nws ntub heev uas cov nceb loj tuaj.

Tab sis qhov no yuav tsis phem heev yog tias nws tau ua tiav raws sijhawm thiab tshem tawm cov teeb meem tshwm sim sai.

Tab sis qhov no yog precisely dab tsi tshwm sim. Pom tau tias cov chaw tsim khoom tsis muaj peev txheej txaus los tshem tawm cov kab mob tam sim ntawd.

Tsuas yog cov neeg siv sau npe tuaj yeem koom nrog hauv daim ntawv ntsuam xyuas. Kos npe rau hauvthov.

Koj puas muaj kev paub dhau los ntawm kev hloov pauv los ntawm txawv teb chaws / tus tswv DBMS mus rau ib qho dawb / hauv tsev?

• 21,3%Yog, zoo 10

• 10,6%Yog, negative5

• 21,3%Tsis yog, DBMS tsis tau hloov pauv 10

• 4,3%DBMS tau hloov, tab sis tsis muaj dab tsi hloov 2

• 42,6%Saib cov txiaj ntsig 20

47 cov neeg siv pov npav. 12 cov neeg siv txwv tsis pub siv.

Tau qhov twg los: www.hab.com