ืคืจืืื ืขื ืืื wal-g ืขืืื ื ืืชื ืืืฆืื ืืืืืจ:
ืคืจืืืืงืื ืืืืกืื S3 ืืคื ืคืืคืืืจื ืืืืกืื ื ืชืื ืื. ืืื ืืืชืจืื ืืช ืฉื S3 ืืื ืืืืืช ืืืืฉื ืืืืฆืขืืช API, ืืืืคืฉืจืช ืืืจืื ืืื ืืจืืงืฆืื ืืืืฉื ืขื ืืืืกืื, ืืืื ืืืฉืช ืงืจืืื ืฆืืืืจืืช, ืืขืื ืฉืขืืืื ืืืืืข ืืืืกืื ืืชืจืืฉ ืจืง ืขื ืืื ืื ืฉืื ืืืจืฉืื.
ืืฉื ื ืืกืคืจ ืืืฉืืืื ืฆืืืืจืืื ืืคืจืืืื ืฉื ืืืกืื ืืคืืขืืื ืขื ืคืจืืืืงืื S3. ืืืื ื ืืื ืคืชืจืื ืคืืคืืืจื ืืืจืืื ืืืกืื ืงืื - Minio.
ืฉืจืช PostgreSQL ืืืื ืืชืืื ืืืืืงืช wal-g, ื-Minio ืืฉืืฉ ืืชืืืืฃ ื-S3.
ืฉืจืช ืืื ืื
ืืชืงื ืช ืืื ืื
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, ืืฉืชืืฉ ืืจืฉืืืช
ืืื ืขื ืืืื ืืจื wal-g, 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 - ืืกืคืจ ืืฆืขืืื ืฉืืืืื ืืืืชื ืืื ืืืงืกืืืื ืืืืืืื ืืืกืืกื, ืืืฆืืื ืืช ืืืื ืืืช ืืขืชืงืช ืืืืชื. ืื ืฉืืชื ืืืฆืจ ืขืืชืง ืืืืืชื ืืงืืืืช ืืืืจืื ื, ืื ืฉืืชื ืขืืฉื ืืืชื ืืืืืืื ืืืื ืืืงืืจื. ืื ืืืจืื ืืืงืจื ืฉืื ืืืชื ืจืืื ืฉื ืืกื ืื ืชืื ืื ืชืืื ืืฉืชื ื ืืืกื ืื ืชืื ืื ืฉืื, ืืืชื ื ืชืื ืื ืืฉืชื ืื ืื ืืืื.
ืืชืงื ืช ืืกื ืื ืชืื ืื.
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
ืื ืืชื ืืืืง ืขื ืฉืจืช ืืื, ืขืืื ืืืืืืจ ืืืืฉ ืืช ืืคืจืืืจ wal_level ืืืจืืืื ืขืืืจ PostgreSQL ืคืืืช ืืืจืกื 1, ืืืขืชืง ืขืืืจ PostgreSQL ืืจืกื 10 ืืืขืื.
wal_level = archive
ืืืื ื ืืื ืืช ืืจืืืื ื WAL ืื 60 ืฉื ืืืช ืืืืฆืขืืช PostgreSQL ืขืฆืื. ื-prod, ืืืื ืื ืขืจื 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
ืฆืืจ ืืืื ืืืืื ืืกื ืื ืชืื ืื1.
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
ืื ื ืืกืชืืืื ืขื ืืจืฉืืืืช ืืืืื ืืืืื ืืกื ืื ืชืื ืื1
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
ืืจืืืื
ะะพัะปะต ะณะตะฝะตัะฐัะธะธ 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