ã¬ããªã±ãŒã·ã§ã³ã¯ããã¯ã¢ããã§ã¯ãããŸããã ãåŠãïŒ ããã§ã¯ãé
延ã¬ããªã±ãŒã·ã§ã³ã䜿çšããŠã·ã§ãŒãã«ããã誀ã£ãŠåé€ããå Žåã«å埩ããæ¹æ³ã説æããŸãã
ã¬ããªã±ãŒã·ã§ã³ã¯ããŒã¿ããŒã¹ãããã¯ã¢ããããæ段ã§ã¯ãããŸãã (gitlab-ce
é 延ã¬ããªã«ã䜿çšãããšãããã 1,5 æéã§ããŒã¿ã埩å ã§ããŸããã ãããã©ã®ããã«èµ·ãã£ãããèŠãŠãã ããã
PostgreSQL ã䜿çšãããã€ã³ãã€ã³ã¿ã€ã ãªã«ããª
PostgreSQL ã«ã¯ãããŒã¿ããŒã¹ã®ç¶æ
ãç¹å®ã®æç¹ã«åŸ©å
ããçµã¿èŸŒã¿é¢æ°ããããŸãã ãããã
ãã®æ©èœãã³ãŒã«ã ããã¯ã¢ããã«äœ¿çšããããã«ãåºæ¬çãªããŒã¿ããŒã¹ ããã¯ã¢ãããå®æçã«äœæããã¢ãŒã«ã€ãã«ä¿åããŸã (GitLab ã¢ãŒã«ã€ãã¯
é 延ã¬ããªã±ãŒã·ã§ã³ãšã¯äœã§ãã?
é
延ã¬ããªã±ãŒã·ã§ã³ã¯ãWAL ããã®å€æŽãé
延ããŠé©çšããŸãã ã€ãŸãããã©ã³ã¶ã¯ã·ã§ã³ã¯ XNUMX æé以å
ã«çºçããŸãã X
ãã ããé
延ããŠã¬ããªã«ã«è¡šç€ºãããŸã d
æéå
ã« X + d
.
PostgreSQL ã«ã¯ãããã¯ã¢ãã ãªã«ããªãšã¹ããªãŒãã³ã° ã¬ããªã±ãŒã·ã§ã³ãšãã 2 ã€ã®æ¹æ³ã§ç©çããŒã¿ããŒã¹ ã¬ããªã«ãèšå®ã§ããŸãã
ã¢ãŒã«ã€ãããã®é 延ãªã«ããªãèšå®ããæ¹æ³
recovery.conf
ã äŸïŒ
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'
ãããã®ãã©ã¡ãŒã¿ã䜿çšããŠãããã¯ã¢ãã ãªã«ããªãåããé
延ã¬ããªã«ãæ§æããŸããã ããã§äœ¿ãããŠããã®ã¯ restore_command
) ãã¢ãŒã«ã€ãããååŸããå€æŽã¯ XNUMX æéåŸã«é©çšãããŸã (recovery_min_apply_delay
ïŒã ã¬ããªã«ã¯ãã¯ã©ã¹ã¿ãŒã®ãã§ã€ã«ãªãŒããŒãªã©ã«ããã¢ãŒã«ã€ãå
ã®ã¿ã€ã ã©ã€ã³ã®å€æŽãç£èŠããŸã (recovery_target_timeline
).
С recovery_min_apply_delay
é
延ã䌎ãã¹ããªãŒãã³ã° ã¬ããªã±ãŒã·ã§ã³ãèšå®ããããšãã§ããŸãããã¬ããªã±ãŒã·ã§ã³ ã¹ããããããã ã¹ã¿ã³ã〠ãã£ãŒãããã¯ãªã©ã«é¢é£ããèœãšãç©Žãããã€ããããŸãã WAL ã¢ãŒã«ã€ãã䜿çšãããšãããããåé¿ã§ããŸãã
ãã©ã¡ãŒã¿ãŒ recovery_min_apply_delay
PostgreSQL 9.3 ã§ã®ã¿ç»å ŽããŸããã 以åã®ããŒãžã§ã³ã§ã¯ãé
延ã¬ããªã±ãŒã·ã§ã³ã®å Žåãçµã¿åãããæ§æããå¿
èŠããããŸããã pg_xlog_replay_pause(), pg_xlog_replay_resume()
) ãŸãã¯ãé
延ã®éãWAL ã»ã°ã¡ã³ããã¢ãŒã«ã€ãã«ä¿æããŸãã
PostgreSQL ã¯ã©ã®ããã«ããŠãããè¡ãã®ã§ãããã?
PostgreSQL ãé
延ãªã«ããªãã©ã®ããã«å®è£
ããã®ããèŠãã®ã¯èå³æ·±ãã§ãã ãèŠããã 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;
}
èŠããã«ãé
延ã¯ãã©ã³ã¶ã¯ã·ã§ã³ã®ã³ããã ã¿ã€ã ã¹ã¿ã³ãã«èšé²ãããç©çæéã«åºã¥ããŠãããšããããšã§ã (xtime
ïŒã ã芧ã®ãšãããé
延ã¯ã³ãããã«ã®ã¿é©çšãããä»ã®ãšã³ããªã«ã¯åœ±é¿ããŸããããã¹ãŠã®å€æŽã¯çŽæ¥é©çšãããã³ãããã¯é
延ããããããæ§æãããé
延ã®åŸã«ã®ã¿å€æŽã衚瀺ãããŸãã
é 延ã¬ããªã«ã䜿çšããŠããŒã¿ã埩å ããæ¹æ³
æ¬çªç°å¢ã§ XNUMX æéã®é
延ãããããŒã¿ããŒã¹ ã¯ã©ã¹ã¿ãŒãšã¬ããªã«ããããšããŸãã äŸã䜿çšããŠããŒã¿ãå埩ããæ¹æ³ãèŠãŠã¿ãŸããã
ãã®åé¡ã«ã€ããŠç¥ã£ããšããç§ãã¡ã¯ã
SELECT pg_xlog_replay_pause();
äžæåæ¢ãããšãã¬ããªã«ããªã¯ãšã¹ããç¹°ãè¿ããªã¹ã¯ã¯ãªããªããŸããã DELETE
ã ãã¹ãŠãç解ããã®ã«æéãå¿
èŠãªå Žåã«äŸ¿å©ã§ãã
éèŠãªã®ã¯ãé
延ã¬ããªã«ããªã¯ãšã¹ãã®çŽåã«å°éããå¿
èŠããããšããããšã§ãã DELETE
ã ç©ççã«é€å»ãããæå»ã¯ã»ãŒããã£ãŠããŸããã åé€ããŸãã recovery_min_apply_delay
ãããŠè¿œå ããŸãã recovery_target_time
в recovery.conf
ã ããã¯ãã¬ããªã«ãé
延ãªãé©åãªç¬éã«å°éããæ¹æ³ã§ãã
recovery_target_time = '2018-10-12 09:25:00+00'
ã¿ã€ã ã¹ã¿ã³ãã䜿çšããå Žåã¯ãèŠéããªãããã«äœåãªå€ãæžããæ¹ãè¯ãã§ãããã 確ãã«ãæžå°ã倧ãããã°å€§ããã»ã©ãããå€ãã®ããŒã¿ã倱ãããŸãã ç¹°ãè¿ããŸããããªã¯ãšã¹ããéããå Žåã¯ã DELETE
ããã¹ãŠãå床åé€ããããããæåããããçŽãå¿
èŠããããŸãïŒãŸãã¯ãPITR çšã®ã³ãŒã«ã ããã¯ã¢ãããäœæããããšãã§ããŸãïŒã
é 延ããã Postgres ã€ã³ã¹ã¿ã³ã¹ãåèµ·åãããšãæå®ãããæéãŸã§ WAL ã»ã°ã¡ã³ããç¹°ãè¿ãããŸããã ãã®æ®µéã§ã®é²è¡ç¶æ³ã远跡ããã«ã¯ã次ã®ããã«è³ªåããŸãã
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;
ã¿ã€ã ã¹ã¿ã³ããå€æŽãããªããªã£ããããªã«ããªã¯å®äºã§ãã ã¢ã¯ã·ã§ã³ã¯ã«ã¹ã¿ãã€ãºå¯èœ recovery_target_action
ããŒã¿ããŒã¹ã¯ããã®æ®å¿µãªãªã¯ãšã¹ãã®åã®ç¶æ ã«æ»ããŸããã ããã§ãããšãã°ããŒã¿ããšã¯ã¹ããŒãã§ããããã«ãªããŸãã åé€ãããã©ãã« ããŒã¿ãšåé¡ãžã®ãã¹ãŠã®ãªã³ã¯ãããã³ããŒãž ãªã¯ãšã¹ãããšã¯ã¹ããŒãããéçšããŒã¿ããŒã¹ã«ç§»åããŸããã æ倱ã倧èŠæš¡ãªå Žåã¯ãã¬ããªã«ãææ ŒãããŠã¡ã€ã³ãšããŠäœ¿çšã§ããŸãã ããããå埩ããæç¹ä»¥éã®ãã¹ãŠã®å€æŽã¯å€±ãããŸãã
ã¿ã€ã ã¹ã¿ã³ãã®ä»£ããã«ããã©ã³ã¶ã¯ã·ã§ã³ ID ã䜿çšããããšããå§ãããŸãã ããšãã°ãDDL ã¹ããŒãã¡ã³ã ( DROP TABLE
)ãã䜿çšã㊠log_statements = 'ddl'
ã ãã©ã³ã¶ã¯ã·ã§ã³ ID ãããã°ã recovery_target_xid
ãªã¯ãšã¹ãã®åã«ãã©ã³ã¶ã¯ã·ã§ã³ãŸã§ãã¹ãŠãå®è¡ããŸãã DELETE
.
äœæ¥ã«æ»ãã®ã¯éåžžã«ç°¡åã§ãããã¹ãŠã®å€æŽãåé€ããŸãã recovery.conf
ãããŠPostgresãåèµ·åããŸãã ã¬ããªã«ã¯ãŸããªãåã³ XNUMX æéã®é
延ãçºçããäºå®ã§ãããä»åŸã®ãã©ãã«ã«åããŠããŸãã
å埩絊ä»é
ã³ãŒã«ã ããã¯ã¢ããã®ä»£ããã«é 延ã¬ããªã«ã䜿çšãããšãã¢ãŒã«ã€ãããã€ã¡ãŒãžå šäœã埩å ããã®ã«äœæéãè²»ããå¿ èŠããªããªããŸãã ããšãã°ãåºæ¬ç㪠2 TB ã®ããã¯ã¢ããå šäœãååŸããã«ã¯ XNUMX æéããããŸãã ãããŠãïŒææªã®å ŽåïŒæãŸããç¶æ ã«å埩ããã«ã¯ãæ¥æ¬¡ WAL å šäœãé©çšããå¿ èŠããããŸãã
é 延ã¬ããªã«ã¯ã次㮠XNUMX ã€ã®ç¹ã§ã³ãŒã«ã ããã¯ã¢ãããããåªããŠããŸãã
- åºæ¬ããã¯ã¢ããå šäœãã¢ãŒã«ã€ãããåé€ããå¿ èŠã¯ãããŸããã
- WAL ã»ã°ã¡ã³ãã«ã¯ãç¹°ãè¿ãå¿ èŠããã XNUMX æéã®åºå®ãŠã£ã³ããŠããããŸãã
ãŸããWAL ãã PITR ãäœæã§ãããã©ãããåžžã«ãã§ãã¯ããŠãããé 延ã¬ããªã«ã®é 延ãç£èŠããããšã§ãWAL ã¢ãŒã«ã€ãã®ç Žæããã®ä»ã®åé¡ã«ããã«æ°ã¥ããŸãã
ãã®äŸã§ã¯ã埩å
ã« 50 åããããŸãããã€ãŸããé床㯠110 æéããã XNUMX GB ã® WAL ããŒã¿ã§ãã (ã¢ãŒã«ã€ãã¯ãŸã ãªã³ã§ãã)
çµæ: é 延ã¬ããªã«ã圹ç«ã€å Žåãšããã§ãªãå Žå
誀ã£ãŠããŒã¿ã倱ããèšå®ãããé 延æéå ã«ãã®åé¡ã«æ°ã¥ããå Žåã¯ãå¿æ¥åŠçœ®ãšããŠé 延ã¬ããªã±ãŒã·ã§ã³ã䜿çšããŠãã ããã
ãã ããã¬ããªã±ãŒã·ã§ã³ã¯ããã¯ã¢ããã§ã¯ãªãããšã«æ³šæããŠãã ããã
ããã¯ã¢ãããšã¬ããªã±ãŒã·ã§ã³ã«ã¯ç°ãªãç®çããããŸãã ã³ãŒã«ã ããã¯ã¢ããã¯ã誀ã£ãŠäœæããå Žåã«åœ¹ç«ã¡ãŸãã DELETE
ãŸã㯠DROP TABLE
ã ã³ãŒã«ã ã¹ãã¬ãŒãžããããã¯ã¢ãããäœæããããŒãã«ãŸãã¯ããŒã¿ããŒã¹å
šäœã®ä»¥åã®ç¶æ
ã埩å
ããŸãã ãããåæã«ãªã¯ãšã¹ã㯠DROP TABLE
ã¯ãäœæ¥äžã®ã¯ã©ã¹ã¿ãŒäžã®ãã¹ãŠã®ã¬ããªã«ã«ã»ãŒå³åº§ã«è€è£œããããããããã§ã¯éåžžã®ã¬ããªã±ãŒã·ã§ã³ã¯åœ¹ã«ç«ã¡ãŸããã ã¬ããªã±ãŒã·ã§ã³èªäœã¯ãåã
ã®ãµãŒããŒãã¬ã³ã¿ã«ãããŠãããšãã«ããŒã¿ããŒã¹ãå©çšå¯èœãªç¶æ
ã«ä¿ã¡ãè² è·ãåæ£ããŸãã
é 延ã¬ããªã«ã䜿çšããå Žåã§ããããŒã¿ã»ã³ã¿ãŒã®é害ãé ããæå·ããŸãã¯ããã«ã¯æ°ä»ããªããã®ä»ã®ã€ãã³ããçºçããå Žåãå®å šãªå Žæã«ã³ãŒã«ã ããã¯ã¢ãããå¿ èŠã«ãªãå ŽåããããŸãã ããã§ã¯ã¬ããªã±ãŒã·ã§ã³ã ãã§ã¯åœ¹ã«ç«ã¡ãŸããã
泚æã äžã®
åºæïŒ habr.com