PostgreSQL pgBackRest

2019-09-09

Language :

pgBackRest

PostgreSQL의 안정적인 백업과 복원을 지원
기본적으로 증분 백업이며 전체 백업, 차등 백업도 가능하다.
PostgreSQL role에 postgres가 있어야 한다.
- CentOS7과 PostgreSQL9.6을 기준으로 작성 (pgBackRest User Guide)

pgBackRest 설치 및 설정 ( Active, Standby )

pgBackRest 다운로드

# wget --no-check-certificate -q -O - https://github.com/pgbackrest/pgbackrest/archive/release/2.15.1.tar.gz | tar zx -C /root

Install build dependencies

# yum install -y build-essential gcc make openssl-devel libxml2-devel perl-ExtUtils-Embed

Check for 64-bit integers

# perl -V | grep USE_64_BIT_INT
	USE_64_BIT_INT USE_ITHREADS USE_LARGE_FILES

Build pgBackRest package

# cd /root/pgbackrest-release-2.15.1/src && ./configure
# make -s -C /root/pgbackrest-release-2.15.1/src

checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for gcc option to accept ISO C99... -std=gnu99
checking for perl_parse in -lperl... yes
checking for EVP_get_digestbyname in -lcrypto... yes
checking for SSL_new in -lssl... yes
checking for xmlSaveToBuffer in -lxml2... yes
checking for deflate in -lz... yes
configure: creating ./config.status
config.status: creating Makefile
config.status: creating build.auto.h

Copy pgBackRest binary

# cp /root/pgbackrest-release-2.15.1/src/pgbackrest /usr/bin
# chmod 755 /usr/bin/pgbackrest

추가 모듈 설치

pgBackRest는 추가 모듈이 필요한 내장 Perl을 포함하고 있다.

# yum install -y perl perl-Time-HiRes perl-Digest-SHA perl-DBD-Pg perl-JSON-PP

Dependency Installed:
perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7
perl-DBI.x86_64 0:1.627-4.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7
perl-Digest.noarch 0:1.17-245.el7 perl-IO-Compress.noarch 0:2.061-2.el7
perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7
perl-version.x86_64 3:0.99.07-3.el7

Complete!

설정 파일과 폴더 생성

# mkdir -p -m 770 /var/log/pgbackrest
# chown postgres:postgres /var/log/pgbackrest
# mkdir -p /etc/pgbackrest/conf.d
# touch /etc/pgbackrest/pgbackrest.conf
# chmod 640 /etc/pgbackrest/pgbackrest.conf
# chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

정상 설치 확인

# su - postgres
# pgbackrest

pgBackRest 2.15 - General help

Usage:
pgbackrest [options] [command]

Commands:
archive-get Get a WAL segment from the archive.
archive-push Push a WAL segment to the archive.
backup Backup a database cluster.
check Check the configuration.
expire Expire backups that exceed retention.
help Get help.
info Retrieve information about backups.
restore Restore a database cluster.
stanza-create Create the required stanza data.
stanza-delete Delete a stanza.
stanza-upgrade Upgrade a stanza.
start Allow pgBackRest processes to run.
stop Stop pgBackRest processes from running.
version Get version.

Use 'pgbackrest help [command]' for more information.

Active : Database Cluster 설정 변경

# vi /data/pgsql/postgresql.conf
data_directory = '/data/pgsql'
wal_level = replica
archive_mode = on			// WAL 보관 기능 활성화
archive_command = 'pgbackrest --stanza=dico archive-push %p'
max_wal_senders = 3
log_filename = 'postgresql.log'	// 복잡한 자동화 가능
log_line_prefix = ''			// 로그 출력을 짧게 유지
# /usr/pgsql-9.6/bin/pg_ctl -D /data/pgsql -l /data/pgsql/logfile restart

WAL 세그먼트를 아카이브할 때 저장소에 도달하는 시간이 60초(기본값) 이상이면 archive_timeout 설정 필요

환경 변수에 로그 경로 설정

# bash -c 'export PGBACKREST_LOG_PATH=/path/set/by/env && pgbackrest --log-level-console=error help backup log-path'

pgBackRest 2.15 - 'backup' command - 'log-path' option help

Path where log files are stored.

