ProHoster > Блог > Nchịkwa > Otu anyị siri jiri PostgreSQL jiri mmụgharị egbu oge maka mgbake ọdachi

Otu anyị siri jiri PostgreSQL jiri mmụgharị egbu oge maka mgbake ọdachi

Otu anyị siri jiri PostgreSQL jiri mmụgharị egbu oge maka mgbake ọdachi
Ntugharị abụghị ndabere. Ma ọ bụ ọ bụghị? Nke a bụ otu anyị si eji mmegharị ebugharị eweghachi azụ site na ihichapụ ụzọ mkpirisi na mberede.

Ndị ọkachamara akụrụngwa GitLab na-ahụ maka ọrụ ahụ GitLab.com - ihe atụ GitLab kachasị na okike. Site na ndị ọrụ nde 3 na ihe fọrọ nke nta ka ọ bụrụ nde 7 nde ọrụ, ọ bụ otu n'ime saịtị SaaS mepere emepe nke nwere ụkpụrụ ụlọ raara onwe ya nye. Na-enweghị sistemụ nchekwa data PostgreSQL, akụrụngwa GitLab.com agaghị aga ebe dị anya, yana ihe anyị na-eme iji hụ na nnabata mmejọ ma ọ bụrụ na ọdịda ọ bụla nwere ike tufuo data. O yighị ka ọdachi dị otú ahụ ga-eme, ma anyị dị njikere nke ọma ma chekwaa usoro nkwado dị iche iche na nhazigharị.

Ntugharị abụghị ụzọ isi akwado nchekwa data (hụ okpuru). Mana ugbu a, anyị ga-ahụ ka esi agbake ngwa ngwa ehichapụ data site na iji mmegharị umengwụ: on GitLab.com onye ọrụ ehichapụ ụzọ mkpirisi ahụ maka oru ngo gitlab-ce yana njikọ furu efu na arịrịọ na ọrụ jikota.

Site na oyiri ewegharịrị, anyị nwetara data n'ime naanị awa 1,5. Lee ka o si mee.

Jiri PostgreSQL tụọ aka na mgbake oge

PostgreSQL nwere ọrụ arụnyere n'ime ya na-eweghachi ọnọdụ nchekwa data n'otu oge na oge. A na-akpọ ya Ntughari n'ime oge (PITR) ma na-eji otu usoro na-eme ka oyiri dị ọhụrụ: malite na foto a pụrụ ịdabere na ya nke ụyọkọ nchekwa data dum (ndabere ndabere), anyị na-etinye usoro mgbanwe nke steeti ruo oge ụfọdụ.

Iji jiri njirimara a maka nkwado ndabere oyi, anyị na-eme nkwado ndabere data oge niile ma chekwaa ya na ebe nchekwa (GitLab Archives bi na ya. nchekwa igwe ojii Google). Anyị na-enyochakwa mgbanwe na steeti nchekwa data site n'ịkwado ndekọ ederede n'ihu (ide-n'ihu log, WAL). Na ihe ndị a niile, anyị nwere ike ịme PITR maka mgbake ọdachi: malite na foto nke ewepụtara tupu ọdịda, na itinye mgbanwe site na ebe nchekwa WAL ruo ọdịda.

Kedu ihe bụ ntugharị ebugharị?

Ntugharị umengwụ bụ itinye mgbanwe sitere na WAL na igbu oge. Ya bụ, azụmahịa ahụ mere n'ime otu awa X, mana ọ ga-apụta na oyiri na-egbu oge d n'ime otu elekere X + d.

PostgreSQL nwere ụzọ 2 iji guzobe oyiri nchekwa data anụ ahụ: mgbake ndabere na ntụgharị nkwanye. Na-eweghachi site na ebe nchekwa, na-arụ ọrụ dị ka PITR, mana na-aga n'ihu: anyị na-eweghachite mgbanwe mgbe niile na ebe nchekwa WAL ma tinye ha na oyiri. A gụgharia nkwanye ugwu na-eweghachite iyi WAL ozugbo site na ndị nnabata nchekwa data dị elu. Anyị na-ahọrọ mgbake Archive - ọ dị mfe ijikwa ma nwee arụmọrụ nkịtị nke na-aga n'ihu na ụyọkọ mmepụta.

Otu esi edobe mgbake egbu oge site na ebe nchekwa

Nhọrọ mgbake kọwara na faịlụ recovery.conf. Ihe atụ:

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'

Site na parampat ndị a, anyị haziela oyiri ewegharịrị nwere mgbake ndabere. Ebe a na-eji wal-e wepụ akụkụ WAL (restore_command) site na Archive, na mgbanwe ga-etinye n'ọrụ mgbe awa asatọ (XNUMX)recovery_min_apply_delay). Ihe oyiri ga-elele maka mgbanwe usoro iheomume n'ime ebe nchekwa, dịka ọmụmaatụ n'ihi ụyọkọ ọdịda (ụyọkọ ọdịda).recovery_target_timeline).

С recovery_min_apply_delay Ị nwere ike iji oge na-egbu oge guzobe mmegharị nkwanye ugwu, mana enwere ọnyà ole na ole ebe a metụtara oghere mmegharị, nzaghachi njikere na-ekpo ọkụ, na ihe ndị ọzọ. Ebe nchekwa WAL na-enye gị ohere ịzere ha.

Ogologo recovery_min_apply_delay pụtara naanị na PostgreSQL 9.3. Na nsụgharị ndị gara aga, maka mmegharị ebugharị, ịkwesịrị ịhazi nchikota ọrụ nchịkwa mgbake (pg_xlog_replay_pause(), pg_xlog_replay_resume()) ma ọ bụ jide akụkụ WAL na ebe nchekwa maka oge igbu oge.

Kedu ka PostgreSQL si eme nke a?

Ọ na-adọrọ mmasị ịhụ ka PostgreSQL si eme mgbake umengwụ. Ka anyị lee anya recoveryApplyDelay(XlogReaderState). A na-akpọ ya site na isi ikwugharị loop maka ntinye ọ bụla sitere na WAL.

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, &microsecs);
    if (secs <= 0 && microsecs <= 0)
        return false;

    while (true)
    {
        // Shortened:
        // Use WaitLatch until we reached recoveryDelayUntilTime
        // and then
        break;
    }
    return true;
}

