🥇Qhia cov wal-g PostgreSQL thaub qab qhov system

WAL-G yog ib qho yooj yim thiab siv tau zoo rau thaub qab PostgreSQL rau huab. Nyob rau hauv cov nqe lus ntawm nws lub ntsiab functionality, nws yog tus txais los ntawm cov cuab yeej nrov WAL-E, tab sis rov sau dua hauv Go. Tab sis muaj ib qho tseem ceeb tshiab hauv WAL-G - delta luam. delta cov ntawv luam WAL-G khaws cov nplooj ntawv ntawm cov ntaub ntawv uas tau hloov txij li yav dhau los thaub qab version. WAL-G siv ntau cov thev naus laus zis rau kev sib piv cov thaub qab. WAL-G nrawm dua WAL-E.

Cov ntsiab lus ntawm yuav ua li cas wal-g ua haujlwm tuaj yeem pom hauv kab lus: Peb overclock tus thaub qab. Yandex lus qhuab qhia

S3 cia raws tu qauv tau ua nrov rau khaws cov ntaub ntawv. Ib qho ntawm qhov zoo ntawm S3 yog qhov muaj peev xwm nkag tau los ntawm API, uas tso cai rau koj los teeb tsa kev hloov pauv hloov pauv nrog kev khaws cia, suav nrog kev nkag mus rau pej xeem, thaum hloov kho cov ntaub ntawv hauv qhov chaw cia tsuas yog los ntawm cov neeg muaj cai.

Muaj ntau qhov kev siv rau pej xeem thiab ntiag tug uas siv S3 raws tu qauv. Niaj hnub no peb yuav saib cov kev daws teeb meem nrov rau kev teeb tsa me me cia - Minio.

Ib tus neeg rau zaub mov PostgreSQL ib leeg zoo rau kev sim wal-g, thiab Minio yog siv los hloov S3.

Minio Server

Minio installation

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

Hloov AccessKey thiab SecretKey hauv /etc/minio/minio.conf

vi /etc/minio/minio.conf

Yog tias koj yuav tsis siv nginx ua ntej Minio, ces koj yuav tsum tau hloov

--address 127.0.0.1:9000

--address 0.0.0.0:9000

Launching Minio

systemctl start minio

Mus rau Minio web interface http://ip-адрес-сервера-minio:9000 thiab tsim ib lub thoob (piv txwv li, pg-backups).

DB server

WAL-G hauv rpm yog sib sau ua ke los ntawm kuv (Anton Patsev). github, Fedora COPR.

Leej twg tsis muaj RPM-raws li system, siv lub official cov lus qhia los ntawm installation.

Nrog rau wal-g binary, rpm muaj cov ntawv sau uas tuaj yeem hloov pauv los ntawm cov ntaub ntawv /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

Nruab walg.

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

Tshawb xyuas wal-g version.

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

Kho kom raug /etc/wal-gd/server-s3.conf rau koj xav tau.

Cov ntaub ntawv teeb tsa thiab cov ntaub ntawv cov ntaub ntawv siv los ntawm pawg database yog ib txwm khaws cia ua ke hauv pawg cov ntaub ntawv teev npe, feem ntau hu ua 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 # Какой метод сжатия использовать.

Thaum configuring WAL-G, koj teev WALG_DELTA_MAX_STEPS - tus naj npawb ntawm cov kauj ruam uas lub delta backup yog siab tshaj plaws los ntawm lub hauv paus backup, thiab qhia kom meej cov delta daim ntawv txoj cai. Txawm hais tias koj ua ib daim ntawv luam los ntawm qhov kawg delta uas twb muaj lawm, los yog koj ua ib tug delta los ntawm tus thawj tag nrho thaub qab. Qhov no yog qhov tsim nyog nyob rau hauv rooj plaub thaum tib lub ntsiab lus ntawm cov ntaub ntawv ib txwm hloov pauv hauv koj lub database, tib cov ntaub ntawv hloov pauv tas li.

