🥇 Ṣafihan eto afẹyinti wal-g PostgreSQL

WAL-G jẹ ohun elo ti o rọrun ati ti o munadoko fun atilẹyin PostgreSQL si awọn awọsanma. Ni awọn ofin ti iṣẹ akọkọ rẹ, o jẹ arole si irinṣẹ olokiki WAL-E, ṣugbọn tun kọ ni Go. Ṣugbọn ẹya tuntun pataki kan wa ni WAL-G - awọn ẹda delta. delta idaako WAL-G tọju awọn oju-iwe ti awọn faili ti o ti yipada lati ẹya afẹyinti ti tẹlẹ. WAL-G n ṣe ọpọlọpọ awọn imọ-ẹrọ pupọ fun isọdọkan awọn afẹyinti. WAL-G yiyara pupọ ju WAL-E lọ.

Awọn alaye ti bii wal-g ṣe le rii ninu nkan naa: A overclock awọn afẹyinti. Yandex ikowe

Ilana ipamọ S3 ti di olokiki fun titoju data. Ọkan ninu awọn anfani ti S3 ni agbara lati wọle si nipasẹ API, eyiti o fun ọ laaye lati ṣeto ibaraenisepo rọ pẹlu ibi ipamọ, pẹlu iwọle kika ti gbogbo eniyan, lakoko ti imudojuiwọn alaye ninu ibi ipamọ waye nikan nipasẹ awọn eniyan ti a fun ni aṣẹ.

Ọpọlọpọ awọn imuṣẹ ibi ipamọ gbogbo eniyan ati ikọkọ lo wa ti o lo ilana S3. Loni a yoo wo ojutu olokiki fun siseto ibi ipamọ kekere - Minio.

Olupin PostgreSQL kan dara fun idanwo wal-g, ati pe a lo Minio bi rirọpo fun S3.

Minio Server

Fifi sori ẹrọ Minio

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

Ṣatunkọ AccessKey ati SecretKey ni /etc/minio/minio.conf

vi /etc/minio/minio.conf

Ti o ko ba lo nginx ṣaaju Minio, lẹhinna o nilo lati yipada

--address 127.0.0.1:9000

--address 0.0.0.0:9000

Ifilọlẹ Minio

systemctl start minio

Lọ si oju opo wẹẹbu Minio http://ip-адрес-сервера-minio:9000 ki o si ṣẹda garawa kan (fun apẹẹrẹ, pg-backups).

DB olupin

WAL-G ni rpm ti wa ni apejọ nipasẹ mi (Anton Patsev). Github, Fedora COPR.

Tani ko ni eto orisun RPM, lo osise naa ilana nipa fifi sori.

Paapọ pẹlu alakomeji wal-g, rpm ni awọn iwe afọwọkọ ti o gbe awọn oniyipada wọle lati faili /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

Fi sori ẹrọ walg.

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

Ṣiṣayẹwo ẹya wal-g.

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

Ṣatunkọ /etc/wal-gd/server-s3.conf si awọn aini rẹ.

Awọn faili iṣeto ni ati awọn faili data ti a lo nipasẹ iṣupọ data jẹ ti aṣa ti o fipamọ papo sinu iwe ilana data iṣupọ, ti a tọka si bi 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 # Какой метод сжатия использовать.

Nigbati o ba tunto WAL-G, o pato WALG_DELTA_MAX_STEPS - nọmba awọn igbesẹ ti afẹyinti delta ti o pọju lati ipilẹ afẹyinti, ati pato ilana ẹda delta. Boya o ṣe ẹda kan lati awọn ti o kẹhin tẹlẹ delta, tabi ti o ṣe a delta lati atilẹba ni kikun afẹyinti. Eyi jẹ pataki ni ọran nigbati paati kanna ti data data nigbagbogbo n yipada ninu data data rẹ, data kanna n yipada nigbagbogbo.