Ihe dị n'okpuru bụ na igbu oge ahụ dabere na oge anụ ahụ edere na azụmahịa na-eme timestamp (xtime). Dị ka ị na-ahụ, igbu oge na-emetụta naanị ime ma ọ dịghị emetụta ntinye ndị ọzọ - a na-etinye mgbanwe niile ozugbo, na ntinye aka na-egbu oge, ya mere, anyị ga-ahụ mgbanwe naanị mgbe nhazi nhazi.

Otu esi eji oyiri egbu oge iji weghachi data

Ka anyị kwuo na anyị nwere ụyọkọ nchekwa data yana oyiri nwere oge awa asatọ na mmepụta. Ka anyị hụ otú e si naghachi data iji ihe atụ na-ehichapụ ụzọ mkpirisi na mberede.

Mgbe anyị nụrụ banyere nsogbu ahụ, anyị akwụsịla iweghachi akwụkwọ ndekọ aha maka oyiri ebigharịrị:

SELECT pg_xlog_replay_pause();

Site na nkwụsịtụ, anyị enweghị ihe egwu na oyiri ga-emegharị arịrịọ ahụ DELETE. Ihe bara uru ma ọ bụrụ na ịchọrọ oge iji chọpụta ihe niile.

Isi ihe bụ na oyiri ebigharịrị ga-erurịrị oge tupu arịrịọ ahụ DELETE. Anyị maara ihe dịka oge mwepụta anụ ahụ. Anyị ehichapụla recovery_min_apply_delay ma gbakwunyere recovery_target_time в recovery.conf. Nke a bụ ka oyiri ahụ si erute oge kwesịrị ekwesị n'egbughị oge:

recovery_target_time = '2018-10-12 09:25:00+00'

Site na stampụ oge, ọ ka mma ịbelata oke ka ị ghara ịgbaghara. N'eziokwu, ka mbelata ka ukwuu, ka data anyị na-atụfu. Ọzọ, ọ bụrụ na anyị na-atụ uche arịrịọ DELETE, ihe niile ga-ehichapụ ọzọ na ị ga-amalite ọzọ (ma ọ bụ ọbụna na-a oyi ndabere maka PITR).

Anyị maliteghachiri ihe atụ Postgres akwụsịla ma megharịa akụkụ WAL ruo oge a kapịrị ọnụ. Ị nwere ike soro ọganihu na ọkwa a site na ịjụ:

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;

Ọ bụrụ na stampụ oge agaghịzi agbanwe, mgbake ahụ ezuola. Enwere ike ịhazi ihe omume recovery_target_actionimechi, kwalite, ma ọ bụ kwụsịtụ ihe atụ mgbe anwalechara (ọ kwụsịrị na ndabara).

