🥇WAL-G: izipele nokutholwa kwe-PostgreSQL DBMS

Sekuyisikhathi eside kwaziwa ukuthi ukwenza izipele zibe yizindawo zokulahla i-SQL (usebenzisa pg_lahla noma pg_dumpall) akuwona umqondo omuhle. Ukwenza isipele i-PostgreSQL DBMS, kungcono ukusebenzisa umyalo pg_basebackup, okwenza ikhophi kanambambili yamalogi we-WAL. Kodwa uma uqala ukufunda yonke inqubo yokudala ikhophi nokubuyisela, uzoqonda ukuthi udinga ukubhala okungenani amabhayisikili amathathu ukuze lokhu kusebenze futhi kungakubangeli ubuhlungu kokubili ngenhla nangaphansi. Ukunciphisa ukuhlupheka, i-WAL-G yathuthukiswa.

I-WAL-G iyithuluzi elibhalwe ku-Go ukwenza isipele nokubuyisela imininingwane ye-PostgreSQL (futhi kamuva nje i-MySQL/MariaDB, i-MongoDB ne-FoundationDB). Isekela ukusebenza nesitoreji se-Amazon S3 (kanye nama-analogue, isibonelo, i-Yandex Object Storage), kanye ne-Google Cloud Storage, i-Azure Storage, i-Swift Object Storage kanye nesistimu yefayela nje. Konke ukusethwa kwehlela ezinyathelweni ezilula, kodwa ngenxa yokuthi izindatshana ezimayelana nakho zisakazeke ku-inthanethi yonkana, akukho bhukwana eliphelele lendlela yokwenza elingafaka zonke izinyathelo kusukela ekuqaleni kuze kube sekupheleni (kunokuthunyelwe okuningana ku-Habré, kodwa amaphuzu amaningi aphuthelwe lapho).

Lesi sihloko sibhalelwe ngokuyinhloko ukuhlela ulwazi lwami. Angiyona i-DBA futhi ngiyakwazi ukuziveza ngolimi lwabantu abavamile endaweni ethile, ngakho noma yiziphi izilungiso zamukelekile!

Ngokwehlukana, ngiyaqaphela ukuthi yonke into engezansi ibalulekile futhi ihlolwe i-PostgreSQL 12.3 ku-Ubuntu 18.04, yonke imiyalo kufanele yenziwe njengomsebenzisi onelungelo.

setting

Ngesikhathi sokubhala lesi sihloko, inguqulo ezinzile ye-WAL-G v0.2.15 (March 2020). Lokhu sizokusebenzisa (kodwa uma ufuna ukuzakhela ngokwakho kusuka egatsheni eliyinhloko, khona-ke inqolobane ye-github inayo yonke imiyalelo yalokhu.). Ukuze ulande futhi ufake udinga ukwenza:

#!/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/

Ngemuva kwalokhu, udinga ukumisa i-WAL-G kuqala, bese i-PostgreSQL ngokwayo.

Isetha i-WAL-G

Isibonelo sokugcina izipele, i-Amazon S3 izosetshenziswa (ngoba iseduze namaseva ami futhi ukusetshenziswa kwayo ishibhile kakhulu). Ukuze usebenze ngayo, udinga "ibhakede le-s3" nokhiye wokufinyelela.

Zonke iziqephu zendatshana ezidlule mayelana ne-WAL-G zisebenzise ukucushwa kusetshenziswa okuguquguqukayo kwemvelo, kodwa ngalokhu kukhululwa izilungiselelo zingatholakala ku- .walg.json ifayela kumkhombandlela wasekhaya womsebenzisi we-postgres. Ukuyidala, sebenzisa iskripthi se-bash esilandelayo:

#!/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

Ake ngichaze kancane ngawo wonke amapharamitha:

WALG_S3_PREFIX - indlela eya ebhakedeni lakho le-S3 lapho kuzolayishwa khona izipele (ungaya empandeni noma kufolda);
AWS_ACCESS_KEY_ID - ukhiye wokungena ku-S3 (esimeni sokuthola kabusha kuseva yokuhlola, lezi zikhiye kufanele zibe neNqubomgomo ye-ReadOnly! Lokhu kuchazwe kabanzi esigabeni sokululama.);
AWS_SECRET_ACCESS_KEY - ukhiye oyimfihlo kusitoreji se-S3;
WALG_COMPRESSION_METHOD - indlela yokucindezela, kungcono ukusebenzisa i-Brotli (njengoba lokhu kuyincazelo yegolide phakathi kobukhulu bokugcina kanye nesivinini sokucindezela / ukuchithwa);
WALG_DELTA_MAX_STEPS - inombolo "ye-deltas" ngaphambi kokudala isipele esigcwele (zigcina isikhathi nosayizi wedatha elandiwe, kodwa zinganciphisa kancane inqubo yokutakula, ngakho-ke akufanelekile ukusebenzisa amanani amakhulu);
I-PGDATA - indlela eya kusiqondisi nedatha yakho yesizindalwazi (ungathola ngokusebenzisa umyalo pg_lsclusters);
I-PGHOST - ukuxhuma ku-database, ngesipele sendawo kungcono ukukwenza nge-unix-socket njengalesi sibonelo.

