I-ProHoster > Π‘Π»ΠΎΠ³ > Ukuphatha > Isingeniso sesistimu yokusekelayo ye-wal-g PostgreSQL

Isingeniso sesistimu yokusekelayo ye-wal-g PostgreSQL

I-WAL-G iyithuluzi elilula nelisebenzayo lokusekela i-PostgreSQL emafini. Ngokuphathelene nokusebenza kwayo okuyinhloko, iyindlalifa yethuluzi elidumile WAL-E, kodwa ibhalwe kabusha kokuthi Hamba. Kepha kunesici esisha esibalulekile kumakhophi we-WAL-G - delta. amakhophi e-delta I-WAL-G gcina amakhasi wamafayela ashintshile kusukela enguqulweni yangaphambili yokusekelayo. I-WAL-G isebenzisa ubuchwepheshe obuningi bokuqhathanisa izipele. I-WAL-G ishesha kakhulu kune-WAL-E.

Imininingwane yokuthi i-wal-g isebenza kanjani ingatholakala esihlokweni: Sidlula isipele. Isifundo se-Yandex

Iphrothokholi yesitoreji se-S3 isidumile ekugcineni idatha. Enye yezinzuzo ze-S3 yikhono lokufinyelela nge-API, evumela ukuthi uhlele ukuxhumana okuguquguqukayo nesitoreji, okuhlanganisa ukufinyelela kokufunda komphakathi, kuyilapho ukubuyekezwa kolwazi kusitoreji kwenzeka kuphela ngabantu abagunyaziwe.

Kunokusetshenziswa kwesitoreji esisesidlangalaleni nesiyimfihlo esisebenzisa iphrothokholi ye-S3. Namuhla sizobheka isisombululo esithandwayo sokuhlela isitoreji esincane - i-Minio.

Iseva eyodwa ye-PostgreSQL ilungele ukuhlola i-wal-g, futhi i-Minio isetshenziswa esikhundleni se-S3.

Iseva encane

Ukufakwa okuncane

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

Hlela i-AccessKey kanye ne-SecretKey ku-/etc/minio/minio.conf

vi /etc/minio/minio.conf

Uma ungeke usebenzise i-nginx ngaphambi kwe-Minio, kuzomele ushintshe

--address 127.0.0.1:9000

--address 0.0.0.0:9000

Kwethulwa i-Minio

systemctl start minio

Iya ku-Minio web interface http://ip-адрСс-сСрвСра-minio:9000 bese udala ibhakede (isibonelo, pg-backups).

Iseva ye-DB

I-WAL-G ku-rpm ihlanganiswe yimi (u-Anton Patsev). I-Github, I-Fedora COPR.

Ubani ongenalo uhlelo olusekelwe ku-RPM, sebenzisa isikhulu imiyalelo ngokufaka.

Kanye ne-wal-g kanambambili, i-rpm iqukethe imibhalo engenisa okuguquguqukayo ukusuka kufayela /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

Faka i-walg.

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

Ihlola inguqulo ye-wal-g.

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

Hlela /etc/wal-gd/server-s3.conf kuzidingo zakho.

Amafayela okumisa namafayela edatha asetshenziswa iqoqo lesizindalwazi agcinwa ndawonye kuhla lwemibhalo lweqoqo, oluvame ukubizwa ngokuthi 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 # Какой ΠΌΠ΅Ρ‚ΠΎΠ΄ сТатия ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ.

Lapho ulungiselela i-WAL-G, ucacisa i-WALG_DELTA_MAX_STEPS - inombolo yezinyathelo isipele se-delta esiphezulu esisuka kukhophi eyisisekelo, futhi ucacise inqubomgomo yekhophi ye-delta. Kuphakathi kokuthi wenza ikhophi ku-delta yokugcina ekhona, noma wenza i-delta kusuka kusipele esigcwele sangempela. Lokhu kuyadingeka uma kwenzeka ingxenye efanayo yesizindalwazi ishintsha njalo kusizindalwazi sakho, idatha efanayo ishintsha njalo.

Ukufaka isizindalwazi.

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

Siqalisa i-database.

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

Uma uhlola kuseva engu-1, kuzomele ulungise kabusha ipharamitha ye-wal_level ukuze igcine kungobo yomlando ye-PostgreSQL ngaphansi kwenguqulo 10, kanye nekhophi yenguqulo ye-PostgreSQL 10 nangaphezulu.

