🥇WAL-G: kuchengetedza uye kudzoreredza kwePostgreSQL DBMS

Izvo zvave zvichizivikanwa kuti kugadzira ma backups muSQL dumps (kushandisa pg_dump kana pg_dumpall) haisi pfungwa yakanaka. Kuchengetedza iyo PostgreSQL DBMS, zviri nani kushandisa murairo pg_basebackup, iyo inogadzira bhinari kopi yeWAL matanda. Asi paunotanga kudzidza maitiro ese ekugadzira kopi uye kudzoreredza, iwe unozonzwisisa kuti iwe unofanirwa kunyora angangoita akati wandei matricycles kuti izvi zvishande uye kwete kukukonzera kurwadziwa zvese kumusoro uye pazasi. Kuderedza kutambura, WAL-G yakagadzirwa.

WAL-G chishandiso chakanyorwa muGo chekutsigira uye kudzoreredza PostgreSQL dhatabhesi (uye munguva pfupi yapfuura MySQL/MariaDB, MongoDB uye FoundationDB) Inotsigira kushanda neAmazon S3 kuchengetedza (uye analogues, semuenzaniso, Yandex Object Storage), pamwe neGoogle Cloud Storage, Azure Storage, Swift Object Storage uye zviri nyore neiyo faira system. Iyo yese setup inodzika kusvika kumatanho akareruka, asi nekuda kwekuti zvinyorwa pamusoro pazvo zvakapararira paInternet, hapana akazara maitirwo-manyorerwo angazosanganisira ese matanho kubva pakutanga kusvika pakupedzisira (kune akati wandei mameseji paHabré, asi mapoinzi mazhinji akarasika ipapo).

Ichi chinyorwa chakanyorwa kunyanya kuronga ruzivo rwangu. Ini handisi weDBA uye ndinogona kuzviratidza mumutauro wechivanhu pane imwe nzvimbo, saka chero zvigadziriso zvinogamuchirwa!

Kusiyana, ini ndinoona kuti zvese zviri pazasi zvine chekuita uye zvakayedzwa PostgreSQL 12.3 paUbuntu 18.04, mirairo yese inofanira kuitwa semushandisi ane rombo.

Kuiswa

Panguva yekunyora chinyorwa ichi, iyo yakagadzikana vhezheni yeWAL-G iri v0.2.15 (Kurume 2020). Izvi ndizvo zvatichashandisa (asi kana iwe uchida kuzvivaka iwe pachako kubva kune master bazi, saka iyo github repository ine mirairo yese yeizvi.) Kudhawunirodha uye kuisa iwe unofanirwa kuita:

#!/bin/bash

curl -L "https://github.com/wal-g/wal-g/releases/download/v0.2.15/wal-g.linux-amd64.tar.gz" -o "wal-g.linux-amd64.tar.gz"
tar -xzf wal-g.linux-amd64.tar.gz
mv wal-g /usr/local/bin/

Mushure meizvi, unofanirwa kugadzirisa WAL-G kutanga, uyezve PostgreSQL pachayo.

Kugadzira WAL-G

Semuenzaniso wekuchengetedza ma backups, Amazon S3 ichashandiswa (nekuti iri padyo nemaseva angu uye kushandiswa kwayo kwakachipa zvakanyanya) Kuti ushande nayo, unoda "s3 bucket" uye makiyi ekuwana.

Zvese zvakapfuura zvinyorwa nezve WAL-G zvakashandiswa gadziriso uchishandisa nharaunda zvinosiyana, asi nekuburitswa uku zvigadziriso zvinogona kuwanikwa mukati. .walg.json file mudhairekitori remba remushandisi wepostgres. Kuti uigadzire, mhanya inotevera bash script:

#!/bin/bash

cat > /var/lib/postgresql/.walg.json << EOF
{
    "WALG_S3_PREFIX": "s3://your_bucket/path",
    "AWS_ACCESS_KEY_ID": "key_id",
    "AWS_SECRET_ACCESS_KEY": "secret_key",
    "WALG_COMPRESSION_METHOD": "brotli",
    "WALG_DELTA_MAX_STEPS": "5",
    "PGDATA": "/var/lib/postgresql/12/main",
    "PGHOST": "/var/run/postgresql/.s.PGSQL.5432"
}
EOF
# обязательно меняем владельца файла:
chown postgres: /var/lib/postgresql/.walg.json

Rega nditsanangure zvishoma nezve ese ma parameter:

