ProHoster > Блог > Whakahaerenga > He kupu whakataki ki te punaha wal-g PostgreSQL

He kupu whakataki ki te punaha wal-g PostgreSQL

WAL-G he taputapu ngawari me te whai hua mo te tautoko i te PostgreSQL ki nga kapua. I runga i tana mahi matua, ko ia te rangatira o te taputapu rongonui WAL-E, engari i tuhia ano ki te Haere. Engari tera ano tetahi ahuatanga hou nui i roto i te WAL-G - he kape delta. nga kape delta WAL-G rokiroki wharangi o nga konae kua rereke mai i te putanga o mua. He maha nga hangarau e whakamahia ana e WAL-G mo te whakarara i nga taapiri. He tere ake te WAL-G i te WAL-E.

Ko nga korero mo te mahi a wal-g ka kitea i roto i te tuhinga: Ka karakahia e matou te taapiri. Ko te korero a Yandex

Ko te kawa rokiroki S3 kua rongonui mo te rokiroki raraunga. Ko tetahi o nga painga o te S3 ko te kaha ki te uru atu ma te API, ka taea e koe te whakarite i nga taunekeneke ngawari me te rokiroki, tae atu ki te panui panui a te iwi, ko te whakahou i nga korero i roto i te rokiroki ka puta ma nga tangata whai mana anake.

He maha nga whakatinanatanga rokiroki a te iwi me te tangata motuhake e whakamahi ana i te kawa S3. I tenei ra ka titiro tatou ki tetahi otinga rongonui mo te whakarite rokiroki iti - Minio.

He pai te tūmau PostgreSQL kotahi mo te whakamatautau wal-g, ka whakamahia a Minio hei whakakapi mo S3.

Tūmau Minio

Te whakaurunga iti

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

Whakatika AccessKey me SecretKey i /etc/minio/minio.conf

vi /etc/minio/minio.conf

Mena kaore koe e whakamahi i te nginx i mua i a Minio, katahi koe ka huri

--address 127.0.0.1:9000

--address 0.0.0.0:9000

Whakarewa Minio

systemctl start minio

Haere ki te atanga tukutuku Minio http://ip-адрес-сервера-minio:9000 ka hanga peere (hei tauira, pg-backups).

tūmau DB

Ko te WAL-G i roto i te rpm i whakaemihia e au (Anton Patsev). GitHub, Fedora COPR.

Ko wai kaore he punaha RPM, whakamahia te mana tohutohu ma te whakaurunga.

I te taha o te wal-g rua, kei roto i te rpm nga tuhinga e kawemai ana i nga taurangi mai i te konae /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

Tāuta walg.

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

Te arowhai i te putanga wal-g.

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

Whakatikatika /etc/wal-gd/server-s3.conf ki o hiahia.

Ko nga konae whirihoranga me nga konae raraunga e whakamahia ana e te kapopu raraunga ka penapena tahi ki roto i te raarangi raraunga kohinga, e kiia ana ko te 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 # Какой метод сжатия использовать.

I te whirihora i te WAL-G, ka tohua e koe te WALG_DELTA_MAX_STEPS - te maha o nga hikoinga ka nui rawa atu te taapiri delta mai i te taapiri turanga, ka tohua te kaupapa here kape delta. Ka mahia e koe he kape mai i te delta o mua, ka mahia ranei he delta mai i te taapiri taketake taketake. He mea tika tenei mehemea kei te huri tonu te waahanga o te papaarangi i nga wa katoa i roto i to paataka, ka huri tonu nga raraunga kotahi.

Te tāuta i te pātengi raraunga.

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

Ka arawhiti tatou i te papaunga raraunga.

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

Mena kei te whakamatautau koe i runga i te 1 tūmau, me whirihora ano e koe te tawhā wal_level hei pūranga mo PostgreSQL iti iho i te putanga 10, me te tauira mo PostgreSQL putanga 10 me te pakeke ake.

wal_level = archive

Me whakapumau i nga puranga WAL ia 60 hēkona ma te whakamahi i te PostgreSQL ake. I runga i te prod, ka whai koe i te uara archive_timeout rereke.

archive_mode = on
archive_command = '/usr/local/bin/wal-push.sh %p'
archive_timeout = 60 # Каждые 60 секунд будет выполнятся команда archive_command.

Ka timata te PostgreSQL

systemctl start postgresql-9.6

I roto i te papatohu motuhake, ka titiro tatou ki nga rangitaki PostgreSQL mo nga hapa: (hurihia te postgresql-Wed.log ki te mea o naianei).

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

Me haere ki te psql.

su - postgres
psql

Waihangahia he pātengi raraunga ki te psql

Waihangahia he ripanga ki te whakamātautau pātengi raraunga1.

create database test1;

Huri ki te whakamātautau pātengi raraunga.

postgres=# c test1;

Ka waihangahia te ripanga indexing_table.

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

Te taapiri raraunga.

Ka timata taatau ki te whakauru raraunga. E tatari ana matou mo te 10-20 meneti.

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

Kia kaha ki te hanga i te taapiri katoa.

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

Ka titiro tatou ki nga rekoata kei te ripanga i roto i te whakamatautau raraunga1

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+

Ko te aho te wa o naianei.

Tirohia te rarangi o nga taapiri katoa

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

Te whakamatautau whakaora

Whakaora katoa me te huri i nga WAL katoa e waatea ana.

Kati te Postgresql.

Mukua nga mea katoa mai i te kōpaki /var/lib/pgsql/9.6/data.

Whakahaerehia te /usr/local/bin/backup-fetch.sh hōtuhi hei kaiwhakamahi postgres.

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

Kua oti te tangohanga tārua.

Tāpirihia te recovery.conf ki te kōpaki /var/lib/pgsql/9.6/data me te ihirangi e whai ake nei.

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

Ka timata matou i te PostgreSQL. Ka timata a PostgreSQL i te tukanga whakaora mai i nga WAL kua whakapumautia, katahi ka tuwhera te papaa raraunga. 

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

Whakaora mo tetahi wa.

Mena kei te pirangi matou ki te whakahoki i te papaarangi ki runga i tetahi meneti, ka taapirihia e matou te tawhā recovery_target_time ki recovery.conf - ka tohuhia he aha te wa hei whakaora i te paataka.

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

I muri i te whakaoranga, tirohia te ripanga 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

Ka timata matou i te PostgreSQL. Ka timata a PostgreSQL i te tukanga whakaora mai i nga WAL kua whakapumautia, katahi ka tuwhera te papaa raraunga. 

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

Te whakamātautau

Te whakaputa i te 1GB te papaa raraunga e whakaahuatia ana i konei https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff

Te tono i te rahi o te peere i muri i te whakaputanga 1GB o nga raraunga.

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

Ko te s4cmd he taputapu raina whakahau koreutu mo te mahi me nga raraunga kei te rokiroki Amazon S3. Ko te whaipainga kua tuhia ki te reo whakamaori python, na tenei ka taea te whakamahi i nga punaha whakahaere Windows me Linux.

Tāuta 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

Whakatauritenga o nga hua i runga i te tūtohi.

He kupu whakataki ki te punaha wal-g PostgreSQL

Ka taea e koe te kite, he rite te rahi o Brotli ki te LZMA, engari ka mahia te taapiri i te wa LZ4.

Kōrerorero a te hapori PostgreSQL reo Rūhia: https://t.me/pgsql

Tena koa homai he whetu ki a Github mena ka whakamahi koe wal-g

Source: will.com

Tāpiri i te kōrero