ืืื ืึท ืคึผืฉืื ืืื ืขืคืขืงืืืื ืืขืฆืืึทื ืคึฟืึทืจ ืืึทืงืื ื ืึทืจืืืฃ PostgreSQL ืฆื ืื ืืืึธืืงื. ืืื ืืขืจืืื ืขื ืคืื ืืืึทื ืืืืคึผื ืคืึทื ืืงืฉืึทื ืึทืืืื, ืขืก ืืื ืืขืจ ืกืึทืงืกืขืกืขืจ ืฆื ืื ืคืึธืืงืก ืืขืฆืืึทื , ืึธืืขืจ ืจืืจืืื ืืื Go. ืึธืืขืจ WAL-G ืืื ืืืื ืืืืืืืง ื ืืึทืข ืฉืืจืื: ืืขืืืึท ืงืืคืืขืก. ืืขืืืึท ืงืืคืืขืก ืงืจืึธื ืืืขืืขืจ ืคืื ืืขืงืขืก ืืืึธืก ืืึธืื ืืขืืืื ืืื ื ืื ืคืจืืขืจืืืงืข ืืืขืจืกืืข ืคืื โโโโืื ืืึทืงืึทืคึผ. WAL-G ืืืคึผืืึทืืึทื ืฅ ืืึทื ืฅ ืึท ืคึผืืึทืฅ ืคืื ืืขืงื ืึทืืึทืืืฉืื ืคึฟืึทืจ ืคึผืึทืจืึทืืขืืืืื ื ืืึทืงืึทืคึผืก. WAL-G ืืื ืคืื ืคืึทืกืืขืจ ืืื WAL-E.
ืืขืืึทืืืก ืคืื ืืื wal-g ืึทืจืืขื ืงืขื ืขื ืืืื ืืขืคึฟืื ืขื ืืื ืืขื ืึทืจืืืงื:
ืื S3 ืกืืึธืจืืืืฉ ืคึผืจืึธืืึธืงืึธื ืืื ืืขืืืืจื ืคืึธืืงืก ืคึฟืึทืจ ืกืืึธืจืื ื ืืึทืื. ืืืื ืขืจ ืคืื ืื ืึทืืืืึทื ืืืืืฉืื ืคืื S3 ืืื ืื ืคืืืืงืืื ืฆื ืึทืงืกืขืก ืืืจื ืึทืคึผื, ืืืึธืก ืึทืืึทืื ืืืจ ืฆื ืึธืจืืึทื ืืืืจื ืคืืขืงืกืึทืืึทื ืื ืืขืจืึทืงืฉืึทื ืืื ืื ืกืืึธืจืืืืฉ, ืึทืจืืึทื ืืขืจืขืื ื ืขืคื ืืืขื ืืืืขื ืขื ืึทืงืกืขืก, ืืฉืขืช ืึทืคึผืืืืืื ื ืืื ืคึฟืึธืจืืึทืฆืืข ืืื ืื ืกืืึธืจืืืืฉ ืึทืงืขืจื ืืืืื ืืืจื ืึธืืขืจืืืื ืืขื ืืฉื.
ืขืก ืืขื ืขื ืขืืืขืืข ืขืคื ืืืขื ืืื ืคึผืจืืืืึทื ืกืืึธืจืืืืฉ ืืืคึผืืึทืืึทื ืฅ ืืืึธืก ื ืืฆื ืื S3 ืคึผืจืึธืืึธืงืึธื. ืืืึทื ื ืืืจ ืืืขืื ืงืืงื ืืื ืึท ืคืึธืืงืก ืืืืืื ื ืคึฟืึทืจ ืึธืจืืึทื ืืืืื ื ืงืืืื ืกืืึธืจืืืืฉ ืคืึทืกืืืึทืืื - ืืื ืืึธ.
ื ืืืื 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 ืืืืืขืจ ืืื ืืึธ, ืืืจ ืืึทืจืคึฟื ืฆื ืืืืฉื
--address 127.0.0.1:9000--address 0.0.0.0:9000ืงืึทืืขืจ ืืื ืืึธ
systemctl start minioืืืื ืฆื ืื ืืื ืืึธ ืืืขื ืฆืืืื ื ืืื ืฉืึทืคึฟื ืึท ืขืืขืจ (ืคึฟืึทืจ ืืืึทืฉืคึผืื, pg-backups).
ืื ืกืขืจืืืขืจ
ืืื ืึทืกืขืืืึทื WAL-G ืืื ืจืคึผื (ืึทื ืืึธื ืคึผืึทืืกืขืื). , .
ืคึฟืึทืจ ืื ืืืืก ืืึธื ื ืื ืืึธืื ืึท RPM-ืืืืืจื ืกืืกืืขื, ื ืืฆื ืื ืืึทืึทืืืขืจ ืืืืฃ ืืึทื ืืึธื ืืืจืื ื.
ืฆืืืืืขื ืืื ืื wal-g ืืืื ืขืจื, ืื ืจืคึผื ืึผืืื ืกืงืจืืคึผืก ืืืึธืก ืึทืจืืึทื ืคืืจ ืืืขืจืืึทืืึทืื ืคืื ืื /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ืืืื ืืืจ ืคึผืจืืืืจื ืืืืฃ 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.
ืฉืึทืคึฟื ืึท ืืืฉ ืืื ืื ืืขืกื1 ืืึทืืึทืืืืก.
create database test1;ืืึทืฉืืืืขื ืฆื ืื ืคึผืจืึธืืข ืืึทืืึทืืืืก.
postgres=# c test1;ืฉืึทืคึฟื ืึท ืืืฉ ืื ืืขืงืกืื ื_ืืึทืืืข.
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ืืืจ ืงืืงื ืืื ืื ืืืื ืกื ืืื ืื ืืืฉ ืืื ืื 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 ืคึฟืื ืื ืคึผืึธืกืืืจืขืก ืืึทื ืืฆืขืจ.
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 ืืืขื ืงืึทืืขืจ ืื ืึธืคึผืืื ืคึผืจืึธืฆืขืก ืคึฟืื ืื ืึทืจืืฉืืืืขื WAL, ืืื ืืืืื ืืขืืึธืื ืื ืืึทืืึทืืืืก ืืืขื ืขืคืขื ืขื.
systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.logืึธืคึผืืื ืคึฟืึทืจ ืึท ืืขืืืืกืข ืฆืืื.
ืืืื ืืืจ ืืืืื ืฆื ืืืงืขืจื ืื ืืึทืืึทืืืืก ืฆื ืึท ืืืืขืจ ืืื ืื, ืืื Recovery.conf ืืืจ ืืืืื ืื ืจืขืงืึธืืืขืจื_ืืึทืจืืขื_ืืืืข ืคึผืึทืจืึทืืขืืขืจ - ืืืจ ืึธื ืืืืึทืื ืืื ืืืึธืก ืฆืืื ืฆื ืืืงืขืจื ืื ืืึทืืึทืืืืก.
restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'
recovery_target_time = '2020-01-29 09:46:25'ื ืึธื ืึธืคึผืืื, ืงืืง ืืื ืื ืื ืืขืงืกืื ื_ืืึทืืืข ืืืฉ
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 ืืืขื ืงืึทืืขืจ ืื ืึธืคึผืืื ืคึผืจืึธืฆืขืก ืคึฟืื ืื ืึทืจืืฉืืืืขื WAL, ืืื ืืืืื ืืขืืึธืื ืื ืืึทืืึทืืืืก ืืืขื ืขืคืขื ืขื.
systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.logืืขืกืืื ื
ืืืจ ืืืฉืขื ืขืจืืื ืึท 1GB ืืึทืืึทืืืืก ืืื ืืืกืงืจืืืื ืืึธ
ืืืจ ืืขืื ืื ืขืืขืจ ืืจืืืก ื ืึธื ืืืฉืขื ืขืจืืืืื ื 1 ืื ืคืื ืืึทืื.
postgres=# SELECT pg_size_pretty(pg_database_size('test1'));
pg_size_pretty
----------------
1003 MBs4cmd โ ะฑะตัะฟะปะฐัะฝัะน ะธะฝััััะผะตะฝั ะบะพะผะฐะฝะดะฝะพะน ัััะพะบะธ ะดะปั ัะฐะฑะพัั ั ะดะฐะฝะฝัะผะธ, ัะฐัะฟะพะปะพะถะตะฝะฝัะผะธ ะฒ ั ัะฐะฝะธะปะธัะต Amazon S3. ะฃัะธะปะธัะฐ ะฝะฐะฟะธัะฐะฝะฐ ะฝะฐ ัะทัะบะต ะฟัะพะณัะฐะผะผะธัะพะฒะฐะฝะธั python, ะธ ะฑะปะฐะณะพะดะฐัั ััะพะผั ะผะพะถะตั ะธัะฟะพะปัะทะพะฒะฐัััั ะฒ ะพะฟะตัะฐัะธะพะฝะฝัั ัะธััะตะผะฐั ะธ Windowsืืื Linux.
ืื ืกืืึทืืืจื s4cmd
pip install s4cmdืืืงืกื ืืืงืก
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 ะะ ะทะฐะฝะธะผะฐะตั ะฟะพะปะฝัะน ะฑะตะบะฐะฟืืืืึท
ะะพัะปะต ะณะตะฝะตัะฐัะธะธ 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