WALG_S3_PREFIX - iyo nzira inoenda kune yako S3 bhaketi uko ma backups anozoiswa (iwe unogona kumudzi kana kune folda);
AWS_ACCESS_KEY_ID - kiyi yekuwana muS3 (kana kupora pane test server, makiyi aya anofanira kunge aine ReadOnly Policy! Izvi zvinotsanangurwa zvakadzama muchikamu chekupora.);
AWS_SECRET_ACCESS_KEY - kiyi yakavanzika muS3 kuchengetedza;
WALG_COMPRESSION_METHOD - compression nzira, zviri nani kushandisa Brotli (sezvo iyi ndiyo yegoridhe zvinoreva pakati peiyo yekupedzisira saizi uye yekumanikidza / decompression kumhanya);
WALG_DELTA_MAX_STEPS - iyo nhamba ye "deltas" isati yagadzira kuchengetedza kwakazara (vanochengetedza nguva uye saizi yedata yakatorwa, asi inogona kunonoka zvishoma kudzoreredza maitiro, saka hazvikurudzirwe kushandisa maitiro makuru);
PGDATA - nzira inoenda kudhairekitori nedatabase yako (unogona kuzviwana nekumhanyisa murairo pg_lsclusters);
PHOST - kubatanidza kune dhatabhesi, ine backup yemuno zviri nani kuzviita kuburikidza neunix-socket semumuenzaniso uyu.

Mamwe ma parameter anogona kuwanikwa mune zvinyorwa: https://github.com/wal-g/wal-g/blob/v0.2.15/PostgreSQL.md#configuration.

Kugadzira PostgreSQL

Kuti iyo archiver mukati medhatabhesi iise WAL matanda kune gore uye idzorere kubva kwavari (kana zvichidikanwa), iwe unofanirwa kuseta akati wandei ma paramita mufaira rekugadzirisa. /etc/postgresql/12/main/postgresql.conf. Chekutanga chete unofanira kuve nechokwadikuti hapana chimwe chezvirongwa zviri pasi apa zvakagadzirirwa kune chero humwe hutsika, kuitira kuti kana iyo yekugadzirisa ichidzoserwa zvakare, DBMS haiparadzi. Iwe unogona kuwedzera aya ma parameter uchishandisa:

#!/bin/bash

echo "wal_level=replica" >> /etc/postgresql/12/main/postgresql.conf
echo "archive_mode=on" >> /etc/postgresql/12/main/postgresql.conf
echo "archive_command='/usr/local/bin/wal-g wal-push "%p" >> /var/log/postgresql/archive_command.log 2>&1' " >> /etc/postgresql/12/main/postgresql.conf
echo “archive_timeout=60” >> /etc/postgresql/12/main/postgresql.conf
echo "restore_command='/usr/local/bin/wal-g wal-fetch "%f" "%p" >> /var/log/postgresql/restore_command.log 2>&1' " >> /etc/postgresql/12/main/postgresql.conf

# перезагружаем конфиг через отправку SIGHUP сигнала всем процессам БД
killall -s HUP postgres

Tsanangudzo yemaparameter achaiswa:

wal_level - ingani ruzivo rwekunyora muWAL logs, "replica" - nyora zvese;
archive_mode -gonesa kurodha pasi WAL matanda uchishandisa rairo kubva paparameter archive_command;
archive_command - kuraira kwekuchengeta rakapedzwa WAL log;
archive_timeout -kuchengeta matanda kunoitwa chete kana yapedzwa, asi kana sevha yako ichichinja / ikawedzera data diki kune dhatabhesi, zvino zvine musoro kuisa muganhu pano mumasekondi, mushure meiyo archive command ichanzi nechisimba (Ini ndinonyora zvakanyanya kune dhatabhesi sekondi yega yega, saka ndakafunga kusaisa iyi parameter mukugadzira);
dzorera_command - murairo wekudzoreredza iyo WAL log kubva kune backup ichashandiswa kana iyo "yakazara backup" (base backup) isina shanduko ichangoburwa mudhatabhesi.

Iwe unogona kuverenga zvakawanda nezve ese ma parameter mushanduro yezvinyorwa zviri pamutemo: https://postgrespro.ru/docs/postgresql/12/runtime-config-wal.

Kugadzira chirongwa chekuchengetedza

Chero zvingataurwa nemunhu, nzira iri nyore yekuimhanyisa ndeye cron. Izvi ndizvo zvatichagadzirisa kugadzira backups. Ngatitangei nemurairo kugadzira backup yakazara: mu wal-g iyi ndiyo nharo yekutanga backup-push. Asi kutanga, zviri nani kumhanyisa uyu murairo nemaoko kubva kumushandisi wepostgres kuti uve nechokwadi chekuti zvese zvakanaka (uye hapana zvikanganiso zvekuwana):

#!/bin/bash

su - postgres -c '/usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main'

