Chidziwitso cha wal-g PostgreSQL backup system

WAL-G ndi chida chosavuta komanso chothandiza pothandizira PostgreSQL kumitambo. Ponena za magwiridwe ake akuluakulu, ndiye wolowa m'malo mwa chida chodziwika bwino WAL-E, koma olembedwanso mu Go. Koma pali chinthu chimodzi chofunikira chatsopano mu WAL-G - delta makope. delta makope WAL-G sungani masamba a mafayilo omwe asinthidwa kuchokera ku mtundu wakale wa zosunga zobwezeretsera. WAL-G imagwiritsa ntchito matekinoloje ambiri ofananirako zosunga zobwezeretsera. WAL-G ndiyothamanga kwambiri kuposa WAL-E.

Tsatanetsatane wa momwe wal-g amagwirira ntchito angapezeke m'nkhaniyi: Ife overclock zosunga zobwezeretsera. Yandex maphunziro

Protocol yosungira ya S3 yakhala yotchuka posungira deta. Chimodzi mwazabwino za S3 ndi kuthekera kofikira kudzera pa API, yomwe imakupatsani mwayi wokonzekera kuyanjana kosinthika ndi zosungirako, kuphatikiza mwayi wowerengera anthu, pomwe kukonzanso zidziwitso posungira kumachitika ndi anthu ovomerezeka okha.

Pali njira zingapo zosungira anthu ndi zapadera zomwe zimagwiritsa ntchito protocol ya S3. Lero tiwona yankho lodziwika bwino lokonzekera zosungirako zazing'ono - Minio.

Seva imodzi ya PostgreSQL ndiyabwino kuyesa wal-g, ndipo Minio imagwiritsidwa ntchito m'malo mwa S3.

Minio Server

Kuyika kwa Minio

yum -y install yum-plugin-copr
yum copr enable -y lkiesow/minio
yum install -y minio

Sinthani AccessKey ndi SecretKey mu /etc/minio/minio.conf

vi /etc/minio/minio.conf

Ngati simugwiritsa ntchito nginx pamaso pa Minio, muyenera kusintha

--address 127.0.0.1:9000

--address 0.0.0.0:9000

Kukhazikitsa Minio

systemctl start minio

Pitani ku mawonekedwe a intaneti a Minio http://ip-адрес-сервера-minio:9000 ndikupanga chidebe (mwachitsanzo, pg-backups).

DB seva

WAL-G mu rpm amasonkhanitsidwa ndi ine (Anton Patsev). Github, Fedora COPR.

Amene alibe dongosolo la RPM, gwiritsani ntchito mkuluyo malangizo mwa kukhazikitsa.

Pamodzi ndi wal-g binary, rpm ili ndi zolemba zomwe zimalowetsa zosintha kuchokera ku /etc/wal-gd/server-s3.conf file.

backup-fetch.sh
backup-list.sh
backup-push.sh
wal-fetch.sh
wal-g-run.sh
wal-push.sh

Ikani walg.

yum -y install yum-plugin-copr
yum copr enable -y antonpatsev/wal-g
yum install -y wal-g

Kuyang'ana mtundu wa wal-g.

wal-g --version
wal-g version v0.2.14

Sinthani /etc/wal-gd/server-s3.conf pazosowa zanu.

Mafayilo osinthira ndi mafayilo a data omwe amagwiritsidwa ntchito ndi gulu la database nthawi zambiri amasungidwa limodzi mu cluster data directory, yomwe imatchedwa PGDATA

#!/bin/bash

export PG_VER="9.6"

export WALE_S3_PREFIX="s3://pg-backups" # бакет, который мы создали в S3
export AWS_ACCESS_KEY_ID="xxxx" # AccessKey из /etc/minio/minio.conf 
export AWS_ENDPOINT="http://ip-адрес-сервера-minio:9000"
export AWS_S3_FORCE_PATH_STYLE="true"
export AWS_SECRET_ACCESS_KEY="yyyy" # SecretKey из /etc/minio/minio.conf

