🥇Fa'atomuaga ile wal-g PostgreSQL backup system

WAL-G o se meafaigaluega faigofie ma aoga mo le lagolagoina o PostgreSQL i le ao. E tusa ai ma ana galuega autu, o le sui o le meafaigaluega lauiloa WAL-E, ae toe tusia i le Go. Ae o le WAL-G e tasi le mea taua fou: kopi kopi. kopi Delta WAL-G teu itulau o faila ua suia talu mai le lomiga muamua o le faaleoleo. Ua fa'aaogaina e le WAL-G le tele o tekinolosi mo le fa'atusaina o fa'amaumauga. O le WAL-G e sili atu le saoasaoa nai lo le WAL-E.

O fa'amatalaga o le auala e galue ai wal-g e mafai ona maua ile tusiga: Matou te faatelevaveina le faaleoleo. Yandex lauga

O le S3 storage protocol ua lauiloa mo le teuina o faʻamatalaga. O se tasi o mea lelei o le S3 o le mafai lea ona maua e ala i le API, lea e mafai ai e oe ona faʻatulagaina fegalegaleaiga fetuutuunai ma le teuina, e aofia ai le faitau lautele, aʻo faʻafouina faʻamatalaga i totonu o le teuina e naʻo tagata faʻatagaina.

O loʻo i ai le tele o faʻatinoga faʻapipiʻi lautele ma tagata tumaoti e faʻaogaina le S3 protocol. O aso nei o le a tatou vaʻavaʻai i se fofo lauiloa mo le faʻatulagaina o fale teu oloa laiti - Minio.

Ole PostgreSQL server e tasi e fetaui mo le suʻega wal-g, ma o le Minio e faʻaaogaina e sui ai le S3.

Minio Server

Fa'apipi'i le Minio

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

Fa'atonu AccessKey ma SecretKey i totonu /etc/minio/minio.conf

vi /etc/minio/minio.conf

Afai e te le faʻaogaina le nginx i luma o Minio, ona e manaʻomia lea ona sui

--address 127.0.0.1:9000

--address 0.0.0.0:9000

Tatala Minio

systemctl start minio

Alu i le Minio web interface http://ip-адрес-сервера-minio:9000 ma fai se pakete (mo se faataitaiga, pg-backups).

DB server

Ou te faʻapotopotoina WAL-G i le rpm (Anton Patsev). Github, Fedora COPR.

Mo i latou e leai se faiga faʻavae RPM, faʻaaoga le mea aloaia faatonuga i luga o le faʻapipiʻiina.

Faatasi ai ma le wal-g binary, o le rpm o loʻo i ai faʻamaumauga e aumai ai fesuiaiga mai le /etc/wal-gd/server-s3.conf faila.

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

Fa'apipi'i wal-g.

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

Siaki le fa'asologa o wal-g.

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

Fa'atonu /etc/wal-gd/server-s3.conf e fetaui ma ou mana'oga.

Fa'atonu faila ma faila fa'amaumauga o lo'o fa'aogaina e se fa'amaumauga tu'ufa'atasiga e masani ona teuina fa'atasi i totonu o le lisi o fa'amaumauga o fa'amaumauga, e masani ona ta'ua o 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 # Какой метод сжатия использовать.

Pe a setiina le WAL-G, e te faʻamaonia WALG_DELTA_MAX_STEPS - o le numera o laʻasaga e sili atu le mamao o le faʻamaumauga o le delta mai le faʻamaumauga autu, ma faʻamaonia le tulafono kopi kopi. Pe e te faia se kopi mai le delta mulimuli o iai, pe e te faia se delta mai le uluai kopi atoa. E manaʻomia lenei mea mo le tulaga pe a suia i taimi uma le vaega faʻamaumauga tutusa i totonu o lau faʻamaumauga, o faʻamaumauga tutusa e suia i taimi uma.

Fa'apipi'i le fa'amaumauga.

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

Se'i amatalia le fa'amaumauga.

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

Afai o lo'o e su'eina i luga ole 1 server, e mana'omia ona e toe fetu'una'i le wal_level parameter e teu ai mo le PostgreSQL version itiiti ifo i le 10, ma fa'atusa mo PostgreSQL version 10 ma le matua.

wal_level = archive

Sei o tatou faaleoleo WAL archives uma 60 sekone e faaaoga PostgreSQL lava ia. I le gaosiga o le ae maua se isi archive_timeout tau.

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

Tatou amata PostgreSQL

systemctl start postgresql-9.6

I se faʻamafanafanaga ese, matou te vaʻavaʻai i ogalaau PostgreSQL mo mea sese: (suia postgresql-Wed.log i le taimi nei).

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

Tatou o i le psql.

su - postgres
psql

Matou te fatuina se faʻamaumauga i le psql.

Fausia se laulau i le test1 database.

create database test1;

Su'e ile su'ega fa'amaumauga.

postgres=# c test1;

Fausia se laulau indexing_table.

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

Fa'aopoopoina fa'amaumauga.

Tatou amata fa'aofi fa'amaumauga. Matou te faʻatali mo le 10-20 minute.

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

Ia mautinoa e fai se sao atoatoa.

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

Matou te vaʻavaʻai i faʻamaumauga i le laulau i le test1 database

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+

O le manoa o le taimi nei.

Matou te tilotilo i le lisi o faʻamaumauga atoa

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

Su'ega toe fa'aleleia

Toe fa'aleleia atoatoa ma ta'avale uma WAL avanoa.

Taofi Postgresql.

Matou te tape mea uma mai le /var/lib/pgsql/9.6/data folder.

Fa'asolo le tusitusiga /usr/local/bin/backup-fetch.sh mai le tagata fa'aoga postgres.

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

Ua mae'a le fa'asaoina.

Fa'aopoopo le recovery.conf ile /var/lib/pgsql/9.6/data folder ma mea nei.

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

Tatala PostgreSQL. PostgreSQL o le a faʻalauiloa le faagasologa o le toe faʻaleleia mai le WAL faʻamaumauga, ma naʻo le taimi lava e tatala ai le database.

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

Toe faʻaleleia mo se taimi patino.

Afai matou te mananaʻo e toe faʻafoʻisia le faʻamaumauga i se minute patino, ona matou faʻaopoopoina lea o le recovery_target_time parameter i recovery.conf - matou te faʻaalia le taimi e toe faʻaleleia ai le database.

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

A maeʻa ona toe faʻaleleia, vaʻai ile laulau 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

Tatala PostgreSQL. PostgreSQL o le a faʻalauiloa le faagasologa o le toe faʻaleleia mai le WAL faʻamaumauga, ma naʻo le taimi lava e tatala ai le database.

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

Suʻega

Matou te fatuina se 1GB faʻamaumauga e pei ona faʻamatalaina iinei https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff

Matou te talosagaina le tele o pakete pe a uma ona gaosia le 1GB o faʻamaumauga.

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

s4cmd — бесплатный инструмент командной строки для работы с данными, расположенными в хранилище Amazon S3. Утилита написана на языке программирования python, и благодаря этому может использоваться в операционных системах и Windowsma Linux.

Faʻapipiʻi le 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

Fa'atusatusaga o fa'ai'uga ile kalafi.