Mapoka ekutanga anoratidza nzira inoenda kune data dhairekitori - ndinokuyeuchidza kuti unogona kuiwana nekumhanya pg_lsclusters.

Kana zvese zvikafamba pasina zvikanganiso uye data rakaiswa muS3 chengetedzo, saka unogona kugadzirisa periodic kutanga mu crontab:

#!/bin/bash

echo "15 4 * * *    /usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main >> /var/log/postgresql/walg_backup.log 2>&1" >> /var/spool/cron/crontabs/postgres
# задаем владельца и выставляем правильные права файлу
chown postgres: /var/spool/cron/crontabs/postgres
chmod 600 /var/spool/cron/crontabs/postgres

Mumuenzaniso uyu, maitiro ekuchengetedza anotanga zuva rega rega na4:15 am.

Kudzima ma backups ekare

Zvingangodaro, haufanire kuchengetedza zvachose mabhapu kubva kuMesozoic era, saka zvichave zvinobatsira nguva nenguva "kuchenesa" chengetedzo yako (zvese "zvizere backups" uye WAL matanda). Isu tichaita izvi zvese kuburikidza necron basa:

#!/bin/bash

echo "30 6 * * *    /usr/local/bin/wal-g delete before FIND_FULL $(date -d '-10 days' '+%FT%TZ') --confirm >> /var/log/postgresql/walg_delete.log 2>&1" >> /var/spool/cron/crontabs/postgres
# ещё раз задаем владельца и выставляем правильные права файлу (хоть это обычно это и не нужно повторно делать)
chown postgres: /var/spool/cron/crontabs/postgres
chmod 600 /var/spool/cron/crontabs/postgres

Cron ichamhanyisa basa iri zuva rega rega na6:30 am, ichidzima zvese (yakazara backups, deltas uye WALs) kunze kwemakopi emazuva gumi apfuura, asi ichisiya kamwechete backup. up to yakatarwa date kuitira kuti chero pfungwa после mazuva akaiswa muPITR.

Kudzoreredza kubva kune backup

Haisi chakavanzika kuti kiyi kune ine hutano dhatabhesi kudzoreredza nguva nenguva uye kusimbiswa kwekuvimbika kwe data mukati. Ini ndichakuudza nzira yekupora uchishandisa WAL-G muchikamu chino, uye tichazotaura nezve cheki gare gare.

Zvakakosha kucherechedza zvakasiyana kuti kudzoreredza munzvimbo yekuyedza (zvese zvisiri kugadzirwa) unofanirwa kushandisa Read Chete account muS3 kuitira kuti usarase netsaona ma backups. Panyaya yeWAL-G, unofanirwa kuseta kodzero dzinotevera dzemushandisi weS3 muGroup Policy (Mhedzisiro: Bvumira): s3:GetObject, s3:ListBucket, s3:GetBucketLocation. Uye, zvechokwadi, usakanganwa kuisa archive_mode=off mune zvigadziriso faira postgresql.conf, kuitira kuti dhatabhesi yako yebvunzo irege kuda kutsigirwa chinyararire.

Kudzorera kunoitwa nekufamba zvishoma kweruoko kudzima data rese rePostgreSQL (kusanganisira vashandisi), saka ndapota chenjera zvakanyanya paunomhanyisa mirairo inotevera.

#!/bin/bash

# если есть балансировщик подключений (например, pgbouncer), то вначале отключаем его, чтобы он не нарыгал ошибок в лог
service pgbouncer stop
# если есть демон, который перезапускает упавшие процессы (например, monit), то останавливаем в нём процесс мониторинга базы (у меня это pgsql12)
monit stop pgsql12
# или останавливаем мониторинг полностью
service monit stop
# останавливаем саму базу данных
service postgresql stop
# удаляем все данные из текущей базы (!!!); лучше предварительно сделать их копию, если есть свободное место на диске
rm -rf /var/lib/postgresql/12/main
# скачиваем резервную копию и разархивируем её
su - postgres -c '/usr/local/bin/wal-g backup-fetch /var/lib/postgresql/12/main LATEST'
# помещаем рядом с базой специальный файл-сигнал для восстановления (см. https://postgrespro.ru/docs/postgresql/12/runtime-config-wal#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY ), он обязательно должен быть создан от пользователя postgres
su - postgres -c 'touch /var/lib/postgresql/12/main/recovery.signal'
# запускаем базу данных, чтобы она инициировала процесс восстановления
service postgresql start

