Ukuphindaphinda akuyona ikhophi yasenqolobaneni. Noma cha? Nansi indlela esisebenzise ngayo ukuphindaphinda okuhlehlisiwe ukuze silulame ekususeni izinqamuleli ngephutha.
I-GitLab inesibopho somsebenzi - isibonelo esikhulu kunazo zonke se-GitLab ngokwemvelo. Ngabasebenzisi abayizigidi ezi-3 namaphrojekthi acishe abe yizigidi eziyisi-7, ingenye yezindawo ezinkulu ezivulekile ze-SaaS ezinomthombo ozinikele wezakhiwo. Ngaphandle kwesistimu yedatha ye-PostgreSQL, ingqalasizinda ye-GitLab.com ngeke ihambe ibanga elide, futhi senzani ukuze siqinisekise ukubekezelelana kwamaphutha uma kwenzeka kuba nokwehluleka lapho idatha ilahleka. Angeke kwenzeke ukuthi inhlekelele enjalo yenzeke, kodwa sizilungiselele kahle futhi sigcwele izindlela ezihlukahlukene zokusekela nokuphindaphinda.
Ukuphindaphinda akuyona indlela yokwenza ikhophi yolwazi (). Kodwa manje sizobona ukuthi ungayithola kanjani ngokushesha idatha esuswe ngephutha usebenzisa ukuphindaphinda okuvilaphayo: kuvuliwe umsebenzisi okwephrojekthi nokulahlekelwa ukuxhumana nezicelo zokuhlanganisa nemisebenzi.
Ngomfanekiso omisiwe, sibuyise idatha emahoreni angu-1,5 nje. Buka ukuthi kwenzeke kanjani.
Khomba ekubuyiseleni isikhathi nge-PostgreSQL
I-PostgreSQL inomsebenzi owakhelwe ngaphakathi obuyisela isimo sesizindalwazi endaweni ethile ngesikhathi. Kubizwa (PITR) futhi isebenzisa izindlela ezifanayo ezigcina isifaniso sisesikhathini samanje: siqala ngesifinyezo esithembekile salo lonke iqoqo lesizindalwazi (isipele esiyisisekelo), sisebenzisa uchungechunge lwezinguquko zesimo kuze kufike endaweni ethile ngesikhathi.
Ukusebenzisa lesi sici ukwenza ikhophi yasenqolobaneni ebandayo, senza njalo isipele sesizindalwazi esiyisisekelo futhi sisigcine kungobo yomlando (Izingobo zomlando ze-GitLab zihlala kuyo. ). Siphinde siqaphe izinguquko esimweni sesizindalwazi ngokufaka kungobo yomlando ilogi yokubhala phambili (, WAL). Futhi ngakho konke lokhu endaweni, singenza i-PITR yokuthola kabusha inhlekelele: siqala ngesifinyezo esithathwe ngaphambi kokwehluleka, nokusebenzisa izinguquko ezisuka kungobo yomlando ye-WAL kuze kufike ekuhlulekeni.
Kuyini ukuphindaphinda okuhlehlisiwe?
Ukuphindaphinda okuvilapha ukusetshenziswa kwezinguquko ezivela ku-WAL ngokubambezeleka. Okusho ukuthi, ukuthengiselana kwenzeke ngehora X, kodwa izovela kusifaniso ngokubambezeleka d ngehora X + d.
I-PostgreSQL inezindlela ezi-2 zokusetha i-replica yesizindalwazi esiphathekayo: ukubuyisela isipele nokuphindaphinda kokusakaza. , empeleni isebenza njenge-PITR, kodwa ngokuqhubekayo: sihlala silanda izinguquko kungobo yomlando ye-WAL futhi sizisebenzisa ekufaniseni. A ibuyisa ngokuqondile ukusakaza kwe-WAL kumsingathi wesizindalwazi okhuphukayo. Sincamela ukutholwa kwengobo yomlando - kulula ukuyiphatha futhi inokusebenza okuvamile okuhambisana neqoqo lokukhiqiza.
Ungasetha kanjani ukutakula okubambezelekile kusuka kungobo yomlando
echazwe kufayela recovery.conf. Isibonelo:
standby_mode = 'on'
restore_command = '/usr/bin/envdir /etc/wal-e.d/env /opt/wal-e/bin/wal-e wal-fetch -p 4 "%f" "%p"'
recovery_min_apply_delay = '8h'
recovery_target_timeline = 'latest'Ngala mapharamitha, silungiselele ikhophi ehlehlisiwe ngokuthola ikhophi yasenqolobaneni. Lapha kuyasetshenziswa ukukhipha izingxenye ze-WAL (restore_command) kusuka kungobo yomlando, futhi izinguquko zizosetshenziswa ngemva kwamahora ayisishiyagalombili (recovery_min_apply_delay). Isifaniso sizobuka izinguquko zomugqa wesikhathi kungobo yomlando, isibonelo ngenxa yokuhluleka kweqoqo (recovery_target_timeline).
Π‘ recovery_min_apply_delay Ungasetha ukuphindaphinda kokusakaza-bukhoma ngokubambezeleka, kodwa kukhona izingibe ezimbalwa lapha ezihlobene nezikhala zokuphindaphinda, impendulo eshisayo yokulinda, njalo njalo. Ingobo yomlando ye-WAL ikuvumela ukuthi uzigweme.
Ipharamitha recovery_min_apply_delay ivele kuphela ku-PostgreSQL 9.3. Ezinguqulweni ezedlule, ukuphindaphinda okuhlehlisiwe udinga ukulungisa inhlanganisela (pg_xlog_replay_pause(), pg_xlog_replay_resume()) noma ubambe amasegimenti e-WAL kungobo yomlando ngesikhathi sokulibaziseka.
I-PostgreSQL ikwenza kanjani lokhu?
Kuyathakazelisa ukubona ukuthi i-PostgreSQL isebenzisa kanjani ukutakula kobuvila. Ake sibheke . Ibizwa ngo kokungena ngakunye okuvela ku-WAL.
static bool
recoveryApplyDelay(XLogReaderState *record)
{
uint8 xact_info;
TimestampTz xtime;
long secs;
int microsecs;
/* nothing to do if no delay configured */
if (recovery_min_apply_delay <= 0)
return false;
/* no delay is applied on a database not yet consistent */
if (!reachedConsistency)
return false;
/*
* Is it a COMMIT record?
*
* We deliberately choose not to delay aborts since they have no effect on
* MVCC. We already allow replay of records that don't have a timestamp,
* so there is already opportunity for issues caused by early conflicts on
* standbys.
*/
if (XLogRecGetRmid(record) != RM_XACT_ID)
return false;
xact_info = XLogRecGetInfo(record) & XLOG_XACT_OPMASK;
if (xact_info != XLOG_XACT_COMMIT &&
xact_info != XLOG_XACT_COMMIT_PREPARED)
return false;
if (!getRecordTimestamp(record, &xtime))
return false;
recoveryDelayUntilTime =
TimestampTzPlusMilliseconds(xtime, recovery_min_apply_delay);
/*
* Exit without arming the latch if it's already past time to apply this
* record
*/
TimestampDifference(GetCurrentTimestamp(), recoveryDelayUntilTime,
&secs, µsecs);
if (secs <= 0 && microsecs <= 0)
return false;
while (true)
{
// Shortened:
// Use WaitLatch until we reached recoveryDelayUntilTime
// and then
break;
}
return true;
}Okubalulekile ukuthi ukubambezeleka kusekelwe esikhathini esingokoqobo esirekhodwe kusitembu sesikhathi sesibopho sokwenziwe (xtime). Njengoba ubona, ukubambezeleka kusebenza kuphela ekuzibophezeleni futhi akuthinti okunye okufakiwe - zonke izinguquko zisetshenziswa ngokuqondile, futhi isivumelwano sibambezelekile, ngakho sizobona kuphela izinguquko ngemva kokubambezeleka okumisiwe.
Ungayisebenzisa kanjani i-replica ebambezelekile ukubuyisela idatha
Ake sithi sineqoqo lesizindalwazi kanye nesifaniso esinokulibaziseka kwamahora ayisishiyagalombili ekukhiqizeni. Ake sibone ukuthi ungayibuyisela kanjani idatha usebenzisa isibonelo .
Lapho sifunda ngale nkinga, sathi okwesifaniso esihlehlisiwe:
SELECT pg_xlog_replay_pause();Ngokuma kancane, asibanga nabungozi bokuthi umfaniso ungaphinda isicelo DELETE. Into ewusizo uma udinga isikhathi sokuthola yonke into.
Iphuzu ukuthi ikhophi ehlehlisiwe kufanele ifinyelele isikhashana ngaphambi kwesicelo DELETE. Cishe sasazi isikhathi somzimba sokususwa. Sisusile recovery_min_apply_delay futhi wanezela recovery_target_time Π² recovery.conf. Lena yindlela isifaniso esifinyelela ngayo esikhathini esifanele ngaphandle kokulibala:
recovery_target_time = '2018-10-12 09:25:00+00'Ngezitembu zesikhathi, kungcono ukunciphisa okweqile ukuze ungaphuthelwa. Yiqiniso, ukwehla okukhulu, silahlekelwa idatha eyengeziwe. Futhi, uma siphuthelwa isicelo DELETE, yonke into izosuswa futhi futhi kuzodingeka ukuthi uqale phansi (noma uthathe isipele esibandayo se-PITR).
Siqale kabusha isibonelo esihlehlisiwe se-Postgres futhi amasegimenti e-WAL aphindwa kuze kube isikhathi esishiwo. Ungakwazi ukulandelela inqubekelaphambili kulesi sigaba ngokubuza:
SELECT
-- current location in WAL
pg_last_xlog_replay_location(),
-- current transaction timestamp (state of the replica)
pg_last_xact_replay_timestamp(),
-- current physical time
now(),
-- the amount of time still to be applied until recovery_target_time has been reached
'2018-10-12 09:25:00+00'::timestamptz - pg_last_xact_replay_timestamp() as delay;Uma isitembu sesikhathi singasashintshi, ukutholwa kuqediwe. Isenzo singenziwa ngokwezifiso ukuvala, ukukhuthaza, noma ukumisa isikhashana isenzakalo ngemva kokuzama futhi (kumiswe ngokuzenzakalelayo).
Isizindalwazi sibuyele esimweni saso ngaphambi kwaleso sicelo esiyishwa. Manje ungakwazi, isibonelo, ukuthekelisa idatha. Sikhiphe idatha yelebula esusiwe kanye nazo zonke izixhumanisi zezinkinga futhi sihlanganisa izicelo futhi sazihambisa kusizindalwazi sokukhiqiza. Uma ukulahlekelwa kukukhulu, ungamane uphromothe isifaniso futhi usisebenzise njengokuyinhloko. Kodwa-ke zonke izinguquko ngemva kwephuzu esiluleme kulo zizolahleka.
Esikhundleni sezitembu zesikhathi, kungcono ukusebenzisa ama-ID okwenziwayo. Kuwusizo ukurekhoda lawa ma-ID, isibonelo, ezitatimendeni ze-DDL (njenge DROP TABLE), ngokusebenzisa log_statements = 'ddl'. Ukube besine-ID yokwenziwe, besingathatha recovery_target_xid futhi ngenze yonke into yehlela emsebenzini ngaphambi kwesicelo DELETE.
Ukubuyela emsebenzini kulula kakhulu: susa zonke izinguquko recovery.conf bese uqala kabusha i-Postgres. Isifaniso maduzane sizoba nokubambezeleka kwamahora ayisishiyagalombili futhi, futhi silungele izinkinga ezizayo.
Izinzuzo Zokubuyisela
Ngesifaniso esihlehlisiwe esikhundleni sekhophi yasenqolobaneni ebandayo, akumele uchithe amahora ubuyisela sonke isithombe esivela kungobo yomlando. Isibonelo, kusithatha amahora amahlanu ukuthola sonke isipele esiyisisekelo esingu-2 TB. Futhi-ke kusafanele usebenzise yonke i-WAL yansuku zonke ukuze ubuyele esimweni osifunayo (esimeni esibi kakhulu).
Ikhophi ehlehlisiwe ingcono kune-backup ebandayo ngezindlela ezimbili:
- Asikho isidingo sokususa sonke isipele esiyisisekelo kungobo yomlando.
- Kukhona iwindi elilungisiwe lamahora ayisishiyagalombili lamasegimenti e-WAL okumele aphindwe.
Siphinde sihlole njalo ukuze sibone ukuthi kuyenzeka yini ukwenza i-PITR kusuka ku-WAL, futhi sizoqaphela ngokushesha inkohlakalo noma ezinye izinkinga ngengobo yomlando ye-WAL ngokuqapha ukushiyeka kwesifaniso esihlehlisiwe.
Kulesi sibonelo, kusithathe imizuzu engu-50 ukubuyisela, okusho ukuthi isivinini kwakungu-110 GB wedatha ye-WAL ngehora (ingobo yomlando yayisavuliwe. ). Sekukonke, siyixazulule inkinga futhi sabuyisela idatha emahoreni angu-1,5.
Imiphumela: lapho ikhophi ehlehlisiwe iwusizo (futhi lapho ingekho khona)
Sebenzisa ukuphindaphinda okulibaziseke njengosizo lokuqala uma ulahlekelwe idatha ngephutha futhi uqaphele le nkinga phakathi nokulibaziseka okumisiwe.
Kodwa khumbula: ukuphindaphinda akuyona ikhophi yasenqolobaneni.
Ukwenza ikhophi yasenqolobaneni nokuphindaphinda kunezinjongo ezihlukene. Isipele esibandayo sizoba usizo uma wenze ngephutha DELETE noma DROP TABLE. Senza ikhophi yasenqolobaneni endaweni ebandayo futhi sibuyisela isimo sangaphambilini setafula noma yonke imininingwane egciniwe. Kodwa ngesikhathi esifanayo isicelo DROP TABLE icishe yenziwe kabusha kuzo zonke izifaniso zeqoqo elisebenzayo, ngakho ukuphindaphinda okuvamile ngeke kusize lapha. Ukuphindaphinda ngokwako kugcina isizindalwazi sitholakala lapho amaseva angawodwana eqashwa futhi asabalalisa umthwalo.
Ngisho nesifaniso esihlehlisiwe, ngezinye izikhathi sidinga ikhophi yasenqolobaneni ebandayo endaweni ephephile uma kwenzeka ukwehluleka kwesikhungo sedatha, umonakalo ofihliwe, noma ezinye izehlakalo ezingabonakali ngokushesha. Ukuphindaphinda kukodwa akusizi ngalutho lapha.
Ukubhala. Kuvuliwe Okwamanje sivikela kuphela ekulahlekeni kwedatha ezingeni lesistimu futhi asibuyiseli idatha ezingeni lomsebenzisi.
Source: www.habr.com