The log path provides a location for pgBackRest to store log files. Note that
if log-level-file=off then no log path is required.

current: /path/set/by/env
default: /var/log/pgbackrest

pgbackrest 설정 파일 작성

Active (102)

# vi /etc/pgbackrest/pgbackrest.conf
# Configure Cluster Stanza
[dico]
pg1-path = /data/pgsql
pg1-port = 5432

# Configure the pgBackRest repository path
[global]
repo1-host=192.168.56.101
repo1-host-user=postgres
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=3
repo1-retention-archive=2
process-max=2
log-level-console=info
log-level-file=debug

[global:archive-push]
compress-level=3

Standby (101)

# vi /etc/pgbackrest/pgbackrest.conf
[dico]
pg1-path = /data/pgsql
pg1-port = 5432
pg1-host=192.168.56.102
pg1-host-user=postgres

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=3
repo1-retention-archive=2
process-max=2
log-level-console=info
log-level-file=detail
start-fast=y
stop-auto=y

[global:archive-push]
compress-level=3

SSH 설정 (user : postgres)
pgBackRest는 기본적으로 SSH 22번을 사용한다.

Standby / Active

$ ssh-keygen -t rsa -b 4096 -N ""
$ restorecon -R ~/.ssh
$ cp ~/.ssh/id_rsa ~/.ssh/id_rsa.enc
$ openssl rsa -in ~/.ssh/id_rsa.enc -out ~/.ssh/id_rsa

Active / Standby

$ ssh root@192.168.56.101 cat ~/.ssh/id_rsa.pub | tee -a ~/.ssh/authorized_keys
$ chmod 700 ~/.ssh
$ chmod 600 ~/.ssh/authorized_keys

Standby / Active 확인

$ ssh postgres@192.168.56.101

Standby 에서 백업 실행

저장소 생성

# mkdir -p /var/lib/pgbackrest
# chown postgres.postgres /var/lib/pgbackres

Stanza 생성

$ pgbackrest --stanza=dico stanza-create

P00 INFO: stanza-create command end: completed successfully

저장소 구성 테스트.
pgBackRest 및 archive_command 설정이 아카이브 및 백업에 대해 올바르게 구성되었는지 확인
오류났을 때 굳이 stop할 필요없이 오류 부분 수정하고 다시 check 하면 된다.

$ pgbackrest --stanza=dico check

P00 INFO: check command end: completed successfully

백업 실행
옵션 type 기본값은 incr 증분 백업. 그외 설정값은 diff 차등 백업, full 전체 백업. (--type=incr)
증분 백업은 처음엔 전체 백업된다.

$ pgbackrest --stanza=dico backup

P00 INFO: expire command end: completed successfully

정보 보기
status는 backup 실행 전에 확인하면 ‘status: error (no valid backups)’ 라고 뜬다.

$ pgbackrest --stanza=dico info

stanza: dico
status: ok
cipher: none

db (current)
wal archive min/max (9.6-1): 0000000100000000000000F1/0000000100000000000000F1
full backup: 20190727-160357F
timestamp start/stop: 2019-07-27 16:03:57 / 2019-07-27 16:04:24
wal start/stop: 0000000100000000000000F1 / 0000000100000000000000F1
database size: 832.7MB, backup size: 832.7MB
repository size: 187.5MB, repository backup size: 187.5MB

Active에서 복원 실행

복원은 PostgreSQL 호스트에서 실행할 수 있다.

$ /usr/pgsql-9.6/bin/pg_ctl -D /data/pgsql stop
$ find /data/pgsql -mindepth 1 -delete
$ pgbackrest --stanza=dico restore

Stanza 삭제

$ pgbackrest --stanza=dico stop
$ /usr/pgsql-9.6/bin/pg_ctl -D /data/pgsql stop
$ pgbackrest --stanza=dico stanza-delete

민갤

Back-End Developer

백엔드 개발자입니다.

Profile Articles

PostgreSQL 확장 기능 postgres_fdw

PostgreSQL 확장 기능 postgre_fdw를 사용하여 외부 서버와 Select Union

SSH 설치 & 비밀번호 없이 로그인하기

Linux SSH 설치하여 원격 서버에서 로그인 없이 접속