export PGDATA=/var/lib/pgsql/$PG_VER/data/
export PGHOST=/var/run/postgresql/.s.PGSQL.5432 # Сокет для подключения к PostgreSQL

export WALG_UPLOAD_CONCURRENCY=2 # Кол-во потоков для закачки 
export WALG_DOWNLOAD_CONCURRENCY=2 # Кол-во потоков для скачивания
export WALG_UPLOAD_DISK_CONCURRENCY=2 # Кол-во потоков на диске для закачки
export WALG_DELTA_MAX_STEPS=7
export WALG_COMPRESSION_METHOD=brotli # Какой метод сжатия использовать.

Mukakonza WAL-G, mumatchula WALG_DELTA_MAX_STEPS - kuchuluka kwa masitepe omwe zosunga zobwezeretsera za delta ndizochulukirapo kuchokera pazosunga zoyambira, ndikufotokozerani mfundo za kukopera kwa delta. Mwina mupanga kopi kuchokera kumalo omaliza omwe analipo, kapena mupanga delta kuchokera pazosunga zonse zoyambirira. Izi ndizofunikira ngati gawo lomwelo la database likusintha nthawi zonse mu database yanu, zomwezo zikusintha nthawi zonse.

Kuyika database.

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.
noarch.rpm
yum install -y postgresql96 postgresql96-server mc

Timakhazikitsa database.

/usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK

Ngati mukuyesa pa seva 1, ndiye kuti muyenera kusinthanso gawo la wal_level kuti musungitse PostgreSQL yocheperako kuposa mtundu 10, ndikufanizira mtundu wa 10 wa PostgreSQL ndi kupitilira apo.

wal_level = archive

Tiyeni tisunge zolemba zakale za WAL masekondi 60 aliwonse pogwiritsa ntchito PostgreSQL yokha. Pa prod, mudzakhala ndi archive_timeout mtengo wosiyana.

archive_mode = on
archive_command = '/usr/local/bin/wal-push.sh %p'
archive_timeout = 60 # Каждые 60 секунд будет выполнятся команда archive_command.

Kuyambira PostgreSQL

systemctl start postgresql-9.6

Mu cholembera chosiyana, timayang'ana zolemba za PostgreSQL zolakwa: (sinthani postgresql-Wed.log kukhala yamakono).

tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log

Tiyeni tipite ku psql.

su - postgres
psql

Pangani database mu psql

Pangani tebulo mu database test1.

create database test1;

Sinthani ku test database.

postgres=# c test1;

Timapanga tebulo indexing_table.

test1=# CREATE TABLE indexing_table(created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());

Kuwonjezera deta.

Timayamba kuyika deta. Timadikirira kwa mphindi 10-20.

#!/bin/bash
# postgres
while true; do
psql -U postgres -d test1 -c "INSERT INTO indexing_table(created_at) VALUES (CURRENT_TIMESTAMP);"
sleep 60;
done

Onetsetsani kuti mwasunga zonse.

su - postgres
/usr/local/bin/backup-push.sh

Timayang'ana zolemba patebulo mu test database1

select * from indexing_table;
2020-01-29 09:41:25.226198+
2020-01-29 09:42:25.336989+
2020-01-29 09:43:25.356069+
2020-01-29 09:44:25.37381+
2020-01-29 09:45:25.392944+
2020-01-29 09:46:25.412327+
2020-01-29 09:47:25.432564+
2020-01-29 09:48:25.451985+
2020-01-29 09:49:25.472653+
2020-01-29 09:50:25.491974+
2020-01-29 09:51:25.510178+

Chingwe ndi nthawi yokha.

Onani mndandanda wa zosunga zobwezeretsera zonse

/usr/local/bin/backup-list.sh

Kuyesa kuchira

Kuchira kwathunthu ndikugudubuza zonse zomwe zilipo WAL.