Amanye amapharamitha angatholakala kumadokhumenti: https://github.com/wal-g/wal-g/blob/v0.2.15/PostgreSQL.md#configuration.

Isetha i-PostgreSQL

Ukuze i-archiver engaphakathi kwedathabheyisi ilayishe amalogi e-WAL efwini futhi iwabuyisele kuwo (uma kunesidingo), udinga ukusetha amapharamitha ambalwa kufayela lokumisa. /etc/postgresql/12/main/postgresql.conf. Okokuqala nje udinga ukuqinisekisaukuthi azikho izilungiselelo ezingezansi ezisethelwe kunoma imaphi amanye amanani, ukuze kuthi lapho ukucushwa kulayishwa kabusha, i-DBMS ingaphahlazeka. Ungangeza le mingcele usebenzisa:

#!/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

Incazelo yamapharamitha azosethwa:

izinga_lodonga - lungakanani ulwazi okufanele lubhalwe kumalogi we-WAL, "i-replica" - bhala yonke into;
imodi_yengobo yomlando - vumela ukulandwa kwamalogi we-WAL usebenzisa umyalo ovela kupharamitha i-archive_command;
i-archive_command - umyalo wokufaka kungobo yomlando ilogi ye-WAL egcwalisiwe;
archive_timeout - ukugcinwa kwamalogi kwenziwa kuphela uma sekuqediwe, kodwa uma iseva yakho ishintsha / yengeza idatha encane ku-database, khona-ke kunengqondo ukubeka umkhawulo lapha ngemizuzwana, ngemva kwalokho umyalo wokugcinwa kwengobo yomlando uzobizwa ngenkani (Ngibhala ngokujulile ku-database njalo ngomzuzwana, ngakho-ke nginqume ukungabeki le parameter ekukhiqizeni);
buyisela_umyalo - umyalo wokubuyisela ilogi ye-WAL ku-backup uzosetshenziswa uma "isipele esigcwele" (isipele esiyisisekelo) singenazo izinguquko zakamuva kusizindalwazi.

Ungafunda kabanzi mayelana nayo yonke le mingcele ekuhunyushweni kwemibhalo esemthethweni: https://postgrespro.ru/docs/postgresql/12/runtime-config-wal.

Ukusetha ishejuli yokusekelayo

Noma ngabe umuntu angathini, indlela elula kakhulu yokuyiqhuba i-cron. Yilokhu esizokulungisa ukuze senze izipele. Ake siqale ngomyalo wokwenza isipele esigcwele: ku-wal-g lena impikiswano yokuqalisa i-backup-push. Kodwa okokuqala, kungcono ukusebenzisa lo myalo ngesandla kumsebenzisi we-postgres ukuze uqiniseke ukuthi konke kuhamba kahle (futhi awekho amaphutha okufinyelela):

#!/bin/bash

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

Izimpikiswano zokuqalisa zibonisa indlela eya kumkhombandlela wedatha - ngiyakukhumbuza ukuthi ungayithola ngokuqalisa pg_lsclusters.

Uma konke kuhambe ngaphandle kwamaphutha futhi idatha yalayishwa kusitoreji se-S3, ungabese ulungiselela ukwethulwa ngezikhathi ezithile ku-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

Kulesi sibonelo, inqubo yokwenza isipele iqala nsuku zonke ngo-4:15 am.

Isusa izipele ezindala

Kungenzeka ukuthi awudingi ukugcina ngokuphelele zonke izipele zenkathi ye-Mesozoic, ngakho-ke kuyoba usizo ukuthi ngezikhathi ezithile "uhlanze" isitoreji sakho (zombili "izipele ezigcwele" nezingodo ze-WAL). Sizokwenza konke lokhu ngomsebenzi we-cron:

#!/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

I-Cron izosebenzisa lo msebenzi nsuku zonke ngo-6:30 am, isuse yonke into (izipele ezigcwele, i-deltas nama-WAL) ngaphandle kwamakhophi wezinsuku eziyi-10 zokugcina, kodwa ishiya okungenani isipele esisodwa. ukuze usuku olushiwo ukuze noma yiliphi iphuzu после izinsuku zifakiwe ku-PITR.

