{"id":30356,"date":"2019-10-31T21:35:06","date_gmt":"2019-10-31T18:35:06","guid":{"rendered":"https:\/\/prohoster.info\/blog\/kak-my-ispolzovali-otlozhennuyu-replikatsiyu-dlya-avarijnogo-vosstanovleniya-s-postgresql\/"},"modified":"2019-10-31T21:35:06","modified_gmt":"2019-10-31T18:35:06","slug":"kak-my-ispolzovali-otlozhennuyu-replikatsiyu-dlya-avarijnogo-vosstanovleniya-s-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sl\/blog\/administrirovanie\/kak-my-ispolzovali-otlozhennuyu-replikatsiyu-dlya-avarijnogo-vosstanovleniya-s-postgresql","title":{"rendered":"Kako smo uporabljali odlo\u017eeno replikacijo za nujno obnovo s PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\"Kako
\nReplikacija ni varnostna kopija. Ali pa je? Tukaj je, kako smo uporabili odlo\u017eeno replikacijo za obnovo po naklju\u010dnem brisanju bli\u017enjic.<\/p>\n

<\/p>\n

Strokovnjaki za infrastrukturo<\/a><\/noindex> na GitLabu so odgovorni za delovanje GitLab.com<\/a><\/noindex> \u2014 najve\u010djega primerka GitLaba na svetu. Tukaj je 3 milijone uporabnikov in skoraj 7 milijonov projektov, kar ga uvr\u0161\u010da med najve\u010dje odprtokodne SaaS strani z namensko arhitekturo. Brez sistema baz podatkov PostgreSQL bi infrastruktura GitLab.com te\u017eko delovala, in kar koli \u017ee po\u010dnemo za zagotavljanje odpornosti v primeru okvar, ki bi lahko povzro\u010dile izgubo podatkov. Malo verjetno je, da bi se tak\u0161na katastrofa zgodila, a smo se dobro pripravili in se opremili z razli\u010dnimi mehanizmi varnostnega kopiranja in replikacije.<\/p>\n

<\/p>\n

Replikacija ni sredstvo za varnostno kopiranje baz podatkov (glejte spodaj<\/a><\/noindex>). A zdaj bomo videli, kako hitro lahko obnovimo po naklju\u010dnem brisanju podatke s pomo\u010djo odlo\u017eene replikacije: na GitLab.com<\/a><\/noindex> uporabnik je izbrisal bli\u017enjico<\/a><\/noindex> za projekt gitlab-ce<\/code><\/a><\/noindex> in izgubil povezave z zdru\u017eitvenimi zahtevki in nalogami.<\/p>\n

<\/p>\n

S pomo\u010djo odlo\u017eene replikacije smo obnovili podatke v samo 1,5 uri. Poglejte, kako je potekalo.<\/p>\n

<\/a><\/noindex><\/p>\n

Obnova na to\u010dno \u010dasovno to\u010dko s PostgreSQL<\/h3>\n

<\/p>\n

PostgreSQL ima vgrajeno funkcijo, ki obnavlja stanje baze podatkov na dolo\u010den trenutek. Imenuje se Obnova na to\u010dno dolo\u010den \u010das<\/a><\/noindex> (PITR) in uporablja enake mehanizme, ki podpirajo posodobljeno replikacijo: za\u010denjamo z zanesljivim posnetkom celotnega klasterja baz podatkov (osnovna varnostna kopija) in \u0161tevimo vrsto sprememb stanja do dolo\u010denega trenutka.<\/p>\n

<\/p>\n

Da bi to funkcijo uporabili za hladno varnostno kopijo, redno izdelujemo osnovno varnostno kopijo baze podatkov in jo shranjujemo v arhiv (arhivi GitLaba \u017eivijo v Google Cloud Storage<\/a><\/noindex>). Prav tako spremljamo spremembe v stanju baze podatkov z arhiviranjem dnevnika predhodnih zapisov (dnevnik predhodnih zapisov<\/a><\/noindex>, WAL). In s tem lahko izvedemo PITR za nujno obnovo: za\u010dnemo s posnetkom, ustvarjenim pred napako, in uporabljamo spremembe iz arhiva WAL do napake.<\/p>\n

<\/p>\n

Kaj je odlo\u017eena replikacija?<\/h3>\n

<\/p>\n

Odlo\u017eena replikacija je uporaba sprememb iz WAL z zamudo. To pomeni, da se je transakcija zgodila v uri X<\/code>, toda v replikaciji se bo pojavila z zamudo d<\/code> v eni uri X + d<\/code>.<\/p>\n

<\/p>\n

V PostgreSQL sta dva na\u010dina za nastavitev fizi\u010dne replikacije baze podatkov: obnovitev iz arhiva in tokovna replikacija. Obnovitev iz arhiva<\/a><\/noindex>, v bistvu deluje kot PITR, vendar neprekinjeno: nenehno pridobivamo spremembe iz arhiva WAL in jih uporabljamo na replikaciji. A tokovna replikacija<\/a><\/noindex> pridobiva tok WAL neposredno iz nadrejene baze podatkov. Raje imamo obnovitev iz arhiva \u2014 la\u017eje je upravljati in ima normalno zmogljivost, ki ne zaostaja za delovnim zvezdnim oblikovnim sistemom.<\/p>\n

<\/p>\n

Kako nastaviti odlo\u017eeno obnovitev iz arhiva<\/h3>\n

<\/p>\n

Mo\u017enosti obnove<\/a><\/noindex> so opisane v datoteki recovery.conf<\/code>. Primer:<\/p>\n

<\/p>\n

standby_mode = 'on'\nrestore_command = 'usr\/bin\/envdir \/etc\/wal-e.d\/env \/opt\/wal-e\/bin\/wal-e wal-fetch -p 4 \"%f\" \"%p\"'\nrecovery_min_apply_delay = '8h'\nrecovery_target_timeline = 'latest'<\/code><\/pre>\n
<\/p>\n
S temi parametri smo nastavili odlo\u017eeno replikacijo z obnovitvijo iz arhiva. Uporablja se wal-e<\/a><\/noindex> za pridobivanje segmentov WAL (restore_command<\/code>) iz arhiva, spremembe pa bodo uporabljene po osmih urah (recovery_min_apply_delay<\/code>). Replikacija bo spremljala spremembe \u010dasovne osi v arhivu, na primer zaradi obdelave odpovedi v zvezdnem oblaku (recovery_target_timeline<\/code>).<\/p>\n
<\/p>\n
Z recovery_min_apply_delay<\/code> lahko nastavite tokovno replikacijo z odlo\u017eitvijo, a tu obstaja nekaj pasti, povezanih s prostori replikacije, povratnimi informacijami aktivnega rezervnega in podobno. Arhiv WAL omogo\u010da, da se temu izognemo.<\/p>\n
<\/p>\n
Parametri recovery_min_apply_delay<\/code> se je pojavil \u0161ele v PostgreSQL 9.3. V prej\u0161njih razli\u010dicah je bilo za odlo\u017eeno replikacijo treba nastaviti kombinacijo funkcij za upravljanje obnove<\/a><\/noindex> (pg_xlog_replay_pause(), pg_xlog_replay_resume()<\/code>) ali obdr\u017eati segmentne WAL v arhivu za \u010das odlo\u017eitve.<\/p>\n
<\/p>\n
Kako to po\u010dne PostgreSQL?<\/h3>\n
<\/p>\n
Zanimivo je videti, kako PostgreSQL implementira odlo\u017eeno obnovitev. Poglejmo na recoveryApplyDelay(XlogReaderState)<\/code><\/a><\/noindex>. Kli\u010de se iz glavnega cikla ponovnega<\/a><\/noindex> za vsak zapis iz WAL.<\/p>\n
<\/p>\n
static bool\nrecoveryApplyDelay(XLogReaderState *record)\n{\n    uint8       xact_info;\n    TimestampTz xtime;\n    long        secs;\n    int         microsecs;\n\n    \/* \u043d\u0438\u0447\u0435\u0433\u043e \u043d\u0435 \u0434\u0435\u043b\u0430\u0442\u044c, \u0435\u0441\u043b\u0438 \u0437\u0430\u0434\u0435\u0440\u0436\u043a\u0430 \u043d\u0435 \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043d\u0430 *\/\n    if (recovery_min_apply_delay <= 0)\n        return false;\n\n    \/* \u0437\u0430\u0434\u0435\u0440\u0436\u043a\u0430 \u043d\u0435 \u043f\u0440\u0438\u043c\u0435\u043d\u044f\u0435\u0442\u0441\u044f \u043a \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445, \u043a\u043e\u0442\u043e\u0440\u0430\u044f \u0435\u0449\u0435 \u043d\u0435 \u043f\u043e\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u043d\u043e *\/\n    if (!reachedConsistency)\n        return false;\n\n    \/*\n     * \u042d\u0442\u043e \u0437\u0430\u043f\u0438\u0441\u044c COMMIT?\n     *\n     * \u041c\u044b \u0441\u043f\u0435\u0446\u0438\u0430\u043b\u044c\u043d\u043e \u0432\u044b\u0431\u0438\u0440\u0430\u0435\u043c \u043d\u0435 \u0437\u0430\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u0442\u044c \u043e\u0442\u043c\u0435\u043d\u044b, \u0442\u0430\u043a \u043a\u0430\u043a \u043e\u043d\u0438 \u043d\u0435 \u0432\u043b\u0438\u044f\u044e\u0442 \u043d\u0430\n     * MVCC. \u041c\u044b \u0443\u0436\u0435 \u043f\u043e\u0437\u0432\u043e\u043b\u044f\u0454\u043c\u043e \u0432\u043e\u0441\u043f\u0440\u043e\u0438\u0437\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u0437\u0430\u043f\u0438\u0441\u0435\u0439, \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043d\u0435\u0442 \u043e\u0442\u043c\u0435\u0442\u043a\u0438 \u0432\u0440\u0435\u043c\u0435\u043d\u0438,\n     * \u043f\u043e\u044d\u0442\u043e\u043c\u0443 \u0443\u0436\u0435 \u0441\u0443\u0449\u0435\u0441\u0442\u0432\u0443\u0435\u0442 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044c \u043f\u043e\u044f\u0432\u043b\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0431\u043b\u0435\u043c \u0438\u0437-\u0437\u0430 \u0440\u0430\u043d\u043d\u0438\u0445 \u043a\u043e\u043d\u0444\u043b\u0438\u043a\u0442\u043e\u0432 \u043d\u0430\n     * \u0440\u0435\u0437\u0435\u0440\u0432\u043d\u044b\u0445 \u043a\u043e\u043f\u0438\u044f\u0445.\n     * \/\n    if (XLogRecGetRmid(record) != RM_XACT_ID)\n        return false;\n\n    xact_info = XLogRecGetInfo(record) & XLOG_XACT_OPMASK;\n\n    if (xact_info != XLOG_XACT_COMMIT &&\n        xact_info != XLOG_XACT_COMMIT_PREPARED)\n        return false;\n\n    if (!getRecordTimestamp(record, &xtime))\n        return false;\n\n    recoveryDelayUntilTime =\n        TimestampTzPlusMilliseconds(xtime, recovery_min_apply_delay);\n\n    \/*\n     * \u0412\u044b\u0439\u0442\u0438 \u0431\u0435\u0437 \u0430\u043a\u0442\u0438\u0432\u0430\u0446\u0438\u0438 \u0437\u0430\u0436\u0438\u043c\u0430, \u0435\u0441\u043b\u0438 \u0443\u0436\u0435 \u043f\u0440\u043e\u0448\u043b\u043e \u0432\u0440\u0435\u043c\u044f \u0434\u043b\u044f \u043f\u0440\u0438\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u044d\u0442\u043e\u0439\n     * \u0437\u0430\u043f\u0438\u0441\u0438\n     * \/\n    TimestampDifference(GetCurrentTimestamp(), recoveryDelayUntilTime,\n                        &secs, &microsecs);\n    if (secs <= 0 && microsecs <= 0)\n        return false;\n\n    while (true)\n    {\n        \/\/ \u0421\u043e\u043a\u0440\u0430\u0449\u0435\u043d\u043e:\n        \/\/ \u0418\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0439\u0442\u0435 WaitLatch, \u043f\u043e\u043a\u0430 \u043d\u0435 \u0434\u043e\u0439\u0434\u0451\u043c \u0434\u043e recoveryDelayUntilTime\n        \/\/ \u0438 \u0437\u0430\u0442\u0435\u043c\n        break;\n    }\n    return true;\n}<\/code><\/pre>\n
<\/p>\n
\u0421\u0443\u0442\u044c \u0432 \u0442\u043e\u043c, \u0447\u0442\u043e \u0437\u0430\u0434\u0435\u0440\u0436\u043a\u0430 \u043e\u0441\u043d\u043e\u0432\u0430\u043d\u0430 \u043d\u0430 \u0444\u0438\u0437\u0438\u0447\u0435\u0441\u043a\u043e\u043c \u0432\u0440\u0435\u043c\u0435\u043d\u0438, \u0437\u0430\u043f\u0438\u0441\u0430\u043d\u043d\u043e\u043c \u0432 \u043c\u0435\u0442\u043a\u0435 \u0432\u0440\u0435\u043c\u0435\u043d\u0438 \u043a\u043e\u043c\u043c\u0438\u0442\u0430 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 (xtime<\/code>). \u041a\u0430\u043a \u0432\u0438\u0434\u043d\u043e, \u0437\u0430\u0434\u0435\u0440\u0436\u043a\u0430 \u043f\u0440\u0438\u043c\u0435\u043d\u044f\u0435\u0442\u0441\u044f \u0442\u043e\u043b\u044c\u043a\u043e \u043a \u043a\u043e\u043c\u043c\u0438\u0442\u0430\u043c \u0438 \u043d\u0435 \u0437\u0430\u0442\u0440\u0430\u0433\u0438\u0432\u0430\u0435\u0442 \u0434\u0440\u0443\u0433\u0438\u0435 \u0437\u0430\u043f\u0438\u0441\u0438 \u2014 \u0432\u0441\u0435 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u043f\u0440\u0438\u043c\u0435\u043d\u044f\u044e\u0442\u0441\u044f \u043d\u0430\u043f\u0440\u044f\u043c\u0443\u044e, \u0430 \u043a\u043e\u043c\u043c\u0438\u0442 \u043e\u0442\u043a\u043b\u0430\u0434\u044b\u0432\u0430\u0435\u0442\u0441\u044f, \u0442\u0430\u043a \u0447\u0442\u043e \u043c\u044b \u0443\u0432\u0438\u0434\u0438\u043c \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u0442\u043e\u043b\u044c\u043a\u043e \u043f\u043e\u0441\u043b\u0435 \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043d\u043d\u043e\u0439 \u0437\u0430\u0434\u0435\u0440\u0436\u043a\u0438.<\/p>\n
<\/p>\n
\u041a\u0430\u043a \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c \u043e\u0442\u043b\u043e\u0436\u0435\u043d\u043d\u0443\u044e \u0440\u0435\u043f\u043b\u0438\u043a\u0443 \u0434\u043b\u044f \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f \u0434\u0430\u043d\u043d\u044b\u0445<\/h3>\n
<\/p>\n
\u0414\u043e\u043f\u0443\u0441\u0442\u0438\u043c, \u0443 \u043d\u0430\u0441 \u0432 \u043f\u0440\u043e\u0434\u0430\u043a\u0448\u0435\u043d\u0435 \u0435\u0441\u0442\u044c \u043a\u043b\u0430\u0441\u0442\u0435\u0440 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u0440\u0435\u043f\u043b\u0438\u043a\u0430 \u0441 \u0432\u043e\u0441\u044c\u043c\u0438\u0447\u0430\u0441\u043e\u0432\u043e\u0439 \u0437\u0430\u0434\u0435\u0440\u0436\u043a\u043e\u0439. \u041f\u043e\u0441\u043c\u043e\u0442\u0440\u0438\u043c, \u043a\u0430\u043a \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0435 \u043d\u0430 \u043f\u0440\u0438\u043c\u0435\u0440\u0435 \u0441\u043b\u0443\u0447\u0430\u0439\u043d\u043e\u0433\u043e \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u044f \u044f\u0440\u043b\u044b\u043a\u043e\u0432<\/a><\/noindex>.<\/p>\n
<\/p>\n
\u041a\u043e\u0433\u0434\u0430 \u043c\u044b \u0443\u0437\u043d\u0430\u043b\u0438 \u043e \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u0435, \u043c\u044b \u043f\u0440\u0438\u043e\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u043b\u0438 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435 \u0438\u0437 \u0430\u0440\u0445\u0438\u0432\u0430<\/a><\/noindex> \u0434\u043b\u044f \u043e\u0442\u043b\u043e\u0436\u0435\u043d\u043d\u043e\u0439 \u0440\u0435\u043f\u043b\u0438\u043a\u0438:<\/p>\n
<\/p>\n
SELECT pg_xlog_replay_pause();<\/code><\/pre>\n
<\/p>\n
\u0421 \u043f\u0430\u0443\u0437\u043e\u0439 \u0443 \u043d\u0430\u0441 \u043d\u0435 \u0431\u044b\u043b\u043e \u0440\u0438\u0441\u043a\u0430, \u0447\u0442\u043e \u0440\u0435\u043f\u043b\u0438\u043a\u0430 \u043f\u043e\u0432\u0442\u043e\u0440\u0438\u0442 \u0437\u0430\u043f\u0440\u043e\u0441 DELETE<\/code>. \u041f\u043e\u043b\u0435\u0437\u043d\u0430\u044f \u0448\u0442\u0443\u043a\u0430, \u0435\u0441\u043b\u0438 \u043d\u0443\u0436\u043d\u043e \u0432\u0440\u0435\u043c\u044f \u0432\u043e \u0432\u0441\u0435\u043c \u0440\u0430\u0437\u043e\u0431\u0440\u0430\u0442\u044c\u0441\u044f.<\/p>\n
<\/p>\n
\u0421\u0443\u0442\u044c \u0432 \u0442\u043e\u043c, \u0447\u0442\u043e \u043e\u0442\u043b\u043e\u0436\u0435\u043d\u043d\u0430\u044f \u0440\u0435\u043f\u043b\u0438\u043a\u0430 \u0434\u043e\u043b\u0436\u043d\u0430 \u0434\u043e\u0439\u0442\u0438 \u0434\u043e \u043c\u043e\u043c\u0435\u043d\u0442\u0430 \u043f\u0435\u0440\u0435\u0434 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u043c DELETE<\/code>. \u041c\u044b \u043f\u0440\u0438\u043c\u0435\u0440\u043d\u043e \u0437\u043d\u0430\u043b\u0438 \u0444\u0438\u0437\u0438\u0447\u0435\u0441\u043a\u043e\u0435 \u0432\u0440\u0435\u043c\u044f \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u044f. \u041c\u044b \u0443\u0434\u0430\u043b\u0438\u043b\u0438 recovery_min_apply_delay<\/code> \u0438 \u0434\u043e\u0431\u0430\u0432\u0438\u043b\u0438 recovery_target_time<\/code> u recovery.conf<\/code>. \u0422\u0430\u043a \u0440\u0435\u043f\u043b\u0438\u043a\u0430 \u0434\u043e\u0445\u043e\u0434\u0438\u0442 \u0434\u043e \u043d\u0443\u0436\u043d\u043e\u0433\u043e \u043c\u043e\u043c\u0435\u043d\u0442\u0430 \u0431\u0435\u0437 \u0437\u0430\u0434\u0435\u0440\u0436\u0435\u043a:<\/p>\n
<\/p>\n
recovery_target_time = '2018-10-12 09:25:00+00'<\/code><\/pre>\n
<\/p>\n
\u0421 \u043c\u0435\u0442\u043a\u0430\u043c\u0438 \u0432\u0440\u0435\u043c\u0435\u043d\u0438 \u043b\u0443\u0447\u0448\u0435 \u0443\u0431\u0430\u0432\u0438\u0442\u044c \u043b\u0438\u0448\u043d\u0435\u0433\u043e, \u0447\u0442\u043e\u0431\u044b \u043d\u0435 \u043f\u0440\u043e\u043c\u0430\u0445\u043d\u0443\u0442\u044c\u0441\u044f. \u041f\u0440\u0430\u0432\u0434\u0430, \u0447\u0435\u043c \u0431\u043e\u043b\u044c\u0448\u0435 \u0443\u0431\u0430\u0432\u043a\u0430, \u0442\u0435\u043c \u0431\u043e\u043b\u044c\u0448\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 \u0442\u0435\u0440\u044f\u0435\u043c. \u041e\u043f\u044f\u0442\u044c \u0436\u0435, \u0435\u0441\u043b\u0438 \u043f\u0440\u043e\u0441\u043a\u043e\u0447\u0438\u043c \u0437\u0430\u043f\u0440\u043e\u0441 DELETE<\/code>, \u0432\u0441\u0435 \u043e\u043f\u044f\u0442\u044c \u0443\u0434\u0430\u043b\u0438\u0442\u0441\u044f \u0438 \u043f\u0440\u0438\u0434\u0435\u0442\u0441\u044f \u043d\u0430\u0447\u0438\u043d\u0430\u0442\u044c \u0437\u0430\u043d\u043e\u0432\u043e (\u0438\u043b\u0438 \u0432\u043e\u043e\u0431\u0449\u0435 \u0431\u0440\u0430\u0442\u044c \u0445\u043e\u043b\u043e\u0434\u043d\u044b\u0439 \u0431\u044d\u043a\u0430\u043f \u0434\u043b\u044f PITR).<\/p>\n
<\/p>\n
Ponovno smo pokrenuli odlo\u017eeni primjerak Postgresa, a WAL segmenti su se ponavljali do zadanog vremena. Napredak u ovoj fazi mo\u017ee se pratiti upitom:<\/p>\n
<\/p>\n
SELECT\n  -- trenutna lokacija u WAL\n  pg_last_xlog_replay_location(),\n  -- trenutni vremenski \u017eig transakcije (stanje replikata)\n  pg_last_xact_replay_timestamp(),\n  -- trenutna fizi\u010dka vremena\n  now(),\n  -- koli\u010dina vremena koja se jo\u0161 treba primijeniti do postizanja recovery_target_time\n  '2018-10-12 09:25:00+00'::timestamptz - pg_last_xact_replay_timestamp() as delay;<\/code><\/pre>\n
<\/p>\n
Ako se vremenski \u017eig vi\u0161e ne mijenja, obnova je zavr\u0161ena. Mo\u017ee se postaviti akcija recovery_target_action<\/code><\/a><\/noindex>, kako bi se zatvorio, unaprijedio ili obustavio primjerak nakon ponavljanja (zadano se obustavlja).<\/p>\n
<\/p>\n
Baza podataka je do\u0161la u stanje prije tog nesretnog upita. Sada mo\u017eemo, na primjer, izvesti podatke. Izveli smo izbrisane podatke o oznakama i sve veze s zadacima i mjeri zahtjevima te ih prenijeli u radnu bazu. Ako su gubici veliki, mo\u017eemo jednostavno unaprijediti replikat i koristiti ga kao glavni. No tada \u0107e se izgubiti sve promjene nakon trenutka do kojeg smo se obnovili.<\/p>\n
<\/p>\n
Umjesto vremenskih oznaka bolje je koristiti ID transakcija. Korisno je zabilje\u017eiti te ID-ove, na primjer, za DDL operatore (poput DROP TABLE<\/code>), pomo\u0107u log_statements = 'ddl'<\/code>. Da smo imali ID transakcije, uzeli bismo recovery_target_xid<\/code> i pokrenuli sve do transakcije prije upita DELETE<\/code>.<\/p>\n
<\/p>\n
Vrati se na posao vrlo lako: ukloni sve promjene iz recovery.conf<\/code> i ponovo pokreni Postgres. Ubrzo \u0107e se u replici ponovo pojaviti osmosatna ka\u0161njenja i spremni smo za budu\u0107e nevolje.<\/p>\n
<\/p>\n
Prednosti za obnovu<\/h3>\n
<\/p>\n
S odlo\u017eenom replikom umjesto hladnog backupa ne treba satima obnavljati cijeli snimak iz arhive. Na primjer, treba nam pet sati da dobijemo cijeli osnovni backup od 2 TB. A onda \u0107e jo\u0161 trebati primijeniti cijeli dnevni WAL kako bismo se obnovili do potrebnog stanja (u najgorem slu\u010daju).<\/p>\n
<\/p>\n
Odlo\u017eena replika je bolja od hladnog backupa iz dva razloga:<\/p>\n
<\/p>\n
    \n
  1. Nema potrebe izvla\u010diti cijeli osnovni backup iz arhive.<\/li>\n
  2. Postoji fiksno osmosatno okno WAL segmenata koje treba ponoviti.<\/li>\n<\/ol>\n
    <\/p>\n
    Osim toga, stalno provjeravamo mo\u017ee li se PITR napraviti iz WAL-a, i brzo bismo primijetili o\u0161te\u0107enja ili druge probleme s arhivom WAL, prate\u0107i ka\u0161njenje odlo\u017eene replikacije.<\/p>\n
    <\/p>\n
    U ovom primjeru potro\u0161eno je 50 minuta na obnovu, \u0161to zna\u010di da je brzina bila 110 GB podataka WAL po satu (arhiv je tada jo\u0161 uvijek bio na AWS S3<\/a><\/noindex>). Z uspehom smo re\u0161ili te\u017eavo in obnovili podatke v 1,5 uri.<\/p>\n
    <\/p>\n
    Zaklju\u010dki: kje je uporabna odlo\u017eena replikacija (in kje ne)<\/h3>\n
    <\/p>\n
    Uporabite odlo\u017eeno replikacijo kot sredstvo prve pomo\u010di, \u010de slu\u010dajno izgubite podatke in opazite to te\u017eavo v okviru nastavljene zamude.<\/p>\n
    <\/p>\n
    Vendar upo\u0161tevajte: replikacija ni varnostna kopija.<\/p><\/blockquote>\n
    Varnostne kopije in replikacije imata razli\u010dne cilje. Hladna varnostna kopija bo koristna, \u010de slu\u010dajno naredite DELETE<\/code> ili DROP TABLE<\/code>. Opravljamo varnostno kopijo iz hladnega shranjevanja in obnavljamo prej\u0161nje stanje tabele ali celotne baze podatkov. Ampak ob tem se poizvedba DROP TABLE<\/code> skoraj takoj reproducira na vseh replikah v delovnem klasterju, zato obi\u010dajna replikacija v tem primeru ne bo pomagala. Sama replikacija ohranja bazo podatkov dostopno, ko se oddajajo posamezni stre\u017eniki, in porazdeli obremenitev.<\/p>\n
    <\/p>\n
    Tudi z odlo\u017eeno replikacijo potrebujemo hladno varnostno kopijo na varnem mestu, \u010de pride do okvare podatkovnega centra, skritega po\u0161kodb ali drugih dogodkov, ki jih takoj ne opazite. Tukaj od same replikacije ni koristi.<\/p>\n
    <\/p>\n
    Napomena<\/strong>. Na GitLab.com<\/a><\/noindex> trenutno \u0161\u010ditimo pred izgubo podatkov le na ravni sistema in ne obnavljamo podatkov na ravni uporabnika.<\/p>\n
    Izvor: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
    \u0420\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044f \u2014 \u043d\u0435 \u0431\u044d\u043a\u0430\u043f. \u0418\u043b\u0438 \u043d\u0435\u0442? \u0412\u043e\u0442 \u043a\u0430\u043a \u043c\u044b \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043b\u0438 \u043e\u0442\u043b\u043e\u0436\u0435\u043d\u043d\u0443\u044e \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044e \u0434\u043b\u044f \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f, \u0441\u043b\u0443\u0447\u0430\u0439\u043d\u043e \u0443\u0434\u0430\u043b\u0438\u0432 \u044f\u0440\u043b\u044b\u043a\u0438. \u0421\u043f\u0435\u0446\u0438\u0430\u043b\u0438\u0441\u0442\u044b \u043f\u043e \u0438\u043d\u0444\u0440\u0430\u0441\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0435 \u043d\u0430 GitLab \u043e\u0442\u0432\u0435\u0447\u0430\u044e\u0442 \u0437\u0430 \u0440\u0430\u0431\u043e\u0442\u0443 GitLab.com \u2014 \u0441\u0430\u043c\u043e\u0433\u043e \u0431\u043e\u043b\u044c\u0448\u043e\u0433\u043e \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0430 GitLab \u0432 \u043f\u0440\u0438\u0440\u043e\u0434\u0435. \u0417\u0434\u0435\u0441\u044c 3 \u043c\u0438\u043b\u043b\u0438\u043e\u043d\u0430 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439 \u0438 \u043f\u043e\u0447\u0442\u0438 7 \u043c\u0438\u043b\u043b\u0438\u043e\u043d\u043e\u0432 \u043f\u0440\u043e\u0435\u043a\u0442\u043e\u0432, \u0438 \u044d\u0442\u043e \u043e\u0434\u0438\u043d \u0438\u0437 \u0441\u0430\u043c\u044b\u0445 \u043a\u0440\u0443\u043f\u043d\u044b\u0445 \u043e\u043f\u0435\u043d\u0441\u043e\u0440\u0441-\u0441\u0430\u0439\u0442\u043e\u0432 SaaS \u0441 \u0432\u044b\u0434\u0435\u043b\u0435\u043d\u043d\u043e\u0439 \u0430\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u0443\u0440\u043e\u0439. \u0411\u0435\u0437 \u0441\u0438\u0441\u0442\u0435\u043c\u044b […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":22360,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-30356","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Kako smo uporabili odlo\u017eeno replikacijo za nujno obnovitev s PostgreSQL | ProHoster","description":".","canonical_url":"https:\/\/prohoster.info\/sl\/blog\/administrirovanie\/kak-my-ispolzovali-otlozhennuyu-replikatsiyu-dlya-avarijnogo-vosstanovleniya-s-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sl_SI","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041a\u0430\u043a \u043c\u044b \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043b\u0438 \u043e\u0442\u043b\u043e\u0436\u0435\u043d\u043d\u0443\u044e \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044e \u0434\u043b\u044f \u0430\u0432\u0430\u0440\u0438\u0439\u043d\u043e\u0433\u043e \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f \u0441 PostgreSQL | ProHoster","og:description":".","og:url":"https:\/\/prohoster.info\/sl\/blog\/administrirovanie\/kak-my-ispolzovali-otlozhennuyu-replikatsiyu-dlya-avarijnogo-vosstanovleniya-s-postgresql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T18:35:06+00:00","article:modified_time":"2019-10-31T18:35:06+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"30356","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-21 00:50:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 11:48:29","updated":"2026-01-21 00:50:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/posts\/30356","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/comments?post=30356"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/posts\/30356\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/media\/22360"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/media?parent=30356"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/categories?post=30356"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/tags?post=30356"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}