🥇 Okwu mmalite nke wal-g PostgreSQL usoro ndabere

WAL-G bụ ngwa dị mfe ma dị irè maka ịkwado PostgreSQL na igwe ojii. N'ihe banyere isi ọrụ ya, ọ bụ onye nketa nke ngwá ọrụ a ma ama WAL-E, mana edegharịrị ya na Go. Mana enwere otu njirimara ọhụrụ dị mkpa na akwụkwọ WAL-G - delta. mbipụta delta WAL-G chekwaa ibe faịlụ ndị agbanweela kemgbe ụdị ndabere gara aga. WAL-G na-arụ ọrụ dị ukwuu nke teknụzụ maka imekota ndabere. WAL-G dị ngwa karịa WAL-E.

Enwere ike ịhụ nkọwa gbasara otu wal-g si arụ ọrụ n'ime edemede: Anyị gafere ndabere. Yandex nkuzi

Usoro nchekwa nchekwa S3 aghọwo ihe ewu ewu maka ịchekwa data. Otu n'ime uru nke S3 bụ ikike ịnweta site na API, nke na-enye gị ohere ịhazi mkparịta ụka na-agbanwe agbanwe na nchekwa ahụ, gụnyere ohere ịgụ ihe ọha na eze, ebe imelite ozi na nchekwa na-eme nanị site n'aka ndị ikike.

Enwere ọtụtụ mmemme nchekwa ọha na nkeonwe na-eji protocol S3. Taa, anyị ga-eleba anya na ngwọta na-ewu ewu maka ịhazi obere nchekwa - Minio.

Otu ihe nkesa PostgreSQL dị mma maka ịnwale wal-g, a na-ejikwa Minio dochie anya S3.

Minio nkesa

Nwụnye Minio

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

Dezie AccessKey na SecretKey na /etc/minio/minio.conf

vi /etc/minio/minio.conf

Ọ bụrụ na ịgaghị eji nginx tupu Minio, mgbe ahụ ịkwesịrị ịgbanwe

--address 127.0.0.1:9000

--address 0.0.0.0:9000

Na-amalite Minio

systemctl start minio

Gaa na interface weebụ Minio http://ip-адрес-сервера-minio:9000 ma mepụta ịwụ (dịka ọmụmaatụ, pg-backups).

sava DB

WAL-G na rpm bụ m (Anton Patsev) gbakọtara. Github, Ọnụ ego nke Fedora COPR.

Onye na-enweghị usoro dabere na RPM, jiri onye ọrụ gọọmentị ntuziaka site echichi.

Tinyere ọnụọgụ wal-g, rpm nwere scripts na-ebubata mgbanwe site na faịlụ /etc/wal-gd/server-s3.conf.

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

Wụnye walg.

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

Na-enyocha ụdị wal-g.

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

Dezie /etc/wal-gd/server-s3.conf maka mkpa gị.

A na-echekwa faịlụ nhazi na faịlụ data nke ụyọkọ nchekwa data na-eji ọnụ na ndekọ ndekọ ụyọkọ, nke a na-akpọkarị 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 # Какой метод сжатия использовать.

Mgbe ị na-ahazi WAL-G, ị na-akọwapụta WALG_DELTA_MAX_STEPS - ọnụọgụ usoro nke ndabere delta kacha si na ndabere ndabere, wee kọwapụta amụma nnomi delta. Ma ọ bụ na ị na-eme otu n'ime delta ikpeazụ dị adị, ma ọ bụ ị na-eme delta site na nkwado ndabere zuru oke nke mbụ. Nke a dị mkpa ma ọ bụrụ na otu akụkụ nke nchekwa data na-agbanwe mgbe niile na nchekwa data gị, otu data ahụ na-agbanwe mgbe niile.

Ịwụnye nchekwa data.

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

Anyị na-amalite nchekwa data.

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

Ọ bụrụ na ị na-anwale na ihe nkesa 1, mgbe ahụ ịkwesịrị ịhazigharị paramita wal_level ka ọ bụrụ ebe nchekwa maka PostgreSQL na-erughị ụdị 10, yana oyiri maka ụdị PostgreSQL 10 na karịa.

wal_level = archive

Ka anyị na-echekwa ebe nchekwa WAL kwa sekọnd 60 site na iji PostgreSQL n'onwe ya. Na prod, ị ga-enwe uru archive_timeout dị iche.

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

Malite PostgreSQL

systemctl start postgresql-9.6

Na njikwa dị iche, anyị na-elele ndekọ PostgreSQL maka njehie: (gbanwee postgresql-Wed.log na nke dị ugbu a).

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

Ka anyị gaa na psql.

su - postgres
psql

Mepụta nchekwa data na psql

Mepụta tebụl n'ime ule nchekwa data1.

create database test1;

Banye na ule nchekwa data.

postgres=# c test1;

Anyị na-emepụta table indexing_table.

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

Na-agbakwụnye data.

Anyị na-amalite itinye data. Anyị na-eche 10-20 nkeji.

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

Jide n'aka na ị ga-eme nkwado ndabere zuru oke.

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

Anyị na-elele ihe ndekọ dị na tebụl na ule nchekwa data1

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+

Eriri bụ oge ugbu a.

Hụ ndepụta nkwado ndabere zuru oke

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

Nnwale mgbake

mgbake zuru oke yana ịgbagharị WAL niile dị.

Kwụsị Postgresql.

Hichapụ ihe niile na /var/lib/pgsql/9.6/data nchekwa.

Gbaa ederede /usr/local/bin/backup-fetch.sh dị ka onye ọrụ postgres.

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

Mwepụta ndabere zuru ezu.

Tinye recovery.conf na /var/lib/pgsql/9.6/data nchekwa yana ọdịnaya ndị a.

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

Anyị na-amalite PostgreSQL. PostgreSQL ga-amalite usoro mgbake site na WAL nke echekwara, naanị mgbe ahụ ka nchekwa data ga-emeghe.

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

Iweghachite maka oge ụfọdụ.

Ọ bụrụ na anyị chọrọ iweghachi nchekwa data ahụ ruo oge ụfọdụ, mgbe ahụ, anyị na-agbakwụnye paramita recovery_target_time na recovery.conf - anyị na-egosi oge ọ ga-eweghachi nchekwa data.

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

Mgbe mgbake gasịrị, lelee tebụl 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

Anyị na-amalite PostgreSQL. PostgreSQL ga-amalite usoro mgbake site na WAL nke echekwara, naanị mgbe ahụ ka nchekwa data ga-emeghe.

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

Ule

Ịmepụta nchekwa data 1GB dịka akọwara ebe a https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff

Na-arịọ nha ịwụ mgbe ị nwetachara 1GB data.

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

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

Ịwụnye 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

Ntụle nsonaazụ na eserese ahụ.