Singula quomodo opera wal-g in articulo inveniri possunt:
S3 protocollum repositionis popularis facta est ad notitias reponendas. Una emolumentorum S3 facultas accessiendi per API est, quae te permittit ut flexibilem commercium cum repositione ordinare, inter publica lege aditum, dum adaequationis informationes in repositione nonnisi ab auctoritate personarum occurrit.
Plures sunt exsecutiones tabulariorum publicarum et privatarum quae protocollo S3 utuntur. Hodie spectabimus solutionem popularem pro parva repositione ordinanda - Minio.
Unicus PostgreSQL ministrator tenuis est ad probandum wal-g, et Minio subrogandi S3 adhibetur.
Minio Servo
Minio institutionem
yum -y install yum-plugin-copr
yum copr enable -y lkiesow/minio
yum install -y minio
Edit AccessKey et SecretKey in /etc/minio/minio.conf
vi /etc/minio/minio.conf
Si nginx ante Minio non uteris, debes mutare
--address 127.0.0.1:9000
--address 0.0.0.0:9000
Deductis Minio
systemctl start minio
Vade ad telam interface Minio
DB servo
WAL-G In rpm a me collecta (Anton Patsev).
Qui RPM-fundatur ratio non habet, officiali utere
Una cum binario wal-g, rpm scripta continet quae variabiles ex fasciculo /etc/wal-gd/servo-s3.conf insunt.
backup-fetch.sh
backup-list.sh
backup-push.sh
wal-fetch.sh
wal-g-run.sh
wal-push.sh
Instrue walg.
yum -y install yum-plugin-copr
yum copr enable -y antonpatsev/wal-g
yum install -y wal-g
Reperiens wal-g litera.
wal-g --version
wal-g version v0.2.14
Edit /etc/wal-gd/server-s3.conf tuis necessitatibus.
Fasciculi configurationes et fasciculi notati, quos botrum datorum usitatum est, translaticio in botro notitiarum directorium reponuntur, ut vulgo dicitur 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 # ΠΠ°ΠΊΠΎΠΉ ΠΌΠ΅ΡΠΎΠ΄ ΡΠΆΠ°ΡΠΈΡ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ.
Cum figurans WAL-G, WALG_DELTA_MAX_STEPS numerum denotas - numerus gradibus della tergum maximus est a basi tergum, et exemplar della forma designas. Aut exemplar e ultima della exsistente conficis, aut delta archetypo plena tergum conficis. Hoc necessarium est, si quando eadem pars datorum semper in datorum datorum mutatur, eadem notitia constanter mutatur.
Inaugurari datorum.
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
Nos database initialize.
/usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK
Si in 1 servo probas, tunc debes parametrum wal_level pro archivo PostgreSQL in tabulario pro postgreSQL minus quam in versione 10 configurare, et replica pro versione PostgreSQL 10 et maiore.
wal_level = archive
Sit scriptor tergum WA tabularia singula 60 secundis utendo ipso PostgreSQL. In iaculo, aliud valorem archivum habebis.
archive_mode = on
archive_command = '/usr/local/bin/wal-push.sh %p'
archive_timeout = 60 # ΠΠ°ΠΆΠ΄ΡΠ΅ 60 ΡΠ΅ΠΊΡΠ½Π΄ Π±ΡΠ΄Π΅Ρ Π²ΡΠΏΠΎΠ»Π½ΡΡΡΡ ΠΊΠΎΠΌΠ°Π½Π΄Π° archive_command.
Satus PostgreSQL
systemctl start postgresql-9.6
In separato console, tabulas postgreSQL errorum inspicimus: (mutatio postgresql-Wed.log ad currentem).
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
Eamus ad psql.
su - postgres
psql
Creare database in psql
Creare mensam in test1 datorum.
create database test1;
Vertas database test.
postgres=# c test1;
Tabulam creamus indexing_table.
test1=# CREATE TABLE indexing_table(created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());
Addito data.
Incipimus inserendo data. Exspectamus 10-20 minuta.
#!/bin/bash
# postgres
while true; do
psql -U postgres -d test1 -c "INSERT INTO indexing_table(created_at) VALUES (CURRENT_TIMESTAMP);"
sleep 60;
done
Fac ut sit plenum tergum.
su - postgres
/usr/local/bin/backup-push.sh
Tabulas in tabula spectamus in test1 database
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+
chorda est tempus praesens.
Vide indicem plenum tergum
/usr/local/bin/backup-list.sh
Recuperatio temptationis
Plena recuperatio cum volventibus omnibus praesto WAL.
Desine Postgresql.
Omnia delere ex /var/lib/pgsql/9.6/data folder.
Curre scripturam /usr/local/bin/backup-fetch.sh sicut postgres user.
su - postgres
/usr/local/bin/backup-fetch.sh
Tergum extraction completum.
Adde recovery.conf ad /var/lib/pgsql/9.6/data folder cum sequenti contento.
restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'
Incipimus PostgreSQL. PostgreSQL processus recuperationis ex archivo WALS incipiet, et tunc demum aperietur datorum.
systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
Convaluisset ad aliquod tempus.
Si velimus database usque ad aliquod minutum restituere, tunc addimus parametrum recuperandi_target_time ad convalescendum.conf - indicamus quo tempore database restituat.
restore_command = '/usr/local/bin/wal-fetch.sh "%f" "%p"'
recovery_target_time = '2020-01-29 09:46:25'
Post recuperationem, vide mensam 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
Incipimus PostgreSQL. PostgreSQL processus recuperationis ex archivo WALS incipiet, et tunc demum aperietur datorum.
systemctl start postgresql-9.6
tail -fn100 /var/lib/pgsql/9.6/data/pg_log/postgresql-Wed.log
temptationis
Generans 1GB database de quo hic
Postulantes situla magnitudinis 1GB ex data generans.
postgres=# SELECT pg_size_pretty(pg_database_size('test1'));
pg_size_pretty
----------------
1003 MB
s4cmd liberum est instrumentum mandati lineae operandi cum notitia quae in repositione Amazonio S3 commoratur. Utilitas scripta est in lingua programmandi Pythonis, et ob hoc in Fenestra et Linux systemata operante adhiberi potest.
installing 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
Comparatio proventus chart.
Ut videre potes, Brotli magnitudine cum LZMA comparabilis est, sed tergum LZ4 tempore conficitur.
Loqui de usu loquendi Russiae PostgreSQL communitatis:
Da stellam Github si uteris
Source: www.habr.com