An dade da sanin cewa yin ajiyar kuɗi a cikin jujjuyawar SQL (amfani da pg_zuba ko pg_dumpall) ba kyakkyawan ra'ayi ba ne. Don madadin PostgreSQL DBMS, yana da kyau a yi amfani da umarnin pg_basebackup, wanda ke yin kwafin binary na WAL logs. Amma lokacin da kuka fara nazarin duk tsarin ƙirƙirar kwafin da maidowa, za ku fahimci cewa kuna buƙatar rubuta aƙalla keken keke guda biyu don wannan ya yi aiki kuma ba zai haifar muku da zafi a sama da ƙasa ba. Don rage wahala, an haɓaka WAL-G.
kayan aiki ne da aka rubuta a cikin Go don tallafawa da dawo da bayanan bayanan PostgreSQL (kuma kwanan nan MySQL/MariaDB, MongoDB da FoundationDB). Yana goyan bayan aiki tare da ajiyar Amazon S3 (da analogues, misali, Yandex Object Storage), da Google Cloud Storage, Azure Storage, Swift Object Storage kuma kawai tare da tsarin fayil. Gabaɗayan saitin ya zo zuwa matakai masu sauƙi, amma saboda gaskiyar cewa labaran game da shi sun warwatse a cikin Intanet, babu cikakkiyar yadda za a iya amfani da shi wanda zai haɗa da duk matakai daga farko zuwa ƙarshe (akwai posts da yawa akan Habré, amma an rasa maki da yawa a can).
An rubuta wannan labarin da farko don tsara ilimina. Ni ba DBA ba ne kuma zan iya bayyana kaina cikin yaren layman a wani wuri, don haka duk wani gyara ana maraba da shi!
Na dabam, na lura cewa duk abin da ke ƙasa ya dace kuma an gwada shi don PostgreSQL 12.3 akan Ubuntu 18.04, duk umarni dole ne a aiwatar da su azaman mai amfani mai gata.
saitin
A lokacin rubuta wannan labarin, ingantaccen sigar WAL-G shine . Wannan shi ne abin da za mu yi amfani da shi (amma idan kuna son gina shi da kanku daga babban reshe, to ma'aunin github yana da duk umarnin don wannan). Don saukewa kuma shigar kuna buƙatar yin:
#!/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/
Bayan wannan, kuna buƙatar saita WAL-G da farko, sannan PostgreSQL kanta.
Saita WAL-G
Misali na adana bayanan ajiya, Amazon S3 za a yi amfani da shi (saboda ya fi kusa da sabar dina kuma amfaninsa yana da arha sosai). Don yin aiki da shi, kuna buƙatar "guga s3" da maɓallan shiga.
Duk labaran da suka gabata game da WAL-G sunyi amfani da tsari ta amfani da masu canjin yanayi, amma tare da wannan sakin ana iya samun saitunan a ciki a cikin gida directory na postgres mai amfani. Don ƙirƙirar shi, gudanar da rubutun bash mai zuwa:
#!/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
Bari in yi bayani kadan game da dukkan sigogi:
- WALG_S3_PREFIX - hanyar zuwa guga na S3 inda za'a loda abubuwan ajiya (zaku iya ko dai zuwa tushen ko zuwa babban fayil);
- AWS_ACCESS_KEY_ID - maɓallin shiga cikin S3 (idan an dawo da sabar gwaji, dole ne waɗannan maɓallan su kasance da Manufar ReadOnly! An bayyana wannan dalla-dalla a cikin sashin farfadowa.);
- AWS_SIRRIN_ACCESS_KEY - maɓallin sirri a cikin ajiyar S3;
- WALG_COMPRESSION_HANYAR - Hanyar matsawa, yana da kyau a yi amfani da Brotli (tunda wannan shine ma'anar zinariya tsakanin girman karshe da matsawa / raguwa);
- WALG_DELTA_MAX_STEPS - adadin "deltas" kafin ƙirƙirar cikakken madadin (suna adana lokaci da girman bayanan da aka sauke, amma zasu iya rage jinkirin tsarin dawowa, don haka ba shi da kyau a yi amfani da manyan dabi'u);
- PGDATA - hanyar zuwa kundin adireshi tare da bayanan bayanan ku (za ku iya ganowa ta hanyar gudanar da umarni pg_lsclusters);
- PGHOST - haɗi zuwa bayanan bayanai, tare da madadin gida yana da kyau a yi ta ta hanyar unix-socket kamar yadda a cikin wannan misali.
Ana iya samun wasu sigogi a cikin takaddun: .
Saita PostgreSQL
Domin mai adana bayanan da ke cikin bayanan ya loda rajistan ayyukan WAL zuwa gajimare kuma ya mayar da su daga gare su (idan ya cancanta), kuna buƙatar saita sigogi da yawa a cikin fayil ɗin sanyi. /etc/postgresql/12/main/postgresql.conf. Kawai don farawa kana bukatar ka tabbatarcewa babu wani saitin da ke ƙasa da aka saita zuwa wasu dabi'u, ta yadda lokacin da aka sake loda na'urar, DBMS ba zai rushe ba. Kuna iya ƙara waɗannan sigogi ta amfani da:
#!/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
Bayanin sigogin da za a saita:
- wal_level - nawa bayanin da za a rubuta a cikin rajistan ayyukan WAL, "replica" - rubuta komai;
- Archive_mode - ba da damar zazzage rajistan ayyukan WAL ta amfani da umarni daga siga Archive_umarni;
- Archive_umarni – umarnin don adana cikakken log log na WAL;
- Archive_timeout - Ana yin ajiyar rajistan ayyukan ne kawai lokacin da aka gama, amma idan uwar garken ku ta canza / ƙara ƙaramin bayanai a cikin bayanan, yana da ma'ana don saita iyaka anan cikin daƙiƙa, bayan haka za a kira umarnin adanawa da ƙarfiIna yin rubutu mai zurfi zuwa ga bayanan kowane daƙiƙa, don haka na yanke shawarar kada in saita wannan siga a samarwa);
- mayar_umarni – Za a yi amfani da umarnin mayar da log ɗin WAL daga maajiyar idan “cikakken madadin” (base backup) ya rasa sabbin canje-canje a cikin bayanan.
Kuna iya karanta ƙarin game da duk waɗannan sigogi a cikin fassarar takaddun hukuma: .
Saita jadawalin wariyar ajiya
Duk abin da mutum zai iya faɗi, hanya mafi dacewa don gudanar da shi ita ce cron. Wannan shi ne abin da za mu saita don ƙirƙirar madadin. Bari mu fara da umarnin don ƙirƙirar cikakken madadin: a cikin wal-g wannan shine hujjar ƙaddamarwa madadin-turawa. Amma da farko, yana da kyau a gudanar da wannan umarni da hannu daga mai amfani da postgres don tabbatar da cewa komai yayi kyau (kuma babu kurakurai masu shiga):
#!/bin/bash
su - postgres -c '/usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main'
Abubuwan da aka ƙaddamar suna nuna hanyar zuwa kundin bayanai - Ina tunatar da ku cewa za ku iya gano shi ta hanyar gudu pg_lsclusters.
Idan komai ya tafi ba tare da kurakurai ba kuma an ɗora bayanan a cikin ajiyar S3, sannan zaku iya saita ƙaddamar da lokaci-lokaci a cikin 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
A cikin wannan misali, tsarin ajiyar yana farawa kowace rana da karfe 4:15 na safe.
Share tsofaffin madogara
Mafi mahimmanci, ba kwa buƙatar kiyaye cikakken duk abubuwan da aka adana daga zamanin Mesozoic, don haka zai zama da amfani don "tsabta" ma'ajiyar ku lokaci-lokaci (duka "cikakkun ajiya" da rajistan WAL). Za mu yi wannan duka ta hanyar aikin 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 zai gudanar da wannan aikin kowace rana da karfe 6:30 na safe, yana share komai (cikakkiyar ajiya, deltas da WALs) sai dai kwafi na kwanaki 10 na ƙarshe, amma yana barin aƙalla madadin guda ɗaya. to ƙayyadadden kwanan wata don kowane batu после kwanan wata an haɗa su a cikin PITR.
Ana dawowa daga madadin
Ba asiri ba ne cewa mabuɗin ingantattun bayanai shine maidowa lokaci-lokaci da tabbatar da amincin bayanan da ke ciki. Zan gaya muku yadda ake murmurewa ta amfani da WAL-G a cikin wannan sashe, kuma za mu yi magana game da cak daga baya.
Yana da daraja a lura daban cewa don mayarwa a cikin yanayin gwaji (duk abin da ba a samarwa ba) kuna buƙatar amfani da asusu na Read Only a cikin S3 don kada ku sake rubutawa da gangan. Game da WAL-G, kuna buƙatar saita haƙƙoƙin masu zuwa ga mai amfani da S3 a cikin Manufofin Rukuni (Tasiri: Izinin): s3: Samun Abu, s3: Lissafi, s3: GetBucketLocation. Kuma, ba shakka, kar a manta da saita archive_mode=kashe a cikin fayil ɗin saituna postgresql.conf, ta yadda bayanan gwajin ku baya son a yi mata tallafi cikin nutsuwa.
Ana yin gyare-gyare tare da ɗan motsi na hannu share duk bayanan PostgreSQL (ciki har da masu amfani), don haka da fatan za a yi taka tsantsan lokacin da kuke gudanar da umarni masu zuwa.
#!/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
Ga wadanda suke so su duba tsarin farfadowa, an shirya wani karamin sihiri na bash a kasa, don haka idan akwai matsaloli a farfadowa, rubutun zai fadi tare da lambar fita ba sifili ba. A cikin wannan misali, ana yin cak 120 tare da ƙarewar daƙiƙa 5 (jimlar mintuna 10 don farfadowa) don gano ko an goge fayil ɗin siginar (wannan yana nufin cewa an sami nasarar dawo da shi):
#!/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
Bayan murmurewa mai nasara, kar a manta da fara duk matakai baya (pgbouncer/monit, da sauransu).
Duba bayanai bayan dawo da
Yana da mahimmanci a bincika amincin bayanan bayanan bayan an dawo da su, don kada wani yanayi tare da karyewa / karkatacce madadin ya taso. Kuma yana da kyau a yi wannan tare da kowane tarihin da aka ƙirƙira, amma inda kuma ta yaya ya dogara ne kawai akan tunanin ku (zaku iya haɓaka sabobin kowane sa'o'i ko gudanar da rajistan shiga CI). Amma aƙalla, ya zama dole don bincika bayanai da fihirisa a cikin bayanan.
Don duba bayanan, ya isa a gudanar da shi ta hanyar juji, amma yana da kyau cewa lokacin ƙirƙirar ma'ajin bayanai an kunna rajistan bayanai ():
#!/bin/bash
if ! su - postgres -c 'pg_dumpall > /dev/null'
then
echo 'pg_dumpall failed'
exit 125
fi
Don duba fihirisa - akwai , bari mu dauki tambayar sql daga gare ta kuma ku gina ɗan dabaru a kusa da shi:
#!/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
Don taƙaitawa
Ina so in nuna godiyata ga Andrey Borodin don taimakon da ya bayar wajen shirya littafin da godiya ta musamman kan gudunmawar da ya bayar ga ci gaban WAL-G!
Wannan ya ƙare wannan bayanin. Ina fatan cewa na sami damar isar da sauƙi na saitin da babbar damar yin amfani da wannan kayan aiki a cikin kamfanin ku. Na ji labarin WAL-G da yawa, amma ban sami isasshen lokacin da zan zauna in gane shi ba. Kuma bayan na aiwatar da shi a gida, wannan labarin ya fito daga gare ni.
Na dabam, yana da kyau a lura cewa WAL-G na iya aiki tare da DBMS masu zuwa:
- ;
- ;
- ;
- Kuma yin la'akari da abubuwan da aka aikata, ana sa ran wasu da yawa!
source: www.habr.com