ProHoster > Π‘Π»ΠΎΠ³ > Administrasi > Pambuka kanggo sistem cadangan wal-g PostgreSQL

Pambuka kanggo sistem cadangan wal-g PostgreSQL

WAL-G minangka alat sing prasaja lan efektif kanggo nggawe serep PostgreSQL menyang awan. Ing babagan fungsi utama, iki minangka pewaris alat sing populer WAL-E, nanging ditulis maneh ing Go. Nanging ana siji fitur anyar penting ing WAL-G - salinan delta. salinan delta WAL-G nyimpen kaca file sing wis diganti wiwit versi serep sadurunge. WAL-G ngleksanakake cukup akeh teknologi kanggo parallelizing serep. WAL-G luwih cepet tinimbang WAL-E.

Rincian babagan cara kerjane wal-g bisa ditemokake ing artikel: Kita overclock serep. Kuliah Yandex

Protokol panyimpenan S3 wis dadi populer kanggo nyimpen data. Salah sawijining kaluwihan S3 yaiku kemampuan kanggo ngakses liwat API, sing ngidini sampeyan ngatur interaksi fleksibel karo panyimpenan, kalebu akses maca umum, nalika nganyari informasi ing panyimpenan mung ditindakake dening wong sing sah.

Ana sawetara implementasi panyimpenan umum lan pribadi sing nggunakake protokol S3. Dina iki kita bakal nemokake solusi populer kanggo ngatur panyimpenan cilik - Minio.

Server PostgreSQL siji apik kanggo nguji wal-g, lan Minio digunakake minangka panggantos kanggo S3.

Server Minio Kab

Instalasi minio

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

Sunting AccessKey lan SecretKey ing /etc/minio/minio.conf

vi /etc/minio/minio.conf

Yen sampeyan ora bakal nggunakake nginx sadurunge Minio, sampeyan kudu ngganti

--address 127.0.0.1:9000

--address 0.0.0.0:9000

Lansiran Minio

systemctl start minio

Pindhah menyang antarmuka web Minio http://ip-адрСс-сСрвСра-minio:9000 lan nggawe ember (contone, pg-serep).

server DB

WAL-G ing rpm dirakit dening kula (Anton Patsev). GitHub, Fedora COPR.

Sing ora duwe sistem basis RPM, nggunakake resmi instruksi dening instalasi.

Bebarengan karo biner wal-g, rpm ngemot skrip sing ngimpor variabel saka file /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

Instal walg.

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

Priksa versi wal-g.

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

Sunting /etc/wal-gd/server-s3.conf kanggo kabutuhan.

File konfigurasi lan file data sing digunakake dening kluster basis data biasane disimpen bebarengan ing direktori data kluster, biasane diarani minangka 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 # Какой ΠΌΠ΅Ρ‚ΠΎΠ΄ сТатия ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ.

Nalika ngonfigurasi WAL-G, sampeyan nemtokake WALG_DELTA_MAX_STEPS - jumlah langkah sing serep delta maksimum saka serep basa, lan nemtokake privasi salinan delta. Sampeyan nggawe salinan saka delta pungkasan sing ana, utawa nggawe delta saka serep lengkap asli. Iki perlu yen komponen database sing padha tansah ganti ing basis data sampeyan, data sing padha saya ganti.

Nginstal 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

Kita miwiti database.

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

Yen sampeyan nyoba ing 1 server, sampeyan kudu ngatur ulang parameter wal_level kanggo arsip kanggo PostgreSQL kurang saka versi 10, lan replika kanggo PostgreSQL versi 10 lan lawas.

wal_level = archive

Ayo gawe serep arsip WAL saben 60 detik nggunakake PostgreSQL dhewe. Ing prod, sampeyan bakal duwe nilai archive_timeout sing beda.

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

Miwiti PostgreSQL

systemctl start postgresql-9.6

Ing console kapisah, kita katon ing PostgreSQL log kanggo kasalahan: (ngganti postgresql-Wed.log kanggo saiki).

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

Ayo menyang psql.

su - postgres
psql

Nggawe database ing psql

Nggawe tabel ing test database1.

create database test1;

Ngalih menyang tes database.

postgres=# c test1;

Kita nggawe tabel indexing_table.

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

Nambah data.

Kita miwiti nglebokake data. Kita ngenteni 10-20 menit.

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

Priksa manawa nggawe serep lengkap.

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

Kita ndeleng cathetan ing tabel ing test database1

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+

String minangka wektu saiki.

Deleng dhaptar serep lengkap

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

testing Recovery

Recovery Full karo Rolling kabeh WAL kasedhiya.

Mungkasi Postgresql.

Busak kabeh saka folder /var/lib/pgsql/9.6/data.

Jalanake skrip /usr/local/bin/backup-fetch.sh minangka pangguna postgres.

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

Ekstraksi serep rampung.

Tambah recovery.conf menyang folder /var/lib/pgsql/9.6/data kanthi isi ing ngisor iki.

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

Kita miwiti PostgreSQL. PostgreSQL bakal miwiti proses pemulihan saka WAL sing diarsipakΓ©, lan mung banjur database bakal mbukak. 

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

Recovery kanggo wektu tartamtu.

Yen kita pengin mulihake database nganti menit tartamtu, banjur kita nambah parameter recovery_target_time kanggo recovery.conf - kita nuduhake ing wektu apa kanggo mulihake database.

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

Sawise pulih, deleng tabel 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

Kita miwiti PostgreSQL. PostgreSQL bakal miwiti proses pemulihan saka WAL sing diarsipakΓ©, lan mung banjur database bakal mbukak. 

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

Tes

Nggawe database 1GB kaya sing diterangake ing kene https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff

Njaluk ukuran ember sawise ngasilake 1GB data.

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

s4cmd minangka alat baris printah gratis kanggo nggarap data sing ana ing panyimpenan Amazon S3. Utilitas kasebut ditulis ing basa pemrograman python, lan amarga iki bisa digunakake ing sistem operasi Windows lan Linux.

Nginstal 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

Perbandingan asil ing grafik.

Pambuka kanggo sistem cadangan wal-g PostgreSQL

Nalika sampeyan bisa ndeleng, Brotli iso dibandhingke ing ukuran kanggo LZMA, nanging serep dileksanakake ing wektu LZ4.

Obrolan saka komunitas PostgreSQL sing nganggo basa Rusia: https://t.me/pgsql

Mangga menehi lintang kanggo Github yen sampeyan nggunakake wal-g

Source: www.habr.com

Add a comment