wal_level = archive

Masigcine izingobo zomlando ze-WAL njalo ngemizuzwana engama-60 sisebenzisa i-PostgreSQL ngokwayo. Ku-prod, uzoba nenani elihlukile le-archive_timeout.

archive_mode = on
archive_command = '/usr/local/bin/wal-push.sh %p'
archive_timeout = 60 # ΠšΠ°ΠΆΠ΄Ρ‹Π΅ 60 сСкунд Π±ΡƒΠ΄Π΅Ρ‚ выполнятся ΠΊΠΎΠΌΠ°Π½Π΄Π° archive_command.

Iqala i-PostgreSQL

systemctl start postgresql-9.6

Kwikhonsoli ehlukile, sibheka amalogi e-PostgreSQL ngamaphutha: (shintsha i-postgresql-Wed.log ibe kweyamanje).

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

Asiye ku-psql.

su - postgres
psql

Dala i-database ku-psql

Dala ithebula ku-test yesizindalwazi1.

create database test1;

Shintshela ekuhlolweni kwesizindalwazi.

postgres=# c test1;

Sakha ithebula indexing_table.

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

Yengeza idatha.

Siqala ukufaka idatha. Silinde imizuzu engu-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

Qiniseka ukuthi wenza isipele esigcwele.

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

Sibheka amarekhodi kuthebula ku-test1 yesizindalwazi

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+

Iyunithi yezinhlamvu isikhathi samanje.

Bona uhlu lwezipele ezigcwele

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

Ukuhlolwa kokululama

Ukuthola kabusha okugcwele ngokugoqa yonke i-WAL etholakalayo.

Misa i-Postgresql.

Susa yonke into kufolda /var/lib/pgsql/9.6/data.

Qalisa /usr/local/bin/backup-fetch.sh iskripthi njengomsebenzisi we-postgres.

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

Ukukhipha ikhophi yasenqolobaneni kuqedile.

Engeza recovery.conf kufolda /var/lib/pgsql/9.6/data enokuqukethwe okulandelayo.

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

Siqala i-PostgreSQL. I-PostgreSQL izoqala inqubo yokutakula kuma-WAL afakwe kungobo yomlando, futhi yilapho kuphela isizindalwazi sizovuleka. 

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

Ukubuyisela isikhathi esithile.

Uma sifuna ukubuyisela i-database kuze kube umzuzu othile, bese sengeza ipharamitha ye-recovery_target_time ku-recovery.conf - sibonisa ukuthi yisiphi isikhathi sokubuyisela i-database.

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

Ngemva kokululama, bheka ithebula elithi 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

Siqala i-PostgreSQL. I-PostgreSQL izoqala inqubo yokutakula kuma-WAL afakwe kungobo yomlando, futhi yilapho kuphela isizindalwazi sizovuleka. 

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

Ukuhlola

Ikhiqiza i-database engu-1GB njengoba kuchazwe lapha https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff

Icela usayizi webhakede ngemva kokukhiqiza idatha engu-1GB.

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

I-s4cmd iyithuluzi lomugqa womyalo wamahhala lokusebenza nedatha ehlala kwisitoreji se-Amazon S3. Insiza ibhalwe ngolimi lokuhlela lwe-python, futhi ngenxa yalokhu ingasetshenziswa kuzo zombili izinhlelo zokusebenza zeWindows neLinux.

Ifaka i-s4cmd

pip install s4cmd

I-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 ΠœΠ‘ Π·Π°Π½ΠΈΠΌΠ°Π΅Ρ‚ ΠΏΠΎΠ»Π½Ρ‹ΠΉ Π±Π΅ΠΊΠ°ΠΏ

I-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

U-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

Ukuqhathaniswa kwemiphumela eshadini.

Isingeniso sesistimu yokusekelayo ye-wal-g PostgreSQL

Njengoba ubona, i-Brotli iqhathaniswa ngosayizi ne-LZMA, kodwa isipele senziwa ngesikhathi se-LZ4.

Ingxoxo yomphakathi we-PostgreSQL okhuluma isiRashiya: https://t.me/pgsql

Sicela unikeze inkanyezi ku-Github uma usebenzisa wal-g

Source: www.habr.com

Engeza amazwana