🥇WAL-G: li-backups le ho hlaphoheloa ha PostgreSQL DBMS

Ke khale ho tsejoa hore ho etsa li-backups ho li-dumps tsa SQL (ho sebelisa pg_lahla kapa pg_dumpall) ha se mohopolo o motle. Ho boloka DBMS ea PostgreSQL, ho molemo ho sebelisa taelo khutso, e etsang kopi ea binary ea WAL logs. Empa ha o qala ho ithuta ts'ebetso eohle ea ho etsa kopi le ho e khutlisa, o tla utloisisa hore o hloka ho ngola bonyane li-tricycle tse 'maloa hore sena se sebetse mme se se ke sa u bakela bohloko ka holimo le ka tlase. Ho fokotsa mahlomola, WAL-G e ile ea ntlafatsoa.

WAL-G ke sesebelisoa se ngotsoeng ho Go bakeng sa ho boloka le ho khutlisetsa li-database tsa PostgreSQL (le morao tjena MySQL/MariaDB, MongoDB le FoundationDB). E ts'ehetsa ho sebetsa le polokelo ea Amazon S3 (le li-analogues, mohlala, Yandex Object Storage), hammoho le Google Cloud Storage, Azure Storage, Swift Object Storage le feela ka sistimi ea faele. Setupo eohle e theohela mehatong e bonolo, empa ka lebaka la hore lingoliloeng tse mabapi le eona li hasane marang-rang, ha ho na tataiso e felletseng e tla kenyelletsa mehato eohle ho tloha qalong ho fihlela qetellong (ho na le lipehelo tse 'maloa ho Habré, empa lintlha tse ngata li fosa moo).

Sehlooho sena se ngotsoe haholo-holo ho hlophisa tsebo ea ka. Ha ke DBA mme ke khona ho itlhalosa kae-kae ka puo ea batho feela, kahoo litokiso leha e le life lia amoheleha!

Ka thoko, kea hlokomela hore ntho e ngoe le e ngoe e ka tlase e bohlokoa ebile e lekoa bakeng sa PostgreSQL 12.3 ho Ubuntu 18.04, litaelo tsohle li tlameha ho etsoa joalo ka mosebelisi ea lehlohonolo.

bophirima

Nakong ea ho ngola sengoloa sena, mofuta o tsitsitseng oa WAL-G ke v0.2.15 (March 2020). Sena ke seo re tla se sebelisa (empa haeba u batla ho iketsetsa eona ho tsoa lekaleng le leholo, joale polokelo ea github e na le litaelo tsohle tsa sena.). Ho jarolla le ho kenya o hloka ho etsa:

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

Kamora sena, o hloka ho lokisa WAL-G pele, ebe PostgreSQL ka boeona.

Ho theha WAL-G