Fifi sori ẹrọ 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

A bẹrẹ ipilẹ data.

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

Ti o ba n ṣe idanwo lori olupin 1, lẹhinna o nilo lati tunto paramita wal_level lati ṣe ifipamọ fun PostgreSQL ti o kere ju ẹya 10, ati ẹda fun ẹya PostgreSQL 10 ati agbalagba.

wal_level = archive

Jẹ ki a ṣe afẹyinti awọn ile-ipamọ WAL ni gbogbo iṣẹju 60 ni lilo PostgreSQL funrararẹ. Lori prod, iwọ yoo ni iye archive_timeout ti o yatọ.

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

Bibẹrẹ PostgreSQL

systemctl start postgresql-9.6

Ninu console lọtọ, a wo awọn akọọlẹ PostgreSQL fun awọn aṣiṣe: (ayipada postgresql-Wed.log si eyi ti o wa lọwọlọwọ).

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

Jẹ ki a lọ si psql.

su - postgres
psql

Ṣẹda database ni psql

Ṣẹda tabili kan ninu idanwo data1.

create database test1;

Yipada si igbeyewo database.

postgres=# c test1;

A ṣẹda tabili indexing_tabili.

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

Fifi data.

A bẹrẹ fifi data sii. A n duro fun awọn iṣẹju 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

Rii daju lati ṣe afẹyinti ni kikun.

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

A wo awọn igbasilẹ ninu tabili ni idanwo 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+

Okun naa jẹ akoko lọwọlọwọ.

Wo atokọ ti awọn afẹyinti kikun

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

Idanwo imularada

Imularada ni kikun pẹlu yiyi gbogbo WAL ti o wa.

Duro Postgresql.

Pa ohun gbogbo rẹ kuro ninu folda /var/lib/pgsql/9.6/data.

Ṣiṣe awọn /usr/local/bin/backup-fetch.sh script bi olumulo postgres.

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

Iyọkuro afẹyinti ti pari.

Ṣafikun recovery.conf si folda /var/lib/pgsql/9.6/data pẹlu akoonu atẹle.

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

A bẹrẹ PostgreSQL. PostgreSQL yoo bẹrẹ ilana imularada lati awọn WAL ti o wa ni ipamọ, ati pe lẹhinna nikan ni data data yoo ṣii.

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

Imularada fun akoko kan.

Ti a ba fẹ mu pada data pada titi di iṣẹju kan, lẹhinna a ṣafikun paramita recovery_target_time si recovery.conf - a fihan ni akoko wo lati mu data data pada.

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

Lẹhin imularada, wo tabili indexing_tabili

 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

A bẹrẹ PostgreSQL. PostgreSQL yoo bẹrẹ ilana imularada lati awọn WAL ti o wa ni ipamọ, ati pe lẹhinna nikan ni data data yoo ṣii.

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

Igbeyewo

Ṣiṣẹda data data 1GB gẹgẹbi a ti ṣalaye nibi https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff

Nbeere iwọn garawa lẹhin ti ipilẹṣẹ 1GB ti data.

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

s4cmd jẹ ọpa laini aṣẹ ọfẹ fun ṣiṣẹ pẹlu data ti n gbe ni ibi ipamọ Amazon S3. IwUlO ti kọ ni ede siseto Python, ati nitori eyi o le ṣee lo ni awọn ọna ṣiṣe Windows ati Lainos.

Fifi sori ẹrọ 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

Ifiwera awọn abajade lori chart.

Bi o ti le rii, Brotli jẹ afiwera ni iwọn si LZMA, ṣugbọn afẹyinti ṣe ni akoko LZ4.

Iwiregbe ti agbegbe PostgreSQL ti n sọ Russian: https://t.me/pgsql

Jọwọ fun Github irawọ kan ti o ba lo wal-g

orisun: www.habr.com

Ifihan si eto afẹyinti wal-g PostgreSQL