🥇Introductio ad wal-g PostgreSQL tergum system

WAL-G est simplex et efficax instrumentum ad tergum sursum PostgreSQL ad nubes. Secundum principalem functionem eius heres est instrumenti popularis WAL-Esed fessa in Go. Sed in WA-G-G-delta exemplaribus nova quaedam momenti est. della copies WAL-G reponunt paginas imaginum quae mutatae sunt cum versione priora tergum. WAL-G instrumentum technologiae satis multae ad tergum parallelismum. WAL-G multo velocius quam WAL-E.

Singula quomodo opera wal-g in articulo inveniri possunt: Overclock nos tergum. Yandex lecture

S3 protocollum repositionis popularis facta est ad notitias reponendas. Una emolumentorum S3 facultas accessiendi per API est, quae te permittit ut flexibilem commercium cum repositione ordinare, inter publica lege aditum, dum adaequationis informationes in repositione nonnisi ab auctoritate personarum occurrit.

Plures sunt exsecutiones tabulariorum publicarum et privatarum quae protocollo S3 utuntur. Hodie spectabimus solutionem popularem pro parva repositione ordinanda - Minio.

Unicus PostgreSQL ministrator tenuis est ad probandum wal-g, et Minio subrogandi S3 adhibetur.

Minio Servo

Minio institutionem

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

Edit AccessKey et SecretKey in /etc/minio/minio.conf

vi /etc/minio/minio.conf

Si nginx ante Minio non uteris, debes mutare

--address 127.0.0.1:9000

--address 0.0.0.0:9000

Deductis Minio

systemctl start minio

Vade ad telam interface Minio http://ip-адрес-сервера-minio:9000 et situlam crea (exempli gratia, pg-backups).

DB servo

WAL-G In rpm a me collecta (Anton Patsev). Github, Fedora COPR.

Qui RPM-fundatur ratio non habet, officiali utere instructiones per institutionem.

Una cum binario wal-g, rpm scripta continet quae variabiles ex fasciculo /etc/wal-gd/servo-s3.conf insunt.

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

Instrue walg.

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

Reperiens wal-g litera.

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

Edit /etc/wal-gd/server-s3.conf tuis necessitatibus.

Fasciculi configurationes et fasciculi notati, quos botrum datorum usitatum est, translaticio in botro notitiarum directorium reponuntur, ut vulgo dicitur 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 # Какой метод сжатия использовать.

Cum figurans WAL-G, WALG_DELTA_MAX_STEPS numerum denotas - numerus gradibus della tergum maximus est a basi tergum, et exemplar della forma designas. Aut exemplar e ultima della exsistente conficis, aut delta archetypo plena tergum conficis. Hoc necessarium est, si quando eadem pars datorum semper in datorum datorum mutatur, eadem notitia constanter mutatur.

Inaugurari datorum.

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

Nos database initialize.

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

Si in 1 servo probas, tunc debes parametrum wal_level pro archivo PostgreSQL in tabulario pro postgreSQL minus quam in versione 10 configurare, et replica pro versione PostgreSQL 10 et maiore.

wal_level = archive

Sit scriptor tergum WA tabularia singula 60 secundis utendo ipso PostgreSQL. In iaculo, aliud valorem archivum habebis.

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

Satus PostgreSQL

systemctl start postgresql-9.6

In separato console, tabulas postgreSQL errorum inspicimus: (mutatio postgresql-Wed.log ad currentem).

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

Eamus ad psql.

su - postgres
psql

Creare database in psql

Creare mensam in test1 datorum.

create database test1;

Vertas database test.

postgres=# c test1;

Tabulam creamus indexing_table.

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

Addito data.

Incipimus inserendo data. Exspectamus 10-20 minuta.

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

Fac ut sit plenum tergum.

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

Tabulas in tabula spectamus in 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+

chorda est tempus praesens.

Vide indicem plenum tergum

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

Recuperatio temptationis

Plena recuperatio cum volventibus omnibus praesto WAL.

Desine Postgresql.

Omnia delere ex /var/lib/pgsql/9.6/data folder.

Curre scripturam /usr/local/bin/backup-fetch.sh sicut postgres user.

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

Tergum extraction completum.

Adde recovery.conf ad /var/lib/pgsql/9.6/data folder cum sequenti contento.

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

Incipimus PostgreSQL. PostgreSQL processus recuperationis ex archivo WALS incipiet, et tunc demum aperietur datorum.

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

Convaluisset ad aliquod tempus.

Si velimus database usque ad aliquod minutum restituere, tunc addimus parametrum recuperandi_target_time ad convalescendum.conf - indicamus quo tempore database restituat.

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

Post recuperationem, vide mensam 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

Incipimus PostgreSQL. PostgreSQL processus recuperationis ex archivo WALS incipiet, et tunc demum aperietur datorum.

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

temptationis

Generans 1GB database de quo hic https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff

Postulantes situla magnitudinis 1GB ex data generans.

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

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

installing 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

Comparatio proventus chart.