Ehara te tāruarua i te tārua. Kaore ranei? Anei te huarahi i whakamahia e matou te tukuruatanga whakaroa ki te whakaora mai i te whakakore ohorere i nga pokatata.
Ko te tukurua ehara i te huarahi ki te tautoko i nga papaunga raraunga (gitlab-ce
Na te tauira kua whakaroa, kua ora ake nga raraunga i roto noa i te 1,5 haora. Titiro pehea te tupu.
Tohu i te wa whakaora me te PostgreSQL
Ko te PostgreSQL he mahi hanga-i roto i te whakahoki i te ahua o te papaarangi ki tetahi waahi motuhake i te waa. Ka kiia
Hei whakamahi i tenei ahuatanga mo te taapiri makariri, he rite tonu taatau ki te hanga i tetahi putunga papaa raraunga taketake me te penapena ki roto i te puranga (Kei te noho nga puranga a GitLab
He aha te tukurua whakaroa?
Ko te tukurua mangere ko te tono huringa mai i te WAL me te whakaroa. Arā, i puta te tauwhitinga i roto i te hāora X
, engari ka puta ki te tauira me te whakaroa d
i roto i te haora X + d
.
E 2 nga huarahi a PostgreSQL ki te whakarite i tetahi tauira papaunga raraunga tinana: te whakaora whakaora me te tukurua rerenga.
Me pehea te whakarite i te whakaora whakaroa mai i te puranga
recovery.conf
. He tauira:
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'
Na enei tawhā, i whirihorahia e matou he tauira whakaroa me te whakaora whakaora. I konei ka whakamahia restore_command
) mai i te pūranga, ka whakamahia nga huringa i muri i te waru haora (recovery_min_apply_delay
). Ka tirohia e te tauira nga huringa o te raarangi i roto i te puranga, hei tauira na te ngoikore o te tautau (recovery_target_timeline
).
С recovery_min_apply_delay
Ka taea e koe te whakarite i te tukuruatanga roma me te whakaroa, engari he rua nga mahanga kei konei e pa ana ki nga mokamoka tukurua, nga urupare wera wera, me era atu. Ko te puranga WAL ka taea e koe te karo i a raatau.
Taumahi recovery_min_apply_delay
i puta anake i PostgreSQL 9.3. I roto i nga putanga o mua, mo te tukurua whakaroa me whirihora i te huinga pg_xlog_replay_pause(), pg_xlog_replay_resume()
) pupuri ranei i nga wahanga WAL i roto i te puranga mo te roanga o te whakaroa.
Me pehea te mahi a PostgreSQL?
He rawe ki te kite me pehea te whakatinana a PostgreSQL i te whakaora mangere. Kia titiro tatou ki recoveryApplyDelay(XlogReaderState)
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;
}
Ko te rarangi o raro ko te whakaroa i runga i te wa tinana kua tuhia ki te tohu wa tuku tauwhitinga (xtime
). Ka taea e koe te kite, ko te whakaroa e pa ana ki nga commits karekau e pa ki etahi atu whakaurunga - ka tukuna tika nga huringa katoa, ka roa te tuku, no reira ka kite noa matou i nga huringa i muri i te whakaroa kua whirihorahia.
Me pehea te whakamahi i te tauira kua roa ki te whakaora raraunga
Me kii he kahui putunga korero me tetahi tauira e waru haora te roa o te mahi. Kia kite tatou me pehea te whakaora raraunga ma te whakamahi i tetahi tauira
I te wa i ako ai matou mo te raruraru, ko matou
SELECT pg_xlog_replay_pause();
Ma te okioki, kaore matou i tupono ka tukuna ano e te tauira te tono DELETE
. He mea whai hua mena ka hiahia koe ki te wa ki te whakaaro i nga mea katoa.
Ko te mea nui me tae te tauira whakaroa i mua i te tono DELETE
. I mohio matou ki te wa tinana o te tangohanga. Kua mukua e matou recovery_min_apply_delay
me te tapiri recovery_target_time
в recovery.conf
. Koinei te huarahi e tae ai te tauira ki te wa tika me te kore whakaroa:
recovery_target_time = '2018-10-12 09:25:00+00'
Ma nga tohu wa, he pai ake te whakaiti i te taapiri kia kore ai e ngaro. Pono, ka nui ake te heke, ka nui ake nga raraunga ka ngaro. Ano, ki te ngaro tatou i te tono DELETE
, ka mukua ano nga mea katoa, me timata ano koe (me tango ranei i te taapiri makariri mo te PITR).
I timata ano matou i te tauira Postgres kua whakaroa, ka tukuruatia nga wahanga WAL tae noa ki te wa kua tohua. Ka taea e koe te whai i te ahunga whakamua i tenei wahanga ma te patai:
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;
Ki te kore te tohu wa e huri, kua oti te whakaora. Ka taea te whakarite mahi recovery_target_action
I hoki mai te putunga raraunga ki tona ahuatanga i mua i tera tono kino. Inaianei ka taea e koe, hei tauira, te kaweake raraunga. I kaweake e matou nga raraunga tapanga kua mukua me nga hononga katoa ki nga take me nga tono hanumi ka nukuhia ki roto i te paataka whakaputa. Mena he nui nga mate, ka taea e koe te whakatairanga noa i te tauira me te whakamahi hei mea matua. Engari ka ngaro nga huringa katoa i muri i te waahi i ora ai matou.
Engari i nga tohu wa, he pai ake te whakamahi i nga ID tauwhitinga. He pai ki te tuhi i enei ID, hei tauira, mo nga korero DDL (penei i te DROP TABLE
), ma te whakamahi log_statements = 'ddl'
. Mena he ID tauwhitinga ta matou, ka tangohia e matou recovery_target_xid
ka rere nga mea katoa ki raro ki te tauwhitinga i mua i te tono DELETE
.
He tino ngawari te hoki ki te mahi: tangohia nga huringa katoa mai recovery.conf
ka whakaara ano i a Postgres. E waru haora te roa o te tauira ka roa te roa, a kua rite matou mo nga raru kei te heke mai.
Nga Painga Whakaora
Ma te tauira kua whakaroahia hei utu mo te taapiri makariri, kaore koe e whakapau haora ki te whakahoki i te ahua katoa mai i te purongo. Hei tauira, e rima haora te roa ki te tiki i te taapiri 2 TB taketake katoa. Na ka whai tonu koe ki te tono i te WAL katoa o ia ra kia ora ai ki te ahua e hiahiatia ana (i roto i nga keehi kino rawa atu).
He pai ake te tauira kua whakaroa i te kape makariri i roto i nga huarahi e rua:
- Kaore he take ki te tango i te taapiri taketake katoa mai i te puranga.
- He matapihi mo te waru haora te roa o nga wahanga WAL me whakahoki ano.
Ka tirotirohia e matou i nga wa katoa ka taea te hanga PITR mai i te WAL, a ka tere ka kite matou i te pirau, i etahi atu raru ranei o te puranga WAL ma te aro turuki i te takamuri o te tauira whakaroa.
I tenei tauira, he 50 meneti te roa ki te whakaora, ko te tikanga ko te tere ko te 110 GB o nga raraunga WAL ia haora (kei runga tonu te puranga
Hua: kei te whai hua te tauira whakaroa (me nga waahi kaore)
Whakamahia te tukuruatanga kua roa hei awhina tuatahi mena kua ngaro koe i nga raraunga ka kite koe i tenei raru i roto i te whakaroa kua whirihorahia.
Engari kia maumahara: ehara te tāruarua i te tārua.
He rereke nga kaupapa o te taapiri me te tukurua. Ka puta mai he taapiri makariri ki te pohehe koe DELETE
ranei DROP TABLE
. Ka hangahia e matou he taapiri mai i te rokiroki makariri me te whakahoki i te ahua o mua o te tepu, te katoa o te paataka raraunga. Engari i te wa ano ko te tono DROP TABLE
tata tonu te whakaputa i roto i nga tauira katoa i runga i te kahui mahi, no reira karekau te tukurua noa e awhina i konei. Ko te tāruarua tonu e noho wātea ana te pātengi raraunga ina ka riihitia ngā tūmau takitahi me te tohatoha i te kawenga.
Ahakoa he tauira whakaroa, i etahi wa ka tino hiahia taatau ki te penapena makariri ki tetahi waahi haumaru mena ka rahua te pokapu raraunga, te kino huna, me etahi atu huihuinga kaore i te kitea tonutia. Ko te tāruarua anake karekau he painga i konei.
parau. Kei runga
Source: will.com