Get best of the week

PostgreSQL Wal-g备份系统介绍

WAL-G是用于将PostgreSQL备份到云的简单有效的工具。通过其基本功能，它成为了流行的WAL-E工具的继承者，但在Go中进行了重写。但是WAL-G中有一个重要的新功能-增量副本。WAL-G增量副本将存储文件页面，这些页面与先前的备份版本已更改。WAL-G实现了许多用于并行化备份的技术。WAL-G比WAL-E快得多。


wal-g的详细信息可以在文章：超频备份中找到。Yandex讲座


S3存储协议已变得流行于数据存储。S3的优点之一是能够通过API进行访问，从而可以与存储库进行灵活的交互，包括公共读取访问，而信息仅由授权人员在存储库中更新。


有几种在S3协议下运行的存储的开放和私有实现。今天，我们将研究一种用于组织小型存储库的流行解决方案-Minio。


为了测试wal-g，一台PostgreSQL服务器是合适的，并且Minio被用作S3的替代品。


迷你服务器


Minio安装


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

在/etc/minio/minio.conf中编辑AccessKey和SecretKey


vi /etc/minio/minio.conf

如果在Minio之前不使用nginx，则需要进行更改


--address 127.0.0.1:9000

--address 0.0.0.0:9000

生成并添加到/etc/minio/minio.conf中的MINIO_ACCESS_KEY和MINIO_SECRET_KEY


# Custom username or access key of minimum 3 characters in length.
#MINIO_ACCESS_KEY=

# Custom password or secret key of minimum 8 characters in length.
#MINIO_SECRET_KEY=

启动Minio


systemctl start minio

web- Minio http://ip---minio:9000 (, pg-backups).



WAL-G rpm ( ). Github, Fedora COPR.


RPM-based .


wal-g rpm , /etc/wal-g.d/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-g.d/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 — , base- -, -. , . , , .


.


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 archive PostgreSQL 10 , replica 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 .


test1.


create database test1;

test.


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

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+

.


WAL:


select pg_switch_xlog();
 PostgreSQL  10:
select pg_switch_wal();

.


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


/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

Backup extraction complete.


recovery.conf /var/lib/pgsql/9.6/data .


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

PostgreSQL. PostgreSQL recovery WAL, .


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

.


, recovery.conf recovery_target_time — .


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 recovery WAL, .


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

PostgreSQL 12 -:


  • restore_command recovery_target_time postgresql.conf
  • $PGDATA/recovery.signal


1GB https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff


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

零配件


  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的聊天室：https : //t.me/pgsql


如果您使用wal-g，请在Github上加一个星标

Source: https://habr.com/ru/post/undefined/

More articles:


All Articles