Bakeng sa mohlala oa ho boloka li-backups, Amazon S3 e tla sebelisoa (hobane e haufi le li-server tsa ka mme ts'ebeliso ea eona e theko e tlase haholo). Ho sebetsa le eona, o hloka "bakete ea s3" le linotlolo tsa phihlello.

Lingoliloeng tsohle tse fetileng mabapi le WAL-G e sebelisitsoeng tlhophiso e sebelisa mefuta e fapaneng ea tikoloho, empa ka tokollo ena litlhophiso li ka fumaneha ho .walg.json faele bukeng ea lapeng ea mosebelisi oa postgres. Ho e theha, tsamaisa sengoloa se latelang sa bash:

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

E re ke hlalose hanyenyane ka li-parameter kaofela:

WALG_S3_PREFIX - tsela e lebang baketeng ea hau ea S3 moo li-backups li tla kengoa (o ka ea motsong kapa foldareng);
AWS_ACCESS_KEY_ID - senotlolo sa ho kena ho S3 (tabeng ea ho hlaphoheloa ho seva sa teko, linotlolo tsena li tlameha ho ba le Leano la ReadOnly! Sena se hlalosoa ka ho qaqileng haholoanyane karolong ea ho hlaphoheloa.);
AWS_SECRET_ACCESS_KEY - senotlolo sa lekunutu polokelong ea S3;
WALG_COMPRESSION_METHOD - mokhoa oa ho hatella, ho molemo ho sebelisa Brotli (kaha ena ke moelelo oa khauta pakeng tsa boholo ba ho qetela le lebelo la ho hatella / ho senya);
WALG_DELTA_MAX_STEPS - palo ea "deltas" pele e theha "backup" e felletseng (li boloka nako le boholo ba data e jarollotsoeng, empa e ka fokotsa hanyane ts'ebetso ea ho hlaphoheloa, ka hona ha ho bohlale ho sebelisa litekanyetso tse kholo);
PGDATA - tsela e eang bukeng ka data ea hau ea database (o ka tseba ka ho tsamaisa taelo pg_lsclusters);
TŠEBELETSO - ho hokela ho database, ka backup ea lehae ho molemo ho e etsa ka unix-socket joalo ka mohlala ona.

Li-parameter tse ling li ka fumanoa litokomaneng: https://github.com/wal-g/wal-g/blob/v0.2.15/PostgreSQL.md#configuration.

Ho theha PostgreSQL

E le hore setsi sa polokelo ea boitsebiso se ka hare ho database se kenye lintlha tsa WAL marung le ho li khutlisetsa ho tsona (haeba ho hlokahala), o hloka ho beha litekanyo tse 'maloa faeleng ea tlhophiso. /etc/postgresql/12/main/postgresql.conf. Feela bakeng sa ho qala o hloka ho etsa bonnetehore ha ho le e 'ngoe ea litlhophiso tse ka tlase e behiloeng ho litekanyetso tse ling, e le hore ha tlhophiso e tsosolosoa, DBMS e se ke ea senyeha. U ka eketsa li-parameter tsena u sebelisa:

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

Tlhaloso ea li-parameter tse lokelang ho hlophisoa:

lebota_lebato - ke boitsebiso bo bokae bo lokelang ho ngoloa ka li-logs tsa WAL, "replica" - ngola ntho e 'ngoe le e' ngoe;
archive_mode - nolofalletsa ho khoasolla lintlha tsa WAL u sebelisa taelo e tsoang ho parameter archive_taelo;
archive_taelo - taelo ea ho boloka tlaleho e phethiloeng ea WAL;
archive_timeout - ho boloka li-logs ho etsoa feela ha ho phethiloe, empa haeba seva sa hau se fetoha / se eketsa lintlha tse nyenyane ho database, joale hoa utloahala ho beha moeli mona ka metsotsoana, ka mor'a moo taelo ea polokelo e tla bitsoa ka likhoka (Ke ngola ka matla ho database motsotsoana o mong le o mong, kahoo ke entse qeto ea ho se behe parameter ena tlhahisong);
busetsa_taelo - taelo ea ho khutlisa log ea WAL ho tsoa ho bekapo e tla sebelisoa haeba "backup e felletseng" (backup ea motheo) e se na liphetoho tsa morao-rao polokelong ea litaba.

U ka bala haholoanyane ka liparamente tsena tsohle phetolelong ea litokomane tsa semmuso: https://postgrespro.ru/docs/postgresql/12/runtime-config-wal.

Ho theha kemiso ea bekapo

Eng kapa eng eo motho a ka e buang, tsela e bonolo ka ho fetisisa ea ho e tsamaisa ke cron. Sena ke seo re tla se hlophisa ho theha li-backups. Ha re qaleng ka taelo ea ho theha bekapo e felletseng: ho wal-g ena ke khang ea ho qala bekapo-push. Empa pele, ho molemo ho tsamaisa taelo ena ka letsoho ho tsoa ho mosebelisi oa postgres ho etsa bonnete ba hore tsohle li lokile (mme ha ho na liphoso tsa phihlello):

#!/bin/bash

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

Likhang tsa ho qala li bonts'a tsela e eang bukeng ea data - ke u hopotsa hore u ka e fumana ka ho matha pg_lsclusters.

Haeba ntho e 'ngoe le e' ngoe e tsamaile ntle le liphoso 'me data e kentsoe polokelong ea S3, joale u ka hlophisa tlhahiso ea nako le nako ho 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

Mohlala ona, ts'ebetso ea "backup" e qala letsatsi le leng le le leng ka 4:15 am.

Ho hlakola li-backups tsa khale

Mohlomong ha ho hlokahale hore u boloke li-backups tsohle tsa mehleng ea Mesozoic, kahoo ho tla ba molemo ho "hloekisa" polokelo ea hau nako le nako (ka bobeli "li-backups tse felletseng" le li-logs tsa WAL). Re tla etsa sena kaofela ka mosebetsi oa 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

Cron e tla etsa mosebetsi ona letsatsi le leng le le leng ka 6:30 hoseng, e hlakola ntho e 'ngoe le e' ngoe (li-backups tse feletseng, deltas le WALs) ntle le likopi tsa matsatsi a 10 a fetileng, empa a siea bonyane bekapo e le 'ngoe. ho letsatsi le boletsoeng e le hore ntlha efe kapa efe после matsatsi a kenyelelitsoe ho PITR.

Ho tsosolosa ho tswa ho bekapo

Ha se lekunutu la hore senotlolo sa polokelo ea litaba tse phetseng hantle ke ho khutlisa nako le nako le ho netefatsa botšepehi ba data ka hare. Ke tla u bolella mokhoa oa ho hlaphoheloa u sebelisa WAL-G karolong ena, 'me re tla bua ka licheke hamorao.

Ke habohlokoa ho hlokomela ka thoko hore ho tsosolosa sebakeng sa teko (ntho e 'ngoe le e 'ngoe e sa hlahisoang) u hloka ho sebelisa ak'haonte ea Bala Feela ho S3 e le hore u se ke ua hlakola li-backups ka phoso. Tabeng ea WAL-G, o hloka ho beha litokelo tse latelang bakeng sa mosebelisi oa S3 ho Group Policy (Phello: Lumella): s3:GetObject, s3:ListBucket, s3:GetBucketLocation. 'Me, ha e le hantle, u se ke ua lebala ho beha polokelo_mokhoa=tima faeleng ea li-setting postgresql.conf, e le hore database ea hau ea liteko ha e batle ho tšehetsoa ka khutso.

Tsosoloso e etsoa ka ho sisinyeha hanyenyane ha letsoho ho hlakola data eohle ea PostgreSQL (ho kenyeletsoa le basebelisi), kahoo ka kopo, ela hloko haholo ha u sebelisa litaelo tse latelang.

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

Bakeng sa ba batlang ho hlahloba mokhoa oa ho hlaphoheloa, karoloana e nyenyane ea bash boselamose e lokiselitsoe ka tlase, e le hore haeba ho na le mathata a ho hlaphoheloa, script e tla senyeha ka khoutu ea ho tsoa e se nang zero. Mohlala ona, licheke tse 120 li etsoa ka nako ea metsotsoana e 5 (kakaretso ea metsotso e 10 bakeng sa ho hlaphoheloa) ho fumana hore na faele ea matšoao e hlakotsoe (sena se tla bolela hore ho hlaphoheloa ho atlehile):

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

Kamora ho hlaphoheloa ka katleho, u se ke oa lebala ho qala lits'ebetso tsohle morao (pgbouncer/monit, joalo-joalo).

Ho hlahloba data ka mor'a ho hlaphoheloa

Hoa hlokahala ho hlahloba botšepehi ba database ka mor'a ho tsosolosoa, e le hore boemo bo nang le "backup" bo robehileng / bo sothehileng bo se ke ba hlaha. 'Me ho molemo ho etsa sena ka polokelo e' ngoe le e 'ngoe e entsoeng, empa hokae le joang ho itšetlehile feela ka monahano oa hau (o ka phahamisa li-server ka hora kapa ho tsamaisa cheke ho CI). Empa bonyane, hoa hlokahala ho hlahloba lintlha le li-index ho database.

Ho lekola data, ho lekane ho e tsamaisa ka thotobolong, empa ho molemo hore ha o theha database o be le li-checksums tse nolofalitsoeng (tlhahlobo ea data):

#!/bin/bash

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

Ho hlahloba li-index - e teng amcheck module, ha re nkeng potso ea sql ho eona Liteko tsa WAL-G 'me u hahe mohopolo o monyane ho e potoloha:

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

Ho akaretsa

Ke rata ho hlalosa teboho ea ka ho Andrey Borodin ka thuso ea hae ea ho lokisetsa khatiso le liteboho tse khethehileng bakeng sa tlatsetso ea hae ho nts'etsopele ea WAL-G!

Sena se phethela molaetsa ona. Ke tšepa hore ke khonne ho fetisa boiketlo ba ho seta le monyetla o moholo oa ho sebelisa sesebelisoa sena k'hamphaning ea hau. Ke utloile haholo ka WAL-G, empa ha ho mohla nkileng ka ba le nako e lekaneng ea ho lula fatše le ho e utloisisa. 'Me ka mor'a hore ke e sebelise lapeng, sehlooho sena se ile sa tsoa ho' na.

Ka thoko, ho bohlokoa ho hlokomela hore WAL-G e ka sebetsa le DBMS e latelang:

MySQL / MariaDB;
MongoDB;
MotheoDB;
Mme ho ahlola ka boitlamo, ho lebelletsoe tse ling tse 'maloa!

Source: www.habr.com

WAL-G: li-backups le ho hlaphoheloa ha PostgreSQL DBMS