Wal-g qanday ishlashi haqida batafsil ma'lumotni maqolada topishingiz mumkin:
S3 saqlash protokoli ma'lumotlarni saqlash uchun mashhur bo'ldi. S3 ning afzalliklaridan biri bu API orqali kirish imkoniyatidir, bu sizga saqlash bilan moslashuvchan o'zaro aloqani, shu jumladan ommaviy o'qishga kirishni tashkil qilish imkonini beradi, shu bilan birga saqlashdagi ma'lumotlarni yangilash faqat vakolatli shaxslar tomonidan amalga oshiriladi.
S3 protokolidan foydalanadigan bir nechta ommaviy va shaxsiy saqlash dasturlari mavjud. Bugun biz kichik saqlashni tashkil qilish uchun mashhur echimni ko'rib chiqamiz - Minio.
Bitta PostgreSQL serveri wal-g ni sinab ko'rish uchun juda mos keladi va Minio S3 o'rniga ishlatiladi.
Minio server
Minio o'rnatish
yum -y install yum-plugin-copr
yum copr enable -y lkiesow/minio
yum install -y minio
AccessKey va SecretKey-ni /etc/minio/minio.conf da tahrirlang
vi /etc/minio/minio.conf
Agar siz Minio-dan oldin nginx-dan foydalanmasangiz, uni o'zgartirishingiz kerak
--address 127.0.0.1:9000
--address 0.0.0.0:9000
Minio ishga tushirilmoqda
systemctl start minio
Minio veb-interfeysiga o'ting
DB server
WAL-G rpmda men tomonidan yig'ilgan (Anton Patsev).
Kim RPM-ga asoslangan tizimga ega bo'lmasa, rasmiydan foydalaning
wal-g binar bilan bir qatorda rpm /etc/wal-gd/server-s3.conf faylidan o'zgaruvchilarni import qiluvchi skriptlarni o'z ichiga oladi.
backup-fetch.sh
backup-list.sh
backup-push.sh
wal-fetch.sh
wal-g-run.sh
wal-push.sh
Walg-ni o'rnating.
yum -y install yum-plugin-copr
yum copr enable -y antonpatsev/wal-g
yum install -y wal-g
Wal-g versiyasi tekshirilmoqda.
wal-g --version
wal-g version v0.2.14
/etc/wal-gd/server-s3.conf ni ehtiyojlaringizga qarab tahrirlang.
Ma'lumotlar bazasi klasteri tomonidan foydalaniladigan konfiguratsiya fayllari va ma'lumotlar fayllari an'anaviy ravishda klaster ma'lumotlari katalogida saqlanadi, odatda shunday deb ataladi. 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 # ΠΠ°ΠΊΠΎΠΉ ΠΌΠ΅ΡΠΎΠ΄ ΡΠΆΠ°ΡΠΈΡ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ.
WAL-G ni sozlashda siz WALG_DELTA_MAX_STEPS ni belgilaysiz - delta zahirasining asosiy zaxiradan maksimal bo'lgan qadamlar sonini va delta nusxa ko'chirish siyosatini belgilang. Yoki oxirgi mavjud deltadan nusxa ko'chirasiz yoki asl to'liq zaxiradan delta yaratasiz. Agar ma'lumotlar bazasining bir xil komponenti har doim ma'lumotlar bazasida o'zgarib tursa, bir xil ma'lumotlar doimiy ravishda o'zgarib turadigan bo'lsa, bu zarur.
Ma'lumotlar bazasini o'rnatish.
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
Biz ma'lumotlar bazasini ishga tushiramiz.
/usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK
Agar siz 1 serverda sinovdan oΚ»tayotgan boΚ»lsangiz, PostgreSQL 10-versiyasidan pastroq va PostgreSQL-ning 10-versiyasi va undan yuqori versiyalari uchun nusxalash uchun arxivlash uchun wal_level parametrini qayta sozlashingiz kerak.
wal_level = archive
Keling, PostgreSQL yordamida WAL arxivlarini har 60 soniyada zaxiralaymiz. Mahsulotda siz boshqa archive_timeout qiymatiga ega bo'lasiz.
archive_mode = on
archive_command = '/usr/local/bin/wal-push.sh %p'
archive_timeout = 60 # ΠΠ°ΠΆΠ΄ΡΠ΅ 60 ΡΠ΅ΠΊΡΠ½Π΄ Π±ΡΠ΄Π΅Ρ Π²ΡΠΏΠΎΠ»Π½ΡΡΡΡ ΠΊΠΎΠΌΠ°Π½Π΄Π° archive_command.
PostgreSQL ni ishga tushirish
systemctl start postgresql-9.6
Alohida konsolda biz xatolar uchun PostgreSQL jurnallarini ko'rib chiqamiz: (postgresql-Wed.logni joriyiga o'zgartiring).
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
Keling, psql ga o'tamiz.
su - postgres
psql
psql da ma'lumotlar bazasini yarating
Ma'lumotlar bazasi testida jadval yarating1.
create database test1;
Ma'lumotlar bazasi testiga o'tish.
postgres=# c test1;
Biz indekslash_jadvalini yaratamiz.
test1=# CREATE TABLE indexing_table(created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());
Ma'lumotlarni qo'shish.
Biz ma'lumotlarni kiritishni boshlaymiz. Biz 10-20 daqiqa kutamiz.
#!/bin/bash
# postgres
while true; do
psql -U postgres -d test1 -c "INSERT INTO indexing_table(created_at) VALUES (CURRENT_TIMESTAMP);"
sleep 60;
done
To'liq zaxira nusxasini yaratganingizga ishonch hosil qiling.
su - postgres
/usr/local/bin/backup-push.sh
Ma'lumotlar bazasi testida jadvaldagi yozuvlarni ko'rib chiqamiz1
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+
Satr joriy vaqt.
To'liq zaxiralar ro'yxatini ko'ring
/usr/local/bin/backup-list.sh
Qayta tiklash testi
Barcha mavjud WAL-ni aylantirib, to'liq tiklanish.
Postgresql-ni to'xtating.
/var/lib/pgsql/9.6/data jildidan hamma narsani o'chirib tashlang.
Postgres foydalanuvchisi sifatida /usr/local/bin/backup-fetch.sh skriptini ishga tushiring.
su - postgres
/usr/local/bin/backup-fetch.sh
Zaxira nusxasi olindi.
Recovery.conf faylini quyidagi tarkibga ega /var/lib/pgsql/9.6/data jildiga qo'shing.
restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'
Biz PostgreSQL ni ishga tushiramiz. PostgreSQL arxivlangan WAL-lardan tiklash jarayonini boshlaydi va shundan keyingina ma'lumotlar bazasi ochiladi.
systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
Muayyan vaqt uchun tiklanish.
Agar biz ma'lumotlar bazasini ma'lum bir daqiqagacha qayta tiklamoqchi bo'lsak, biz recovery.conf ga recovery_target_time parametrini qo'shamiz - biz ma'lumotlar bazasini qaysi vaqtda tiklashni ko'rsatamiz.
restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'
recovery_target_time = '2020-01-29 09:46:25'
Qayta tiklangandan so'ng, indexing_table jadvaliga qarang
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
Biz PostgreSQL ni ishga tushiramiz. PostgreSQL arxivlangan WAL-lardan tiklash jarayonini boshlaydi va shundan keyingina ma'lumotlar bazasi ochiladi.
systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
Viktorina
Bu yerda tasvirlanganidek 1 GB ma'lumotlar bazasini yaratish
1 Gb maΚΌlumot ishlab chiqarilgandan soΚ»ng chelak hajmini soΚ»rash.
postgres=# SELECT pg_size_pretty(pg_database_size('test1'));
pg_size_pretty
----------------
1003 MB
s4cmd - Amazon S3 xotirasida joylashgan ma'lumotlar bilan ishlash uchun bepul buyruq qatori vositasi. Yordamchi dastur python dasturlash tilida yozilgan va shuning uchun uni Windows va Linux operatsion tizimlarida ishlatish mumkin.
s4cmd o'rnatilmoqda
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
Natijalarni diagrammada taqqoslash.
Ko'rib turganingizdek, Brotli hajmi bo'yicha LZMA bilan solishtirish mumkin, ammo zaxiralash LZ4 vaqtida amalga oshiriladi.
Rusiyzabon PostgreSQL hamjamiyatining chati:
Agar foydalansangiz, Githubga yulduzcha bering
Manba: www.habr.com