ααααααΆααααα’α·αα’αααΈααααααα Wal-g ααααΎαααΆαα’αΆα
ααααΆααα
αααα»αα’αααααα
αα·ααΈααΆαααΆααααα»α S3 ααΆαααααΆαααΆααΆαααααα·αααααααΆααααΆααααααΆαα»ααα·ααααααα αα»ααααααααα·αα½ααααα»αα αααααα»ααααααααα·αααα S3 ααΊαααααααΆααααα»αααΆαα αΌαααααΎααΆαααα API αααα’αα»ααααΆαα±ααα’ααααααα αα’αααααααααααα’αΆα ααααααααΆαααΆαα½αααΉααααααααααα»α αα½αααΆααααΆαα αΌαα’αΆαααΆααΆααΆααα ααααααααΆαααααΎαα αα α»ααααααααΆαααααααΆααα αααα»ααααααααααα»αααΎαα‘αΎααααα’ααααααααΆαααΆαα’αα»ααααΆαααα»αααααα
ααΆαααΆαα’αα»ααααααΆααααα»αααΆααΆααα αα·αα―ααααα½αα ααα½ααααααααΎαα·ααΈααΆα S3 α αααααααααααααΎαααΉααα·αα·αααααΎααααααααααΆαααααααα·αααα½ααααααΆααααΆααααα ααααααααααα»αααΌα - Minio α
αααΆαααΈααα PostgreSQL αααα½αααΊααα’αααααΆααααΆαααΆααααα wal-g α αΎα Minio ααααΌαααΆαααααΎααΆααΆααααα½ααααααΆαα S3 α
αααΆαααΈααα Minio
ααΆαααα‘αΎαααααΆαααΌα
yum -y install yum-plugin-copr
yum copr enable -y lkiesow/minio
yum install -y minio
ααααααα½α AccessKey αα·α SecretKey αααα»α /etc/minio/minio.conf
vi /etc/minio/minio.conf
ααααα·αααΎα’αααααΉααα·αααααΎ nginx αα»αααα Minio αααααα’αααααααΌαααααΆααααααΌα
--address 127.0.0.1:9000
--address 0.0.0.0:9000
ααΎαααααΎαααΆα Minio
systemctl start minio
α
αΌααα
ααΆααα
ααα»α
αααααΆαααααααΆα Minio
αααΆαααΈααα DB
WAL-G αααα»α rpm ααααΌαααΆααααα»αααααααα»α (Anton Patsev) α
ααΎα’αααααΆααααα·αααΆαααααααααααα’ααααΎ RPM ααΌαααααΎααααΌαααΆα
αα½αααΆαα½αααΉα wal-g binary, rpm ααΆαααααααΈααααααΆαα αΌαα’αααααΈα―αααΆα /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
ααα‘αΎα wal-g α
yum -y install yum-plugin-copr
yum copr enable -y antonpatsev/wal-g
yum install -y wal-g
αααα»ααα·αα·αααααΎααααα wal-g α
wal-g --version
wal-g version v0.2.14
ααααααα½α /etc/wal-gd/server-s3.conf αα ααΆααααααΌαααΆαααααα’αααα
α―αααΆαααααααα
ααΆαααααααα αα·αα―αααΆααα·αααααααααααααΎαααα
αααααααΌαααααΆααα·ααααααααααΌαααΆααααααΆαα»αααΆαααααααΈαα½αααααΆαα
αααα»ααααα·ααααααα
ααααα αααααΆααΌαα
ααα α
ααΆ 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 α’ααααααααΆαα WALG_DELTA_MAX_STEPS - α ααα½αααα αΆααααααΆαααααα»ααα»αααΈααααααΊα’αα·ααααΆααΈααΆαααααα»ααα»αααΌαααααΆα α αΎααααααΆαααααααΆαααα ααααααΈααααα ααΆααα’αααααααΎα αααΆααα ααααααΈααΈαααααααααΆαααααΆααα α»αααααα α¬α’ααααααααΎα delta ααΈααΆαααααα»ααα»αααααααααΎαα αααααΊα αΆαααΆα ααααα»αααααΈααααααΆαααΆαα»ααΌα ααααΆααααΌαααααΆααα·αααααααααααααααΆααααααΌααα αααα»αααΌαααααΆααα·ααααααααααα’ααα αα·ααααααααΌα ααααΆαααα»αααααΆααααααΌαααΆαα·α αα α
ααΆαααα‘αΎαααΌαααααΆααα·ααααααα
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
ααΎαα αΆααααααΎαααΌαααααΆααα·ααααααα
/usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK
ααααα·αααΎα’ααααααα»αααΆααααααα ααΎαααΆαααΈααα 1 αααα’αααααααΌαααααααα ααΆαααααααααααΆαααΆαααααα wal_level α‘αΎααα·αααΎααααΈαα»ααααα»ααααααααΆααααααΆαα PostgreSQL αα·α ααΆααααα 10 α αΎαα αααααααααΆαα PostgreSQL αααα 10 αα·αα αΆααααΆααααα
wal_level = archive
α αΌαααΎαααααα»ααα»α WAL αα»αααΆα―αααΆααααααΆαα 60 αα·ααΆααΈαααααααΎ PostgreSQL αααα½αααΆααααΆααα αα ααΎααα·ααα α’αααααΉαααΆαααααα archive_timeout αα»αααααΆα
archive_mode = on
archive_command = '/usr/local/bin/wal-push.sh %p'
archive_timeout = 60 # ΠΠ°ΠΆΠ΄ΡΠ΅ 60 ΡΠ΅ΠΊΡΠ½Π΄ Π±ΡΠ΄Π΅Ρ Π²ΡΠΏΠΎΠ»Π½ΡΡΡΡ ΠΊΠΎΠΌΠ°Π½Π΄Π° archive_command.
α αΆααααααΎα PostgreSQL
systemctl start postgresql-9.6
αα αααα»ααα»αααΌαααΆα ααααα‘αααα½α ααΎαααΎααααααα ααα» PostgreSQL αααααΆααααα α»αα (ααααΆααααααΌα postgresql-Wed.log αα αα αα α»αααααα)α
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
ααααα psql α
su - postgres
psql
αααααΎαααΌαααααΆααα·αααααααααα»α psql
αααααΎαααΆααΆααααα»αααΆαααααΎαααααααΌαααααΆααα·αααααα α‘.
create database test1;
ααααΌααα ααΆαααααΎαααααααΌαααααΆααα·ααααααα
postgres=# c test1;
ααΎααααααΎαααΆααΆα indexing_table α
test1=# CREATE TABLE indexing_table(created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());
ααΆααααααααα·ααααααα
ααΎαα αΆααααααΎααααα αΌααα·ααααααα ααΎααααα»ααααα αΆα 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
ααααΌαααααΆααααΆααααΎααΆαααααα»ααα»αααααααα
su - postgres
/usr/local/bin/backup-push.sh
ααΎαααΎααααααααααΆαααα»αααΆααΆααααα»α database test1
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+
ααααα’ααααααΊααΆαααααααΆαα αα α»ααααααα
ααΎααααααΈααααΆαααααα»ααα»ααααααα
/usr/local/bin/backup-list.sh
ααΆαααααΎαααααααααΆαα‘αΎααα·α
ααΆαααΎαα‘αΎααα·αααααααααΆαα½αααΉαααΆααααα WAL αααααΆαααΆααα’ααα
αααααα Postgresql α
αα»αα’αααΈαααΆααα’ααα ααααΈαα /var/lib/pgsql/9.6/data α
ααααΎαααΆαααααααΈα /usr/local/bin/backup-fetch.sh ααΆα’αααααααΎααααΆαα postgres α
su - postgres
/usr/local/bin/backup-fetch.sh
ααΆαααΆαααααΆαααααα»ααα»αααΆααααα ααα
αααααα recovery.conf αα αααα»αααα―αααΆα /var/lib/pgsql/9.6/data αααααΆαααααΉαααΆαααΌα ααΆααααααα
restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'
ααΎαα αΆααααααΎα PostgreSQL α PostgreSQL ααΉαα αΆααααααΎαααααΎαααΆαααααααααααΈ WALs αααααΆααα»ααααα»ααααααααΆα α αΎαααΆααααααααααααααααΌαααααΆααα·ααααααααΉαααΎαα
systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
ααΆαααΎαα‘αΎααα·ααααα»αααααααααΆααααΆαααα½αα
ααααα·αααΎααΎαα ααααααΆαααΌαααααΆααα·αααααααα αΌαααααα½αααΆααΈ αααααΎααααααααααΆαααΆαααααα recovery_target_time αα ααΆαα recovery.conf - ααΎααααα αΆαααΆαα αααααΆαααααααΌαααααΆαααΌαααααΆααα·ααααααα‘αΎααα·αα
restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'
recovery_target_time = '2020-01-29 09:46:25'
αααααΆααααΈααΆαααΎαα‘αΎααα·αααΌαααΎαααΆααΆα 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
ααΎαα αΆααααααΎα PostgreSQL α PostgreSQL ααΉαα αΆααααααΎαααααΎαααΆαααααααααααΈ WALs αααααΆααα»ααααα»ααααααααΆα α αΎαααΆααααααααααααααααΌαααααΆααα·ααααααααΉαααΎαα
systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
ααΆααααα
ααΆααααααΎαααΌαααααΆααα·αααααα 1GB ααΌα
αααααΆααα·αααααΆαα
ααΈααα
ααααΎαα»αααα ααα»αααΉααααααΆααααΈαααααΎααα·αααααα 1GB α
postgres=# SELECT pg_size_pretty(pg_database_size('test1'));
pg_size_pretty
----------------
1003 MB
s4cmd ααΊααΆα§ααααααααααΆααααΆααααααααΆα₯ααα·ααααααααααΆααααααΎααΆαααΆαα½ααα·αααααααααααααα αααα»αααααΆαααααα»α Amazon S3 α α§αααααααααΎααααΆααααααΌαααΆααααααααΆααΆααΆααααααααααα·ααΈ python α αΎααααααΆαααΆααΆα’αΆα ααααΎααΆαααΆααααααααααααααα·ααααα·ααΆα Windows αα·α Linux α
ααΆαααα‘αΎα 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
ααΆααααααααααααααααα ααΎααΆααΆαα
ααΌα αααα’αααα’αΆα ααΎα Brotli ααΆαααα αααα»α LZMA ααα»ααααααΆαααααα»ααα»αααααΌαααΆαα’αα»αααααα αααα»ααααααααΆ LZ4 α
ααΆααααααααααα αααα PostgreSQL ααααα·ααΆαααΆααΆαα»ααααΈα
ααΌααααααααααΆααα½ααα
Github ααααα·αααΎα’αααααααΎ
ααααα: www.habr.com