Kune avo vanoda kutarisa maitiro ekugadzirisa, chidimbu chiduku che bash magic chakagadzirirwa pasi apa, kuitira kuti kana pane matambudziko mukugadzirisa, script ichaputsika nekodhi isina zero yekubuda. Mumuenzaniso uyu, macheki zana nemakumi maviri anoitwa nenguva yekubuda kwemasekonzi mashanu (yakazara maminetsi gumi ekupora) kuona kana faira rechiratidzo rakadzimwa (izvi zvinoreva kuti kupora kwakabudirira):

#!/bin/bash

CHECK_RECOVERY_SIGNAL_ITER=0
while [ ${CHECK_RECOVERY_SIGNAL_ITER} -le 120 ]
do
    if [ ! -f "/var/lib/postgresql/12/main/recovery.signal" ]
    then
        echo "recovery.signal removed"
        break
    fi
    sleep 5
    ((CHECK_RECOVERY_SIGNAL_ITER+1))
done

# если после всех проверок файл всё равно существует, то падаем с ошибкой
if [ -f "/var/lib/postgresql/12/main/recovery.signal" ]
then
    echo "recovery.signal still exists!"
    exit 17
fi

Mushure mekubudirira kupora, usakanganwa kutanga maitiro ese kumashure (pgbouncer/monit, nezvimwewo).

Kuongorora data mushure mekupora

Izvo zvakakosha kuti utarise kutendeseka kweiyo dhatabhesi mushure mekudzoreredzwa, kuitira kuti mamiriro ane yakatyoka / yakakombama backup isamuke. Uye zviri nani kuita izvi nearchive yega yega yakagadzirwa, asi kupi uye sei zvinoenderana chete nekufungidzira kwako (iwe unogona kusimudza maseva ega paawa kana kumhanyisa cheki muCI). Asi pane zvishoma, zvinodikanwa kuti utarise iyo data uye indexes mune dhatabhesi.

Kuti utarise iyo data, zvakakwana kuti uimhanye kuburikidza nekurasira, asi zviri nani kuti kana uchigadzira dhatabhesi une macheki akagoneswa (data checksums):

#!/bin/bash

if ! su - postgres -c 'pg_dumpall > /dev/null'
then
    echo 'pg_dumpall failed'
    exit 125
fi

Kutarisa indexes - iripo amcheck module, ngatitorei sql mubvunzo wayo kubva WAL-G bvunzo uye gadzira pfungwa diki yakaitenderedza:

#!/bin/bash

# добавляем sql-запрос для проверки в файл во временной директории
cat > /tmp/amcheck.sql << EOF
CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check(c.oid), c.relname, c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree'
AND c.relpersistence != 't'
AND i.indisready AND i.indisvalid;
EOF
chown postgres: /tmp/amcheck.sql

# добавляем скрипт для запуска проверок всех доступных баз в кластере
# (обратите внимание что переменные и запуск команд – экранированы)
cat > /tmp/run_amcheck.sh << EOF
for DBNAME in $(su - postgres -c 'psql -q -A -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" ')
do
    echo "Database: ${DBNAME}"
    su - postgres -c "psql -f /tmp/amcheck.sql -v 'ON_ERROR_STOP=1' ${DBNAME}" && EXIT_STATUS=$? || EXIT_STATUS=$?
    if [ "${EXIT_STATUS}" -ne 0 ]
    then
        echo "amcheck failed on DB: ${DBNAME}"
        exit 125
    fi
done
EOF
chmod +x /tmp/run_amcheck.sh

# запускаем скрипт
/tmp/run_amcheck.sh > /tmp/amcheck.log

# для проверки что всё прошло успешно можно проверить exit code или grep’нуть ошибку
if grep 'amcheck failed' "/tmp/amcheck.log"
then
    echo 'amcheck failed: '
    cat /tmp/amcheck.log
    exit 125
fi

Kupfupikisa

Ndinoda kuratidza kutenda kwangu kuna Andrey Borodin nerubatsiro rwake mukugadzirira chinyorwa uye kuvonga kwakakosha nekuda kwekubatsira kwake mukuvandudza WAL-G!

Izvi zvinopedzisa chinyorwa ichi. Ndinovimba kuti ndakakwanisa kuendesa kureruka kwekuseta uye kugona kukuru kwekushandisa chishandiso ichi mukambani yako. Ndakanzwa zvakawanda nezve WAL-G, asi handina kumbova nenguva yakakwana yekugara pasi ndozvifunga. Uye mushure mekunge ndazviita kumba, chinyorwa ichi chakabuda mandiri.

Zvakasiyana, zvakakosha kuziva kuti WAL-G inogonawo kushanda neinotevera DBMS:

MySQL / MariaDB;
MongoDB;
FoundationDB;
Uye nekutonga nekuita, akati wandei anotarisirwa!

Source: www.habr.com

WAL-G: backups uye kudzoreredza kwePostgreSQL DBMS