Imani Postgresql.

Chotsani chilichonse ku /var/lib/pgsql/9.6/data foda.

Thamangani /usr/local/bin/backup-fetch.sh script ngati wogwiritsa ntchito postgres.

su - postgres
/usr/local/bin/backup-fetch.sh

Kusunga zosunga zobwezeretsera kwatha.

Onjezani recovery.conf ku /var/lib/pgsql/9.6/data foda yokhala ndi zotsatirazi.

restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'

Timayamba PostgreSQL. PostgreSQL iyambitsa njira yobwezeretsa kuchokera ku WALs zosungidwa, ndipo pokhapokha pomwe database idzatsegulidwa.

systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log

Kuchira kwa nthawi inayake.

Ngati tikufuna kubwezeretsanso database mpaka miniti inayake, ndiye timawonjezera recovery_target_time parameter ku recovery.conf - timasonyeza nthawi yoti tibwezeretse deta.

restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'
recovery_target_time = '2020-01-29 09:46:25'

Mukachira, yang'anani pa tebulo indexing_table

 2020-01-29 09:41:25.226198+00
 2020-01-29 09:42:25.336989+00
 2020-01-29 09:43:25.356069+00
 2020-01-29 09:44:25.37381+00
 2020-01-29 09:45:25.392944+00

Timayamba PostgreSQL. PostgreSQL iyambitsa njira yobwezeretsa kuchokera ku WALs zosungidwa, ndipo pokhapokha pomwe database idzatsegulidwa.

systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log

Kuyesa

Kupanga nkhokwe ya 1GB monga tafotokozera apa https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff

Kufunsira kukula kwa ndowa mutatha kupanga 1GB ya data.

postgres=# SELECT pg_size_pretty(pg_database_size('test1'));
pg_size_pretty
----------------
1003 MB

s4cmd ndi chida chaulere chamzere chaulere chogwira ntchito ndi data yomwe ikukhala ku Amazon S3 yosungirako. Zomwe zimagwiritsidwa ntchito zimalembedwa m'chinenero cha pulogalamu ya python, ndipo chifukwa cha izi zitha kugwiritsidwa ntchito mu machitidwe onse a Windows ndi Linux.

Kuyika s4cmd

pip install s4cmd

LZ4

s4cmd --endpoint-url=http://ip-адрес-сервера-minio:9000 --access-key=xxxx --secret-key=yyyy du -r s3://pg-backups
840540822       s3://pg-backups/wal_005/
840 МБ в формате lz4 только WAL логов

Полный бекап с lz4 - 1GB данных
time backup_push.sh
real 0m18.582s

Размер S3 бакета после полного бекапа

581480085       s3://pg-backups/basebackups_005/
842374424   s3://pg-backups/wal_005
581 МБ занимает полный бекап

LZMA

После генерации 1ГБ данных
338413694       s3://pg-backups/wal_005/
338 мб логов в формате lzma

Время генерации полного бекапа
time backup_push.sh
real    5m25.054s

Размер бакета в S3
270310495       s3://pg-backups/basebackups_005/
433485092   s3://pg-backups/wal_005/

270 мб занимает полный бекап в формате lzma

Brotli

После генерации 1ГБ данных
459229886       s3://pg-backups/wal_005/
459 мб логов в формате brotli

Время генерации полного бекапа
real    0m23.408s

Размер бакета в S3
312960942       s3://pg-backups/basebackups_005/
459309262   s3://pg-backups/wal_005/

312 мб занимает полный бекап в формате brotli

Kuyerekeza zotsatira pa tchati.

Monga mukuwonera, Brotli akufanana ndi kukula kwa LZMA, koma zosunga zobwezeretsera zimachitika mu nthawi ya LZ4.

Macheza a anthu olankhula Chirasha a PostgreSQL: https://t.me/pgsql

Chonde perekani nyenyezi ku Github ngati mugwiritsa ntchito wal-g

Source: www.habr.com