ืจืขืคึผืืึทืงืืืฉืึทื ืืื ื ืืฉื ืืึทืงืึทืคึผ. ืืืขืจ ื ืืฉื? ืืึธ ืก ืืื ืืืจ ืืขืืืืื ื ืืืคืขืจื ืจืขืคึผืืึทืงืืืฉืึทื ืฆื ืฆืืจืืงืงืจืืื ืคืื ืึทืงืกืึทืืขื ืึทืื ืืืืืืื ื ืฉืึธืจืืงืึทืฅ.
ืจืขืคึผืืึทืงืืืฉืึทื ืืื ื ืืฉื ืึท ืืืื ืคืื ืืึทืงืื ื ืึทืจืืืฃ ืืึทืืึทืืืืกืื (gitlab-ce
ืืื ืึท ืืืคืขืจื ืจืขืคึผืืืงืข, ืืืจ ืจืืงืึทืืืขืจื ืืึทืื ืืื ืืืืื 1,5 ืฉืขื. ืงืืง ืืื ืขืก ืืขืืจืืคื.
ืฆืืื ืึธืคึผืืื ืืื PostgreSQL
PostgreSQL ืืื ืึท ืืขืืืื-ืืื ืคืื ืงืฆืืข ืืืึธืก ืจืืกืืึธืจื ืื ืฉืืึทื ืคืื ืึท ืืึทืืึทืืืืก ืฆื ืึท ืกืคึผืขืฆืืคืืฉ ืคืื ื ืืื ืฆืืื. ืขืก ืืื ืืขืจืืคื
ืฆื ื ืืฆื ืืขื ืฉืืจืื ืคึฟืึทืจ ืงืึทืื ืืึทืงืึทืคึผ, ืืืจ ืงืขืกืืืืขืจ ืืึทืื ืึท ืืงืขืจืืืง ืืึทืืึทืืืืก ืืึทืงืึทืคึผ ืืื ืงืจืึธื ืขืก ืืื ืึทื ืึทืจืงืืืื (ืืืืืึทื ืึทืจืงืืืืื ืืขืื ืืื
ืืืึธืก ืืื ืืืคืขืจื ืจืขืคึผืืึทืงืืืฉืึทื?
ืคืืื ืจืขืคึผืืึทืงืืืฉืึทื ืืื ืื ืึทืคึผืืึทืงืืืฉืึทื ืคืื ืขื ืืขืจืื ืืขื ืคึฟืื WAL ืืื ืึท ืคืึทืจืืึทืืื. ืึทื ืืื, ืื ืืึทืกืข ืืื ืคืืจืืขืงืืืขื ืืื ืึท ืฉืขื 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
) ืคึฟืื ืืขื ืึทืจืงืืืื, ืืื ืขื ืืขืจืื ืืขื ืืืขืื ืืืื ืืขืืืขื ืื ื ืึธื ืึทืื ืฉืขื (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
). ืืื ืืืจ ืงืขื ืขื ืืขื, ืื ืคืึทืจืืึทืืื ืึทืคึผืืืื ืืืืื ืฆื ืงืึทืืืฅ ืืื ืืื ื ืืฉื ืืืืจืงื ืื ืืขืจืข ืืืื ืกื - ืึทืืข ืขื ืืขืจืื ืืขื ืืขื ืขื ืืขืืืขื ืื ืืืืึทื, ืืื ืื ืงืึทืืื ืืื ืืืืืื, ืึทืืื ืืืจ ืืืขืื ืืืืื ืืขื ืื ืขื ืืขืจืื ืืขื ื ืึธื ืื ืงืึทื ืคืืืืขืจื ืคืึทืจืืึทืืื.
ืืื ืฆื ื ืืฆื ืึท ืืืืืื ืจืขืคึผืืืงืข ืฆื ืืืงืขืจื ืืึทืื
ืืื ืก ืืึธืื ืืืจ ืืึธืื ืึท ืืึทืืึทืืืืก ืงื ืืื ืืื ืึท ืจืขืคึผืืืงืข ืืื ืึทื ืึทืื-ืฉืขื ืคืึทืจืืึทืืื ืืื ืคึผืจืึธืืืงืฆืืข. ืืื ืก ืืขื ืืื ืฆื ืฆืืจืืงืงืจืืื ืืึทืื ืืื ืึท ืืืึทืฉืคึผืื
ืืืขื ืืืจ ืืขืืขืจื ื ืืืขืื ืืขื ืคึผืจืึธืืืขื, ืืืจ
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
ืื ืืึทืืึทืืืืก ืืึธื ืืื ืืืืืขืงืขืจื ืฆื ืืืื ืฉืืึทื ืืืืืขืจ ืื ื ืขืืขื ืืขืื. ืืืฆื ืืืจ ืงืขื ืขื, ืืืฉื, ืึทืจืืืกืคืืจื ืืึทืื. ืืืจ ืืงืกืคึผืึธืจืืึทื ืื ืืืืกืืขืืขืงื ืคืืจืืข ืืึทืื ืืื ืึทืืข ืคึฟืึทืจืืื ืืื ืืขื ืฆื ืืฉืื ืืื ืฆืื ืืืคืืืกื ืจืืงืืืขืก ืืื ืืจืืืขืจืืขืคืืจื ืืื ืืื ืื ืคึผืจืึธืืืงืฆืืข ืืึทืืึทืืืืก. ืืืื ืื ืืึธืกืกืขืก ืืขื ืขื ืืจืืืก, ืืืจ ืงืขื ืขื ืคืฉืื ืืขืืขืจื ืื ืจืขืคึผืืืงืข ืืื ื ืืฆื ืขืก ืืื ืื ืืืืคึผื. ืึธืืขืจ ืืขืืึธืื ืึทืืข ืขื ืืขืจืื ืืขื ื ืึธื ืื ืคืื ื ืฆื ืืืึธืก ืืืจ ืืึธืื ืจืืงืึทืืืขืจื ืืืขื ืืืื ืคืึทืจืคืึทืื.
ืึทื ืฉืืึธื ืคืื ืืืืขืกืืึทืืคึผืก, ืขืก ืืื ืืขืกืขืจ ืฆื ื ืืฆื ืืจืึทื ืกืึทืงืืืึธื ืืืก. ืขืก ืืื ื ืืฆืืง ืฆื ืจืขืงืึธืจืืืจื ืื IDs, ืืืฉื, ืคึฟืึทืจ DDL ืกืืืืืืึทื ืฅ (ืึทืืึท ืืื DROP TABLE
), ืืืจื ื ืืฆื log_statements = 'ddl'
. ืืืื ืืืจ ืืึธืื ืึท ืืจืึทื ืกืึทืงืืืึธื ืฉืืึทื, ืืืจ ืืืึธืื ื ืขืืขื recovery_target_xid
ืืื ืืขืืืคื ืึทืืฅ ืึทืจืึธืคึผ ืฆื ืื ืืจืึทื ืกืึทืงืืืึธื ืืืืืขืจ ืื ืืงืฉื DELETE
.
ืฆืืจืืงืงืืืขื ืฆื ืึทืจืืขืื ืืื ืืืืขืจ ืคึผืฉืื: ืึทืจืึธืคึผื ืขืืขื ืึทืืข ืขื ืืขืจืื ืืขื ืคืื recovery.conf
ืืื ืจืืกืืึทืจื Postgres. ืื ืจืขืคึผืืืงืข ืืืขื ืืึทืื ืืึธืื ืึทื ืึทืื-ืฉืขื ืคืึทืจืืึทืืื ืืืืืขืจ, ืืื ืืืจ ืืขื ืขื ืฆืืืขืืจืืื ืคึฟืึทืจ ืฆืืงืื ืคึฟื ืงืึธืคึผืืจืืืขื ืืฉ.
ืจืขืงืึธืืืขืจื Benefits
ืืื ืึท ืืืคืขืจื ืจืขืคึผืืืงืข ืึทื ืฉืืึธื ืคืื ืึท ืงืึทืื ืืึทืงืึทืคึผ, ืืืจ ืืึธื ื ืื ืืึธืื ืฆื ืคืึทืจืืจืขื ืืขื ืฉืขื ืฆื ืจืืกืืึธืจืื ื ืื ืืื ืฆืข ืืืื ืคึฟืื ืืขืจ ืึทืจืงืืืื. ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืขืก ื ืขืื ืืื ืื ืคืื ืฃ ืฉืขื ืฆื ืืึทืงืืืขื ืื ืืื ืฆืข ืืงืขืจืืืง 2 ืื ืืึทืงืึทืคึผ. ืืื ืืืจ ื ืึธื ืืึธืื ืฆื ืฆืืืืืื ืื ืืื ืฆืข ืืขืืืขื WAL ืฆื ืฆืืจืืงืงืจืืื ืฆื ืืขืจ ืืขืืืืื ืฉืืึทื (ืืื ืื ืขืจืืกื ืคืึทื).
ื ืืืคืขืจื ืจืขืคึผืืืงืข ืืื ืืขืกืขืจ ืืื ืึท ืงืึทืื ืืึทืงืึทืคึผ ืืื ืฆืืืื ืืืขืื:
- ืขืก ืืื ื ืื ืืึทืจืคึฟื ืฆื ืืึทืืืึทืืืงื ืื ืืื ืฆืข ืืงืขืจืืืง ืืึทืงืึทืคึผ ืคืื ืื ืึทืจืงืืืื.
- ืขืก ืืื ืึท ืคืึทืจืคืขืกืืืงื ืึทืื-ืฉืขื ืคึฟืขื ืฆืืขืจ ืคืื WAL ืกืขืืืึทื ืฅ ืืืึธืก ืืืื ืืืื ืจืืคึผืืืื.
ืืืจ ืืืื ืงืึธื ืืจืึธืืืจื ืงืขืกืืืืขืจ ืฆื ืืขื ืืืื ืขืก ืืื ืืขืืืขื ืฆื ืืึทืื ืึท PITR ืคึฟืื WAL, ืืื ืืืจ ืืืึธืื ืืขืฉืืืื ื ืืึทืืขืจืงื ืงืึธืจืืคึผืฆืืข ืึธืืขืจ ืื ืืขืจืข ืคึผืจืึธืืืขืืก ืืื ืื WAL ืึทืจืงืืืื ืืืจื ืืึธื ืืืึธืจืื ื ืื ืึธืคึผืฉืืื ืคืื ืื ืืืคืขืจื ืจืขืคึผืืืงืข.
ืืื ืืขื ืืืึทืฉืคึผืื, ืขืก ืืึธื ืืื ืื 50 ืืื ืื ืฆื ืืืงืขืจื, ืืืึทืืฉ ืื ืืืืงืืึทื ืืื ืืขืืืขื 110 ืืืืืืืื ืคืื WAL ืืึทืื ืคึผืขืจ ืฉืขื (ืืขืจ ืึทืจืงืืืื ืืื ื ืึธื ืืืืฃ
ืจืขืืืืืึทืื: ืืื ืึท ืืืคืขืจื ืจืขืคึผืืืงืข ืืื ื ืืฆืืง (ืืื ืืื ืขืก ืืื ื ืืฉื)
ื ืืฆื ืืืืืื ืจืขืคึผืืึทืงืืืฉืึทื ืืื ืึท ืขืจืฉืืขืจ ืืืืฃ ืืืื ืืืจ ืึทืงืกืึทืืขื ืึทืื ืคืึทืจืคืึทืื ืืึทืื ืืื ืืืืขืจืงื ืืขื ืคึผืจืึธืืืขื ืืื ืื ืงืึทื ืคืืืืขืจื ืคืึทืจืืึทืืื.
ืึธืืขืจ ืืึทืืื ืืื ืืืื ืื ื: ืจืขืคึผืืึทืงืืืฉืึทื ืืื ื ืืฉื ืึท ืืึทืงืึทืคึผ.
ืืึทืงืงืืคึผ ืืื ืจืขืคึผืืึทืงืืืฉืึทื ืืึธืื ืคืึทืจืฉืืืขื ืข ืฆืืืขืงื. ื ืงืึทืื ืืึทืงืึทืคึผ ืืืขื ืงืืืขื ืืื ืืึทื ืืืง ืืืื ืืืจ ืึทืงืกืึทืืขื ืึทืื ืืขืืืื DELETE
ืึธืืขืจ DROP TABLE
. ืืืจ ืืึทืื ืึท ืืึทืงืึทืคึผ ืคืื ืงืึทืื ืกืืึธืจืืืืฉ ืืื ืืืงืขืจื ืื ืคืจืืขืจืืืงืข ืฉืืึทื ืคืื ืื ืืืฉ ืึธืืขืจ ืื ืืื ืฆืข ืืึทืืึทืืืืก. ืืืขืจ ืืื ืืขืจ ืืขืืืืงืขืจ ืฆืืึทื ืื ืืงืฉื DROP TABLE
ืืื ืึผืืขื ืืืืงืขืฃ ืจืืคึผืจืึทืืืกื ืืื ืึทืืข ืจืขืคึผืืึทืงืึทื ืืืืฃ ืื ืืจืืขืื ืงื ืืื, ืึทืืื ืคึผืจืึธืกื ืจืขืคึผืืึทืงืืืฉืึทื ืืืขื ื ืืฉื ืืขืืคื ืืึธ. ืจืขืคึผืืึทืงืืืฉืึทื ืืื ืืืื ืื ืืึทืืึทืืืืก ืื ืืืฆื ืืืขื ืืืื ืกืขืจืืืขืจืก ืืขื ืขื ืจืขื ืืึทื ืืื ืืืกืืจืืืืืฅ ืื ืืึทืกืข.
ืืคืืื ืืื ืึท ืืืคืขืจื ืจืขืคึผืืืงืข, ืืืจ ืืื ืืึทืงืข ืืึทืจืคึฟื ืึท ืงืึทืื ืืึทืงืึทืคึผ ืืื ืึท ืืืืขืจ ืคึผืืึทืฅ ืืืื ืึท ืืึทืื ืฆืขื ืืขืจ ืืืจืืคืึทื, ืคืึทืจืืึธืจืื ืฉืขืืืงื ืึธืืขืจ ืื ืืขืจืข ืืขืฉืขืขื ืืฉื ืืืึธืก ืืขื ืขื ื ืืฉื ืืืืื ืืืืขืจืงื. ืจืขืคึผืืึทืงืืืฉืึทื ืึทืืืื ืืื ืคืื ืงืืื ื ืืฆื ืืึธ.
ืืึธื. ืืืืฃ
ืืงืืจ: www.habr.com