Ibuyisela kusuka kusipele

Akuyona imfihlo ukuthi ukhiye kusizindalwazi esinempilo ukubuyisela ngezikhathi ezithile nokuqinisekiswa kobuqotho bedatha engaphakathi. Ngizokutshela ukuthi ungalulama kanjani usebenzisa i-WAL-G kulesi sigaba, futhi sizokhuluma ngamasheke kamuva.

Kuyaphawuleka ngokwehlukana ukuthi ukuze ubuyisele endaweni yokuhlola (yonke into engakhiqizi) udinga ukusebenzisa i-akhawunti ethi Funda Kuphela ku-S3 ukuze ungabhali ngaphezulu izipele ngephutha. Esimeni se-WAL-G, udinga ukusetha amalungelo alandelayo kumsebenzisi we-S3 ku-Group Policy (Umthelela: Vumela): s3:GetObject, s3:I-ListBucket, s3:GetBucketLocation. Futhi, yiqiniso, ungakhohlwa ukusetha i-archive_mode=cishiwe kufayela lezilungiselelo postgresql.conf, ukuze isizindalwazi sakho sokuhlola singafuni ukugcinwa ngokuthula.

Ukubuyisela kwenziwa ngokunyakaza okuncane kwesandla isusa yonke idatha ye-PostgreSQL (okuhlanganisa nabasebenzisi), ngakho-ke sicela uqaphele kakhulu lapho usebenzisa imiyalo elandelayo.

#!/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

Kulabo abafuna ukuhlola inqubo yokutakula, ucezu oluncane lwe-bash magic lulungiselelwe ngezansi, ukuze uma kwenzeka izinkinga ekubuyiseleni, iskripthi sizophahlazeka ngekhodi yokuphuma engeyona i-zero. Kulesi sibonelo, ukuhlolwa okungu-120 kwenziwa ngesikhathi sokuvala semizuzwana emi-5 (inani lemizuzu eyi-10 yokuthola kabusha) ukuthola ukuthi ifayela lesiginali lisusiwe yini (lokhu kuzosho ukuthi ukutakula kube yimpumelelo):

#!/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

Ngemuva kokululama ngempumelelo, ungakhohlwa ukuqala zonke izinqubo emuva (pgbouncer/monit, njll.).

Ihlola idatha ngemva kokululama

Kubalulekile ukuhlola ubuqotho besizindalwazi ngemva kokubuyiselwa, ukuze kungaveli isimo esinesipele esiphukile/esigwegwile. Futhi kungcono ukwenza lokhu ngengobo yomlando ngayinye edaliwe, kodwa kuphi futhi kanjani kuncike emcabangweni wakho kuphela (ungaphakamisa amaseva ngamanye ngehora noma wenze isheke ku-CI). Kodwa okungenani, kuyadingeka ukuhlola idatha nezinkomba ku-database.

Ukuhlola idatha, kwanele ukuyiqhuba ngokulahla, kodwa kungcono ukuthi uma udala i-database ube nama-checksums anikwe amandla (amasheke wedatha):

#!/bin/bash

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

Ukuhlola izinkomba - ikhona amcheck module, ake sithathe umbuzo we-sql kuwo Ukuhlolwa kwe-WAL-G futhi wakhe umqondo omncane owuzungezile:

#!/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

Ukufingqa

Ngithanda ukuzwakalisa ukubonga kwami ku-Andrey Borodin ngosizo lwakhe ekulungiseleleni ukushicilelwa kanye nokubonga okukhethekile ngeqhaza lakhe ekuthuthukisweni kwe-WAL-G!

Lokhu kuphetha leli nothi. Ngethemba ukuthi ngikwazile ukudlulisa ukunethezeka kokusetha kanye namandla amakhulu okusebenzisa leli thuluzi enkampanini yakho. Ngizwe okuningi nge-WAL-G, kodwa angikaze ngibe nesikhathi esanele sokuhlala phansi ngikuthole. Futhi ngemva kokuba ngiwusebenzise ekhaya, lesi sihloko saphuma kimi.

Ngokwehlukana, kubalulekile ukuqaphela ukuthi i-WAL-G ingasebenza futhi ne-DBMS elandelayo:

I-MySQL / MariaDB;
I-MongoDB;
I-FoundationDB;
Futhi ukwahlulela ngokuzibophezela, kusalindelwe ezinye eziningi!

Source: www.habr.com

I-WAL-G: izipele kanye nokutholwa kwe-PostgreSQL DBMS