Faahfaahinta sida wal-g u shaqeyso waxaa laga heli karaa maqaalka:
Habka kaydinta S3 ayaa caan ku noqday kaydinta xogta. Mid ka mid ah faa'iidooyinka S3 waa awoodda lagu galo API, taas oo kuu ogolaanaysa inaad abaabusho isdhexgalka dabacsan ee kaydinta, oo ay ku jiraan akhrinta dadweynaha, halka cusboonaysiinta macluumaadka kaydinta ay ku dhacdo kaliya shakhsiyaadka idman.
Waxa jira dhawr hirgelin oo kaydinta guud iyo kuwa gaarka ah oo adeegsada borotokoolka S3. Maanta waxaan eegi doonaa xal caan ah oo loogu talagalay abaabulka kaydinta yaryar - Minio.
Hal server PostgreSQL ayaa ku fiican tijaabinta wal-g, iyo Minio waxaa loo isticmaalaa bedelka S3.
Server-ka Minio
Rakibaadda Minio
yum -y install yum-plugin-copr
yum copr enable -y lkiesow/minio
yum install -y minio
Wax ka beddel AccessKey iyo SecretKey gudaha /etc/minio/minio.conf
vi /etc/minio/minio.conf
Haddii aadan isticmaali doonin nginx ka hor Minio, markaa waxaad u baahan tahay inaad bedesho
--address 127.0.0.1:9000
--address 0.0.0.0:9000
Bilaabida Minio
systemctl start minio
Tag interface webka Minio
Adeegga DB
WAL-G ee ku jira rpm waxaa soo ururiyay aniga (Anton Patsev).
Yaa aan lahayn nidaam ku salaysan RPM, isticmaal kan rasmiga ah
Marka lagu daro wal-g binary, rpm waxaa ku jira qoraallo ka soo dejinaya doorsoomayaasha faylka /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
Ku rakib walg.
yum -y install yum-plugin-copr
yum copr enable -y antonpatsev/wal-g
yum install -y wal-g
Hubinta nooca wal-g
wal-g --version
wal-g version v0.2.14
Wax ka beddel /etc/wal-gd/server-s3.conf baahiyahaaga.
Faylasha qaabeynta iyo faylalka xogta ay adeegsato kutlada keydka xogta ayaa si caadi ah loogu kaydiyaa tusaha xogta kooxda, sida caadiga ah loo tixraaco sida 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 # ΠΠ°ΠΊΠΎΠΉ ΠΌΠ΅ΡΠΎΠ΄ ΡΠΆΠ°ΡΠΈΡ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ.
Markaad habaynayso WAL-G, waxaad cadaysaa WALG_DELTA_MAX_STEPS - tirada tillaabooyinka ay kaydinta delta ugu badan tahay kaydka saldhiga, oo cadee siyaasada nuqulka delta. Ama waxaad nuqul ka sameysaa delta ugu dambeysay, ama waxaad ka sameysaa delta kaydkii asalka ahaa ee buuxa. Tani waa lagama maarmaan haddii ay dhacdo in qaybta isku midka ah ee kaydinta ay had iyo jeer isbeddelayso xogtaada, isla xogta ayaa si joogto ah u beddesha.
Ku rakibida xogta xogta
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
Waxaan bilownay keydka xogta
/usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK
Haddii aad ku tijaabinayso 1 server, markaa waxaad u baahan tahay inaad dib u habayn ku sameyso cabirka wal_level si aad u kaydiso PostgreSQL oo ka yar nooca 10, oo aad nuqul ka sameyso nooca PostgreSQL 10 iyo ka weyn.
wal_level = archive
Aynu kayd ka dhigno kaydka WAL 60kii sekan kasta anagoo adeegsanayna PostgreSQL lafteeda. Soo saarista, waxaad yeelan doontaa qiime ka duwan kaydka_timeout
archive_mode = on
archive_command = '/usr/local/bin/wal-push.sh %p'
archive_timeout = 60 # ΠΠ°ΠΆΠ΄ΡΠ΅ 60 ΡΠ΅ΠΊΡΠ½Π΄ Π±ΡΠ΄Π΅Ρ Π²ΡΠΏΠΎΠ»Π½ΡΡΡΡ ΠΊΠΎΠΌΠ°Π½Π΄Π° archive_command.
Bilaabida PostgreSQL
systemctl start postgresql-9.6
Koonsole gaar ah, waxaan ku eegnaa diiwaanka PostgreSQL ee khaladaadka: ( u beddel postgresql-Wed.log kan hadda jira).
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
Aan aado psql.
su - postgres
psql
Ku samee xog-ururinta psql
Samee miis gudaha imtixaanka xogta xogta1.
create database test1;
U beddelo imtixaanka keydka macluumaadka.
postgres=# c test1;
Waxaan abuurnaa miiska indexing_miiska.
test1=# CREATE TABLE indexing_table(created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());
Ku darida xogta
Waxaan bilaabeynaa gelin gelinta xogta. Waxaan sugeynaa 10-20 daqiiqo.
#!/bin/bash
# postgres
while true; do
psql -U postgres -d test1 -c "INSERT INTO indexing_table(created_at) VALUES (CURRENT_TIMESTAMP);"
sleep 60;
done
Hubi inaad samayso gurmad buuxa.
su - postgres
/usr/local/bin/backup-push.sh
Waxaan eegnaa diiwaanada ku jira shaxda ee imtixaanka xogta macluumaadka1
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+
Xadhiggu waa waqtiga hadda.
Eeg liiska kaydinta buuxa
/usr/local/bin/backup-list.sh
Tijaabada soo kabashada
Soo kabsasho buuxda iyadoo la duubayo dhammaan WAL la heli karo.
Jooji Postgresql.
Ka tirtir wax walba /var/lib/pgsql/9.6/galka xogta.
Ku socodsii qoraalka /usr/local/bin/backup-fetch.sh sida isticmaale postgres.
su - postgres
/usr/local/bin/backup-fetch.sh
Soo saarista kaabta waa dhammaatay
Ku dar recovery.conf galka /var/lib/pgsql/9.6/data oo leh waxyaabaha soo socda.
restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'
Waxaan bilaabeynaa PostgreSQL. PostgreSQL waxay ka bilaabi doontaa habka soo kabashada ee WALs ee kaydsan, ka dibna kaliya ayaa kaydka xogta furmi doona.
systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
Soo kabashada wakhti go'an.
Haddii aan rabno in aan soo celinno xogta illaa daqiiqad gaar ah, ka dib waxaan ku darnaa halbeegga recovery_target_time ee recovery.conf - waxaan tilmaamaynaa waqtiga lagu soo celinayo xogta.
restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'
recovery_target_time = '2020-01-29 09:46:25'
Soo kabashada ka dib, eeg miiska indexing_miiska
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
Waxaan bilaabeynaa PostgreSQL. PostgreSQL waxay ka bilaabi doontaa habka soo kabashada ee WALs ee kaydsan, ka dibna kaliya ayaa kaydka xogta furmi doona.
systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
Imtixaanka
Abuuritaanka kayd 1GB ah sida halkan lagu sharaxay
Codsashada cabbirka baaldiga ka dib soo saarista 1GB ee xogta.
postgres=# SELECT pg_size_pretty(pg_database_size('test1'));
pg_size_pretty
----------------
1003 MB
s4cmd waa khadka taliska bilaashka ah ee la shaqaynta xogta degan kaydinta Amazon S3. Utility-gu wuxuu ku qoran yahay luqadda Python programming, taas awgeedna waxaa loo isticmaali karaa Windows iyo Linux labadaba.
Ku rakibida 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
Isbarbardhigga natiijooyinka shaxda
Sida aad arki karto, Brotli waxay la mid tahay cabbirka LZMA, laakiin kaydinta waxaa lagu sameeyaa waqtiga LZ4.
Wada sheekaysiga bulshada PostgreSQL ee ku hadasha Ruushka:
Fadlan sii Github xiddig haddii aad isticmaasho
Source: www.habr.com