ααΆαα
αααααα·ααααααΆααΆαααααα»ααα»αααα α¬βαα·αβααα? αααααΆαααααααααΎαααΆαααααΎααΆαα
αααααααααΆααααααΆαααααΎααααΈααααααααααΈααΆααα»αααααΌαααΆαααααα
αααααα
ααΆαα
αααααα·ααααααΆαααααααΆαααααΆαααααα»ααα»αααΌαααααΆααα·αααααα (gitlab-ce
ααΆαα½αααΉαααΆαα αααααααααΆααααααΆαααα ααΎαααΆαααΆααααα·ααααααα‘αΎααα·ααααα»αααααααααααΉααα 1,5 ααααα ααΎαααΆααΎααΆααΎαα‘αΎααααΆαααΌα ααααα α
α ααα’α»ααα αααα»αααΆαααΎαα‘αΎααα·ααααααααΆααΆαα½α PostgreSQL
PostgreSQL ααΆααα»αααΆααααααααΆααααααΆαα½ααααααααΆαααααΆαααΆαααααΌαααααΆααα·αααααααα
α
ααα»α
ααΆααααΆαααα½ααααα»ααααααααΆα ααΆβα α
ααΆ
ααΎααααΈααααΎαα»αααΆαααααααααΆααααΆαααααα»ααα»ααααααΆαα ααΎαααααΎααΆαααααα»ααα»αααΌαααααΆααα·ααααααααΆαααα
αΆα α αΎααααααΆαα»αααΆαα
αααα»αααααααΆα (ααααααΆα GitLab ααααα
αααα»α
ααΎααΆαα αααααααααΆαααααΊααΆα’αααΈ?
Lazy replication ααΊααΆααΆαα’αα»ααααααΆαααααΆααααααΌαααΈ 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
ααΌαααααΆααα·ααααααααΆααααα‘αααα ααααΆαααΆαααααααΆαα·α αα»ααααααααΎα’αα»αααααα α₯α‘αΌαααα α’αααα’αΆα α§ααΆα ααα ααΆαα αααα·ααααααα ααΎαααΆαααΆαα αααα·ααααααααααΆααααααΆααα»α αα·ααααααααΆααααΆααα’αααα ααΉααααα αΆ αα·αααααΎαααα αΌαααααΆ α αΎαααΆαααααΆααααΈαα½αααΆαα αααα»αααΌαααααΆααα·ααααααααα·αααααα ααααα·αααΎααΆαααΆααααααΆαααα ααα α’αααααααΆααααα’αΆα ααααααααααΆαααΆαα αααα α αΎαααααΎααΆααΆα ααααα ααα»αααααααααΆααααααΆαααααΆααααααΌαααΆααα’αααααααΆααααΈα ααα»α αααααΎαααΆαααΎαα‘αΎααα·αααΉαααααΌαααΆαααααα
αααα½αα±ααααΆαααααΆαααααααΆ ααΆααΆααΆααααααΎααααα»αααΆαααααΎααααααααΆααααααα·ααααα·ααΆαα ααΆααΆααααααααααααα»αααΆααααααααΆααααααααΆααααΆααααα ααΆα§ααΆα ααα αααααΆααααα
ααααΈαααααααΆααα DDL (ααΌα
ααΆ DROP TABLE
) αααααααΎ log_statements = 'ddl'
. ααααα·αααΎααΎαααΆαααααααααΆααααααα·ααααα·ααΆα ααΎαααΉααα recovery_target_xid
α αΎαααααΎαααΆαα’αααΈααααααααΆαα
α»ααα
ααααα·ααααα·ααΆααα»ααααααααΎ DELETE
.
ααΆααααααααα
ααααΎααΆααα·αααΊααΆααααααΆααα αα»αααΆαααααΆααααααΌαααΆααα’ααα
ααααΈ recovery.conf
α αΎαα
αΆααααααΎα Postgres α‘αΎααα·αα ααΆαα
ααααααΉαααΆαααΆααααααΆααααααααΆαααΈααααααααααααααα»ααααααΆαααααα α αΎαααΎαααΆααααα
ααααααΆαααααα αΆααΆαααα’ααΆααα
α’αααααααααααααααΆαααααΆαα‘αΎααα·α
ααΆαα½αααΉαααΆαα αααααααααΆααααααΆαααααααα½αα±ααααΆαααααα»ααα»ααααααΆαα α’ααααα·αα αΆαααΆα αα αααΆααααα αααΎαααααααΎααααΈααααΆαααΌαααΆαααΆααααΌαααΈααααααΆααααααα ααΆα§ααΆα ααα ααΎαα αααΆααααααααΆαααααααΎααααΈααα½αααΆαααΆαααααα»ααα»αααΌαααααΆα 2 TB ααΆααααΌαα α αΎααααααΆααααα’ααααα ααααααΌαα’αα»αααα WAL αααα αΆαααααααΆααααΌαααΎααααΈααααΆαααααΆαααΆααααα ααααΆα (αααα»αααααΈααα’αΆααααααααα»α) α
ααΆαα αααααααααΆααααααΆααααααΊαααααΎαααΆαααΆαααααα»ααα»ααααααΆααααΆαααΈααα·ααΈα
- αα·αα αΆαααΆα ααα»αααΆαααααα»ααα»αααΌαααααΆαααΆααααΌαα ααααΈαααααααΆαααα
- ααΆααααα’α½α ααααααααααΆαααΈαααααααααααααα WAL αααααααΌαααααααΎαααααααα
ααΎααααα·αα·αααααΎαααΆαα·α αα ααΎααααΈααΎαααΆααΎααΆα’αΆα αα αα½α αααα»αααΆααααααΎα PITR ααΈ WAL αααα¬αα α αΎαααΎαααΉαααααααααΆαααααΆαααΆαααα ααα’αααΈα’αααΎαα»αααα½α α¬αααα αΆααααααααααΆαα½αααααααΆα WAL αααααΆαααΆαααΆαααΊααααΆαααααΆαα αααααααααΆααααααΆααααα
αααα»αα§ααΆα αααααα ααΆααΆαα
αααΆαααα 50 ααΆααΈααΎααααΈααααΆα ααΆααααααΆααααΏαααΊ 110 GB αααα·αααααα WAL αααα»ααα½ααααα (αααααααΆααα
ααααΎα
ααααααα αααααααααααΆαα αααααααααΆααααααΆαααΆααααααααα (αα·ααααααααααααΆαα·αααΆα)
ααααΎααΆαα αααααααααΆααααααΆααααααΆαααα½αααααΌα ααααα·αααΎα’αααααΆααααααα·αααααααααα ααααα α αΎαααΆαααααααααΆαααααα αΆααααα αααα»αααΆααααααΆααααααααΆαααααααα ααΆααααααααα
ααα»ααααβααααΌαβα αΆαβααΆβα ααΆαβα ααααβαα·αβαααβααΆβααΆαβααααα»αβαα»αβααα
ααΆαααααα»ααα»α αα·αααΆαα
ααααααΆαααααααααααααααααΆα ααΆαααααα»ααα»ααααααΆααααΉαααΆαααααααααααααα·αααΎα’αααααΆααααααΎααααα
ααααα DELETE
α¬ DROP TABLE
. ααΎαααααΎααΆαααααα»ααα»αααΈαααααααααα»ααααααΆαα αα·αααααΆαααααΆαααΆααα»αααααΆααΆα α¬ααΌαααααΆααα·ααααααααΆααααΌαα ααα»αααααααααΉαααΉαααΆαααααΎαα»α DROP TABLE
ααααΌαβααΆαβααα·αβα‘αΎαβαα·αβααααΎαβααβααααΆααβαα
βαααα»αβααΆαβα
ααααβααΆααα’ααβαα
βααΎβα
αααααβααααΎααΆα ααΌα
ααααβααΆαβα
ααααβααααααΆβααΉαβαα·αβα’αΆα
βαα½αβααΆαβαα
βααΈαααβααα ααΆαα
αααααααα½αααΆαααααΆααΌαααααΆααα·αααααααααααΆααα
αααααααααΆαααΈαααααΈαα½ααααααΌαααΆααα½αα
αα αα·αα
ααα
αΆαααααα»αα
αααααΈααΆααΆαααΆαα αααααααααΆααααααΆααααααααα αααααααααΎααα·αααΆααααΌαααΆαααΆαααααα»ααα»ααααααΆαααα αααα»ααααααααα»ααααα·ααΆα ααααα·αααΎααααααααααα·αααααααααΆααα ααΆαααΌα ααΆααααααΆαα α¬ααααΉαααα·ααΆαααααααααααααααα·ααα½αα±ααααααααααΆααααΎαα‘αΎαααααΆααα ααΆαα αααααααα½ααααααΊααααΆααααααααααα ααΈαααααα
ααΆαααααααααΆααα ααΎα
ααααα: www.habr.com