Ebe nchekwa data laghachiri na steeti ya tupu arịrịọ ahụ adịghị mma. Ugbu a ị nwere ike, dịka ọmụmaatụ, mbupụ data. Anyị bupụrụ data labelụ ehichapụ na njikọ niile na nsogbu yana jikọta arịrịọ wee bugharịa ha na nchekwa data mmepụta. Ọ bụrụ na mfu ndị ahụ buru ibu, ị nwere ike ịkwalite oyiri ma jiri ya dị ka nke bụ isi. Ma mgbe ahụ mgbanwe niile mgbe isi ihe anyị gbakere ga-efunahụ.

Kama akara oge, ọ ka mma iji NJ azụmahịa. Ọ bara uru ịdekọ ID ndị a, dịka ọmụmaatụ, maka nkwupụta DDL (dịka DROP TABLE), site n'iji log_statements = 'ddl'. Ọ bụrụ na anyị nwere ID azụmahịa, anyị ga-ewere recovery_target_xid wee gbaa ihe niile gbadaa na azụmahịa tupu arịrịọ ahụ DELETE.

Ịlaghachi n'ọrụ dị nnọọ mfe: wepụ mgbanwe niile na recovery.conf ma malitegharịa Postgres. N'oge na-adịghị anya oyiri ga-enwe oge awa asatọ ọzọ, ma anyị dị njikere maka nsogbu n'ọdịnihu.

Uru mgbake

Site na oyiri ewegharịrị kama nkwado ndabere oyi, ịgaghị etinye ọtụtụ awa iji weghachi onyonyo niile na ebe nchekwa. Dịka ọmụmaatụ, ọ na-ewe anyị awa ise iji nweta nkwado ndabere nke abụọ TB niile. Ma mgbe ahụ ị ka ga-etinye WAL kwa ụbọchị iji gbakee na steeti achọrọ (n'ọnọdụ kachasị njọ).

Ntugharị ebigharịrị dị mma karịa ndabere oyi n'ụzọ abụọ:

  1. Ọ dịghị mkpa iwepu ndabere ndabere na ebe nchekwa.
  2. Enwere windo elekere asatọ nke akụkụ WAL nke a ga-emerịrị ugboro ugboro.

Anyị na-enyochakwa mgbe niile iji hụ ma ọ ga-ekwe omume ịme PITR site na WAL, anyị ga-ahụkwa ngwa ngwa nrụrụ aka ma ọ bụ nsogbu ndị ọzọ na ebe nchekwa WAL site n'ịleba anya n'adịghị ka oyiri ahụ ewegharịrị.

N'ihe atụ a, o were anyị nkeji 50 iji weghachi, nke pụtara na ọsọ ahụ bụ 110 GB nke data WAL kwa elekere (ihe ndekọ ahụ ka dị. Azụ S3). Na mkpokọta, anyị doziri nsogbu ahụ wee nwetaghachi data na awa 1,5.

Nsonaazụ: ebe oyiri eweghachitere bara uru (na ebe ọ na-adịghị)

Jiri ndegharị egbu oge dị ka enyemaka mbụ ma ọ bụrụ na ị tụfuru data na mberede wee chọpụta nsogbu a n'ime oge ahaziri.

Ma buru n'uche: ntughari abụghị ndabere.

Ndabere na ntugharị nwere ebumnuche dị iche iche. Ndabere oyi ga-aba uru ma ọ bụrụ na ị mere na mberede DELETE ma ọ bụ DROP TABLE. Anyị na-eme nkwado ndabere na mpaghara oyi ma weghachite ọnọdụ tebụl gara aga ma ọ bụ nchekwa data dum. Ma n'otu oge ahụ arịrịọ DROP TABLE ọ fọrọ nke nta ka ọ bụrụ na a na-ebipụtaghachi ya ozugbo n'ụdị niile dị na ụyọkọ na-arụ ọrụ, ya mere mmegharị nkịtị agaghị enyere ebe a aka. Ntugharị n'onwe ya na-edobe nchekwa data dị mgbe a na-agbazinye sava onye ọ bụla wee kesaa ibu ahụ.

Ọbụlagodi na oyiri ewegharịrị, anyị na-achọ n'ezie nkwado ndabere na mpaghara ebe nchekwa ma ọ bụrụ na ọdịda etiti data, mmebi zoro ezo, ma ọ bụ ihe omume ndị ọzọ na-adịghị ahụ anya ozugbo eme. Ntugharị naanị abaghị uru ebe a.

Примечание. Na GitLab.com Anyị ugbu a naanị na-echebe megide data ọnwụ na sistemụ larịị na-adịghị naghachi data na onye ọrụ larịị.

isi: www.habr.com

Tinye a comment