Txhim kho lub 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

Peb pib lub database.

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

Yog tias koj tab tom sim ntawm 1 server, tom qab ntawd koj yuav tsum rov teeb tsa lub wal_level parameter rau archive rau PostgreSQL tsawg dua version 10, thiab replica rau PostgreSQL version 10 thiab laus dua.

wal_level = archive

Cia peb thaub qab WAL archives txhua 60 vib nas this siv PostgreSQL nws tus kheej. Ntawm prod, koj yuav muaj tus nqi sib txawv archive_timeout.

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

Pib PostgreSQL

systemctl start postgresql-9.6

Hauv ib lub console cais, peb saib PostgreSQL cav rau qhov yuam kev: (hloov postgresql-Wed.log rau qhov tam sim no).

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

Wb mus rau psql.

su - postgres
psql

Tsim ib lub database hauv psql

Tsim ib lub rooj nyob rau hauv qhov kev xeem database1.

create database test1;

Hloov mus rau qhov kev xeem database.

postgres=# c test1;

Peb tsim lub rooj indexing_table.

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

Ntxiv cov ntaub ntawv.

Peb pib ntxig cov ntaub ntawv. Peb tos 10-20 feeb.

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

Nco ntsoov ua kom tag nrho thaub qab.

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

Peb saib cov ntaub ntawv nyob rau hauv lub rooj nyob rau hauv lub database test1

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+

Txoj hlua yog lub sijhawm tam sim no.

Saib daim ntawv teev cov thaub qab tag nrho

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

Kev ntsuam xyuas rov qab

Tag nrho rov qab nrog dov tag nrho cov muaj WAL.

Nres Postgresql.

Rho tawm txhua yam ntawm /var/lib/pgsql/9.6/data folder.

Khiav lub /usr/local/bin/backup-fetch.sh tsab ntawv raws li tus neeg siv postgres.

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

Backup extraction tiav.

Ntxiv recovery.conf rau /var/lib/pgsql/9.6/data folder nrog cov ntsiab lus hauv qab no.

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

Peb pib PostgreSQL. PostgreSQL yuav pib cov txheej txheem rov qab los ntawm WALs archived, thiab tsuas yog tom qab ntawd cov ntaub ntawv yuav qhib.

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

Rov qab rau ib lub sijhawm.

Yog tias peb xav rov qab kho cov ntaub ntawv mus txog ib feeb, ces peb ntxiv qhov rov qab_target_time parameter rau recovery.conf - peb qhia lub sijhawm twg los kho cov ntaub ntawv.

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

Tom qab rov qab, saib lub rooj 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

Peb pib PostgreSQL. PostgreSQL yuav pib cov txheej txheem rov qab los ntawm WALs archived, thiab tsuas yog tom qab ntawd cov ntaub ntawv yuav qhib.

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

Kev Ntsuam Xyuas

Tsim 1GB database raws li tau piav qhia ntawm no https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff

Thov lub thoob loj tom qab tsim 1GB ntawm cov ntaub ntawv.

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

s4cmd yog cov cuab yeej hais kom pub dawb rau kev ua haujlwm nrog cov ntaub ntawv nyob hauv Amazon S3 cia. Cov nqi hluav taws xob tau sau ua lus python programming, thiab vim li no nws tuaj yeem siv tau hauv Windows thiab Linux operating systems.

Txhim kho 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

Kev sib piv ntawm cov txiaj ntsig ntawm daim duab.

Raws li koj tuaj yeem pom, Brotli piv rau qhov loj me rau LZMA, tab sis cov thaub qab tau ua hauv LZ4 lub sijhawm.

Sib tham ntawm lub zej zog hais lus Lavxias teb sab PostgreSQL: https://t.me/pgsql

Thov muab lub hnub qub rau Github yog tias koj siv wal-g

Tau qhov twg los: www.hab.com

Taw qhia rau wal-g PostgreSQL backup system