PostgreSQL
pgBackRest 설치
남용2
2019. 10. 11. 13:35
pgBackRest 설치에 대해 설명한다.
참고: https://blog.dbi-services.com/backup-and-restore-postgresql-with-pgbackrest-i/
설치
1. 다운로드
$ wget https://github.com/pgbackrest/pgbackrest/archive/release/2.04.tar.gz
$ tar -xvf 2.04.tar.gz2. 의존성 빌드
문서에서는 ubuntu라서 다른 점이 있다.
yum groupinstall "Development Tools" "Development Libraries"
yum install perl-DBD-Pg perl-JSON perl-Digest-SHA.x86_64 perl-ExtUtils-Embed perl-Compress-Raw-Zlib
dnf update gcc3.소스코드 빌드
$ cd pgbackrest-release-2.04
$ make -C src
make: 디렉터리 '/home/bylee/install/pgbackrest-release-2.04/src' 들어감
gcc -I. -I../libc -std=c99 -O2 -Wfatal-errors -Wall -Wextra -Wwrite-strings -Wswitch-enum -Wconversion -Wformat=2 -Wformat-nonliteral -Wno-clobbered -Wno-missing-field-initializers `perl -MExtUtils::Embed -e ccopts` -DNDEBUG -c command/archive/common.c -o command/archive/common.o
...
gcc -o pgbackrest command/archive/common.o command/archive/get/get.o command/archive/push/push.o command/help/help.o command/command.o common/debug.o common/encode.o common/encode/base64.o common/error.o common/exit.o common/fork.o common/io/handle.o common/ini.o common/lock.o common/log.o common/memContext.o common/regExp.o common/stackTrace.o common/time.o common/type/buffer.o common/type/convert.o common/type/keyValue.o common/type/list.o common/type/string.o common/type/stringList.o common/type/variant.o common/type/variantList.o common/wait.o config/config.o config/define.o config/load.o config/parse.o crypto/cipherBlock.o crypto/hash.o crypto/crypto.o crypto/random.o perl/config.o perl/exec.o postgres/info.o postgres/pageChecksum.o storage/driver/posix/driver.o storage/driver/posix/driverFile.o storage/driver/posix/driverRead.o storage/driver/posix/driverWrite.o storage/fileRead.o storage/fileWrite.o storage/helper.o storage/storage.o main.o -lcrypto `perl -MExtUtils::Embed -e ldopts`
make: 디렉터리 '/home/bylee/install/pgbackrest-release-2.04/src' 나감
[bylee@localhost pgbackrest-release-2.04]$ cd src/
[bylee@localhost src]$ ll
합계 1696
-rw-rw-r--. 1 bylee bylee 24834 7월 6 09:16 Makefile
drwxrwxr-x. 4 bylee bylee 4096 8월 2 11:31 command
drwxrwxr-x. 5 bylee bylee 4096 8월 2 11:31 common
drwxrwxr-x. 2 bylee bylee 4096 8월 2 11:31 config
drwxrwxr-x. 2 bylee bylee 4096 8월 2 11:31 crypto
-rw-rw-r--. 1 bylee bylee 3447 7월 6 09:16 main.c
-rw-rw-r--. 1 bylee bylee 29752 8월 2 11:31 main.o
drwxrwxr-x. 2 bylee bylee 4096 8월 2 11:31 perl
-rwxrwxr-x. 1 bylee bylee 1635656 8월 2 11:31 pgbackrest
drwxrwxr-x. 2 bylee bylee 4096 8월 2 11:31 postgres
drwxrwxr-x. 3 bylee bylee 4096 8월 2 11:31 storage
-rw-rw-r--. 1 bylee bylee 1557 7월 6 09:16 version.h4. 바이러리 파일 옮기기
[bylee@localhost src]$ sudo cp pgbackrest /usr/local/bin
[bylee@localhost pgbackrest-release-2.04]$ sudo chmod 755 /usr/local/bin/pgbackrest
[bylee@localhost pgbackrest-release-2.04]$ pgbackrest
pgBackRest 2.04 - 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.5. 디렉터리 및 파일 생성
[bylee@localhost pgbackrest-release-2.04]$ sudo mkdir -p -m 770 /var/log/pgbackrest
[bylee@localhost pgbackrest-release-2.04]$ sudo chown bylee:bylee /var/log/pgbackrest
[bylee@localhost pgbackrest-release-2.04]$ sudo mkdir -p /etc/pgbackrest
[bylee@localhost pgbackrest-release-2.04]$ sudo mkdir -p /etc/pgbackrest/conf.d
[bylee@localhost pgbackrest-release-2.04]$ sudo touch /etc/pgbackrest/pgbackrest.conf
[bylee@localhost pgbackrest-release-2.04]$ sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
[bylee@localhost pgbackrest-release-2.04]$ sudo chown bylee:bylee /etc/pgbackrest/pgbackrest.conf 6. pgbackrest.conf에 data 디렉터리 path 추가
[bylee@localhost pgbackrest-release-2.04]$ cat /etc/pgbackrest/pgbackrest.conf
[test]
pg1-path=/home/bylee/install/agensgraph/data7. pgbackrest의 repository 생성 및 pgbackrest.conf 파일에 path 추가
[bylee@localhost agensgraph]$ mkdir pgbackrest
[bylee@localhost pgbackrest-release-2.04]$ cat /etc/pgbackrest/pgbackrest.conf
[test]
pg1-path=/home/bylee/install/agensgraph/data
[global]
repo1-path=/home/bylee/install/agensgraph/pgbackrest8. postgresql.conf에 archiving 설정
archive_command = 'pgbackrest --stanza=test archive-push %p'
archive_mode = on
listen_addresses = '*'
log_line_prefix = ''
max_wal_senders = 3
wal_level = hot_standby
--아래처럼 오류가 발생하지만 9.를 실행하면 발생하지 않는다.
ERROR [055]: : unable to open /home/bylee/install/agensgraph/pgbackrest/archive/test/archive.info or /home/bylee/install/agensgraph/pgbackrest/archive/test/archive.info.copy
ERROR [055]: : archive.info does not exist but is required to push/get WAL segments
HINT: is archive_command configured in postgresql.conf?
HINT: has a stanza-create been performed?
HINT: use --no-archive-check to disable archive checks during backup if you have an alternate archiving scheme.
LOG: archive command failed with exit code 55
DETAIL: The failed archive command was: pgbackrest --stanza=test archive-push pg_wal/0000000100000000000000019.pgBackRest stanza 명령어
[bylee@localhost agensgraph]$ pgbackrest --stanza=test --log-level-console=info stanza-create
2018-08-02 14:49:17.590 P00 INFO: stanza-create command begin 2.04: --log-level-console=info --pg1-path=/home/bylee/install/agensgraph/data --repo1-path=/home/bylee/install/agensgraph/pgbackrest --stanza=test
2018-08-02 14:49:18.117 P00 INFO: stanza-create command end: completed successfully (527ms)
[bylee@localhost agensgraph]$ cd pgbackrest/
[bylee@localhost pgbackrest]$ ll
합계 8
drwxr-x---. 3 bylee bylee 4096 8월 2 14:49 archive
drwxr-x---. 3 bylee bylee 4096 8월 2 14:49 backu10.pgBackRest check 명령어
[bylee@localhost pgbackrest]$ pgbackrest --stanza=test --log-level-console=info check
2018-08-02 14:57:21.042 P00 INFO: check command begin 2.04: --log-level-console=info --pg1-path=/home/bylee/install/agensgraph/data --repo1-path=/home/bylee/install/agensgraph/pgbackrest --stanza=test
2018-08-02 14:57:22.786 P00 INFO: WAL segment 000000010000000000000005 successfully stored in the archive at '/home/bylee/install/agensgraph/pgbackrest/archive/test/10-1/0000000100000000/000000010000000000000005-bba3ce39fdb4e02fcada6892e38cfb5dd9be2ff8.gz'
2018-08-02 14:57:22.786 P00 INFO: check command end: completed successfully (1745ms)11.pgBackRest info 명령어
[bylee@localhost pgbackrest]$ pgbackrest --stanza=test --log-level-console=info info
stanza: test
status: error (no valid backups)
db (current)
wal archive min/max (10-1): 000000010000000000000001 / 000000010000000000000005
[bylee@localhost pgbackrest]$ 12.pgBackRest backup 명령어
[bylee@localhost pgbackrest]$ pgbackrest --stanza=test --log-level-console=info backup
2018-08-02 15:02:32.656 P00 INFO: backup command begin 2.04: --log-level-console=info --pg1-path=/home/bylee/install/agensgraph/data --repo1-path=/home/bylee/install/agensgraph/pgbackrest --repo1-retention-full=2 --stanza=test
WARN: no prior backup exists, incr backup has been changed to full
2018-08-02 15:02:33.366 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2018-08-02 15:02:32": backup begins after the next regular checkpoint completes
2018-08-02 15:02:33.684 P00 INFO: backup start archive = 000000010000000000000007, lsn = 0/7000028
2018-08-02 15:02:35.489 P01 INFO: backup file /home/bylee/install/agensgraph/data/base/13345/1255 (616KB, 2%) checksum 1ee010e76edba0806977be3aa136ee7e88a23921
...
2018-08-02 15:02:44.220 P01 INFO: backup file /home/bylee/install/agensgraph/data/base/1/13181 (0B, 100%)
2018-08-02 15:02:44.244 P00 INFO: full backup size = 23.7MB
2018-08-02 15:02:44.244 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2018-08-02 15:02:44.356 P00 INFO: backup stop archive = 000000010000000000000007, lsn = 0/7000130
2018-08-02 15:02:44.819 P00 INFO: new backup label = 20180802-150232F
2018-08-02 15:02:44.850 P00 INFO: backup command end: completed successfully (12195ms)
2018-08-02 15:02:44.850 P00 INFO: expire command begin
2018-08-02 15:02:44.862 P00 INFO: full backup total < 2 - using oldest full backup for 10-1 archive retention
2018-08-02 15:02:44.875 P00 INFO: expire command end: completed successfully (25ms)13.
[bylee@localhost pgbackrest]$ pgbackrest --stanza=test --log-level-console=info info
stanza: test
status: ok
db (current)
wal archive min/max (10-1): 000000010000000000000007 / 000000010000000000000007
full backup: 20180802-150232F
timestamp start/stop: 2018-08-02 15:02:32 / 2018-08-02 15:02:44
wal start/stop: 000000010000000000000007 / 000000010000000000000007
database size: 23.7MB, backup size: 23.7MB
repository size: 2.7MB, repository backup size: 2.7MB
[bylee@localhost pgbackrest]$ pgbackrest --stanza=test --log-level-console=info backup
2018-08-02 15:06:12.402 P00 INFO: backup command begin 2.04: --log-level-console=info --pg1-path=/home/bylee/install/agensgraph/data --repo1-path=/home/bylee/install/agensgraph/pgbackrest --repo1-retention-full=2 --stanza=test
2018-08-02 15:06:12.664 P00 INFO: last backup label = 20180802-150232F, version = 2.04
2018-08-02 15:06:12.974 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2018-08-02 15:06:12": backup begins after the next regular checkpoint completes
2018-08-02 15:06:13.586 P00 INFO: backup start archive = 000000010000000000000009, lsn = 0/9000028
2018-08-02 15:06:14.425 P01 INFO: backup file /home/bylee/install/agensgraph/data/logfile (209.6KB, 81%) checksum f37ca05014aa9d4c281586c536ed5cb9b147ff96
2018-08-02 15:06:14.428 P01 INFO: backup file /home/bylee/install/agensgraph/data/base/13345/16428 (16KB, 87%) checksum 56e3b9a6d8132a39d4208a54833de7ba4d38dd86
2018-08-02 15:06:14.431 P01 INFO: backup file /home/bylee/install/agensgraph/data/pg_xact/0000 (8KB, 90%) checksum be994d6b5e4fb601b5ea7960fc97acf4f23e4c19
2018-08-02 15:06:14.434 P01 INFO: backup file /home/bylee/install/agensgraph/data/global/pg_control (8KB, 93%) checksum 5f29110c6f44c25ad5c070628aeea89cd5849b45
2018-08-02 15:06:14.443 P01 INFO: backup file /home/bylee/install/agensgraph/data/base/13345/16430 (8KB, 96%) checksum 4affe7b0b38ce16e54bfab263decff06f9cc1d0e
2018-08-02 15:06:14.446 P01 INFO: backup file /home/bylee/install/agensgraph/data/base/13345/16419 (8KB, 99%) checksum b7dc7659f95da6867370b27e48e1256dc1fc2ddb
2018-08-02 15:06:14.474 P01 INFO: backup file /home/bylee/install/agensgraph/data/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2018-08-02 15:06:14.482 P00 INFO: incr backup size = 257.6KB
2018-08-02 15:06:14.495 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2018-08-02 15:06:14.605 P00 INFO: backup stop archive = 000000010000000000000009, lsn = 0/9000130
2018-08-02 15:06:15.070 P00 INFO: new backup label = 20180802-150232F_20180802-150612I
2018-08-02 15:06:15.099 P00 INFO: backup command end: completed successfully (2698ms)
2018-08-02 15:06:15.099 P00 INFO: expire command begin
2018-08-02 15:06:15.113 P00 INFO: full backup total < 2 - using oldest full backup for 10-1 archive retention
2018-08-02 15:06:15.125 P00 INFO: expire command end: completed successfully (26ms)
[bylee@localhost pgbackrest]$ pgbackrest --stanza=test --log-level-console=info info
stanza: test
status: ok
db (current)
wal archive min/max (10-1): 000000010000000000000007 / 000000010000000000000009
full backup: 20180802-150232F
timestamp start/stop: 2018-08-02 15:02:32 / 2018-08-02 15:02:44
wal start/stop: 000000010000000000000007 / 000000010000000000000007
database size: 23.7MB, backup size: 23.7MB
repository size: 2.7MB, repository backup size: 2.7MB
incr backup: 20180802-150232F_20180802-150612I
timestamp start/stop: 2018-08-02 15:06:12 / 2018-08-02 15:06:15
wal start/stop: 000000010000000000000009 / 000000010000000000000009
database size: 23.7MB, backup size: 257.8KB
repository size: 2.7MB, repository backup size: 7.8KB
backup reference list: 20180802-150232F
[bylee@localhost pgbackrest]$ pgbackrest --stanza=test --log-level-console=info --type=full backup
...
2018-08-02 15:07:08.842 P01 INFO: backup file /home/bylee/install/agensgraph/data/base/1/13181 (0B, 100%)
2018-08-02 15:07:08.861 P00 INFO: full backup size = 23.7MB
2018-08-02 15:07:08.861 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2018-08-02 15:07:08.967 P00 INFO: backup stop archive = 00000001000000000000000B, lsn = 0/B000130
2018-08-02 15:07:09.547 P00 INFO: new backup label = 20180802-150659F
2018-08-02 15:07:09.578 P00 INFO: backup command end: completed successfully (10666ms)
2018-08-02 15:07:09.578 P00 INFO: expire command begin
2018-08-02 15:07:09.601 P00 INFO: expire command end: completed successfully (23ms)
[bylee@localhost pgbackrest]$ pgbackrest --stanza=test --log-level-console=info info
stanza: test
status: ok
db (current)
wal archive min/max (10-1): 000000010000000000000007 / 00000001000000000000000B
full backup: 20180802-150232F
timestamp start/stop: 2018-08-02 15:02:32 / 2018-08-02 15:02:44
wal start/stop: 000000010000000000000007 / 000000010000000000000007
database size: 23.7MB, backup size: 23.7MB
repository size: 2.7MB, repository backup size: 2.7MB
incr backup: 20180802-150232F_20180802-150612I
timestamp start/stop: 2018-08-02 15:06:12 / 2018-08-02 15:06:15
wal start/stop: 000000010000000000000009 / 000000010000000000000009
database size: 23.7MB, backup size: 257.8KB
repository size: 2.7MB, repository backup size: 7.8KB
backup reference list: 20180802-150232F
full backup: 20180802-150659F
timestamp start/stop: 2018-08-02 15:06:59 / 2018-08-02 15:07:09
wal start/stop: 00000001000000000000000B / 00000001000000000000000B
database size: 23.7MB, backup size: 23.7MB
repository size: 2.7MB, repository backup size: 2.7MB
[bylee@localhost pgbackrest]$ 14. restore를 위해 data 디렉터리 파일들을 삭제한다.
[bylee@localhost agensgraph]$ cd data/
[bylee@localhost data]$ ll
합계 364
-rw-------. 1 bylee bylee 4 8월 2 13:35 AG_VERSION
-rw-------. 1 bylee bylee 3 8월 2 13:35 PG_VERSION
drwx------. 5 bylee bylee 4096 8월 2 13:35 base
drwx------. 2 bylee bylee 4096 8월 2 14:57 global
-rw-------. 1 bylee bylee 215158 8월 2 15:11 logfile
drwx------. 2 bylee bylee 4096 8월 2 13:35 pg_commit_ts
drwx------. 2 bylee bylee 4096 8월 2 13:35 pg_dynshmem
-rw-------. 1 bylee bylee 4513 8월 2 13:35 pg_hba.conf
-rw-------. 1 bylee bylee 1636 8월 2 13:35 pg_ident.conf
drwx------. 4 bylee bylee 4096 8월 2 15:11 pg_logical
drwx------. 4 bylee bylee 4096 8월 2 13:35 pg_multixact
drwx------. 2 bylee bylee 4096 8월 2 14:56 pg_notify
drwx------. 2 bylee bylee 4096 8월 2 13:35 pg_replslot
drwx------. 2 bylee bylee 4096 8월 2 13:35 pg_serial
drwx------. 2 bylee bylee 4096 8월 2 13:35 pg_snapshots
drwx------. 2 bylee bylee 4096 8월 2 14:56 pg_stat
drwx------. 2 bylee bylee 4096 8월 2 15:15 pg_stat_tmp
drwx------. 2 bylee bylee 4096 8월 2 13:35 pg_subtrans
drwx------. 2 bylee bylee 4096 8월 2 13:35 pg_tblspc
drwx------. 2 bylee bylee 4096 8월 2 13:35 pg_twophase
drwx------. 3 bylee bylee 4096 8월 2 15:11 pg_wal
drwx------. 2 bylee bylee 4096 8월 2 13:35 pg_xact
-rw-------. 1 bylee bylee 28355 8월 2 13:35 postgresql.auto.conf
-rw-------. 1 bylee bylee 28500 8월 2 14:56 postgresql.conf
-rw-------. 1 bylee bylee 56 8월 2 14:56 postmaster.opts
-rw-------. 1 bylee bylee 94 8월 2 14:56 postmaster.pid
[bylee@localhost data]$ rm -rf *
[bylee@localhost data]$ ll
합계 0
[bylee@localhost data]$ agens -d postgres
agens: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
[bylee@localhost data]$
15. pgBackRest restore 명령어
[bylee@localhost pgbackrest]$ pgbackrest --stanza=test --log-level-console=info restore
2018-08-02 15:18:23.349 P00 INFO: restore command begin 2.04: --log-level-console=info --pg1-path=/home/bylee/install/agensgraph/data --repo1-path=/home/bylee/install/agensgraph/pgbackrest --stanza=test
2018-08-02 15:18:23.514 P00 INFO: restore backup set 20180802-150659F
2018-08-02 15:18:23.893 P01 INFO: restore file /home/bylee/install/agensgraph/data/base/13345/1255 (616KB, 2%) checksum 1ee010e76edba0806977be3aa136ee7e88a23921
2018-08-02 15:18:23.900 P01 INFO: restore file /home/bylee/install/agensgraph/data/base/13344/1255 (616KB, 5%) checksum 1ee010e76edba0806977be3aa136ee7e88a23921
...
2018-08-02 15:18:31.388 P01 INFO: restore file /home/bylee/install/agensgraph/data/base/1/13181 (0B, 100%)
2018-08-02 15:18:31.389 P00 INFO: write /home/bylee/install/agensgraph/data/recovery.conf
2018-08-02 15:18:31.391 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2018-08-02 15:18:31.394 P00 INFO: restore command end: completed successfully (8045ms)16. restore 명령어 확인
[bylee@localhost data]$ ll
합계 364
-rw-------. 1 bylee bylee 4 8월 2 13:35 AG_VERSION
-rw-------. 1 bylee bylee 3 8월 2 13:35 PG_VERSION
-rw-------. 1 bylee bylee 229 8월 2 15:07 backup_label
drwx------. 5 bylee bylee 4096 8월 2 15:18 base
drwx------. 2 bylee bylee 4096 8월 2 15:18 global
-rw-------. 1 bylee bylee 214902 8월 2 15:06 logfile
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_commit_ts
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_dynshmem
-rw-------. 1 bylee bylee 4513 8월 2 13:35 pg_hba.conf
-rw-------. 1 bylee bylee 1636 8월 2 13:35 pg_ident.conf
drwx------. 4 bylee bylee 4096 8월 2 15:18 pg_logical
drwx------. 4 bylee bylee 4096 8월 2 15:18 pg_multixact
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_notify
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_replslot
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_serial
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_snapshots
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_stat
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_stat_tmp
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_subtrans
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_tblspc
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_twophase
drwx------. 3 bylee bylee 4096 8월 2 15:18 pg_wal
drwx------. 2 bylee bylee 4096 8월 2 15:18 pg_xact
-rw-------. 1 bylee bylee 28355 8월 2 13:35 postgresql.auto.conf
-rw-------. 1 bylee bylee 28500 8월 2 14:56 postgresql.conf
-rw-rw-rw-. 1 bylee bylee 90 8월 2 15:18 recovery.conf
[bylee@localhost data]$ cat recovery.conf
restore_command = 'pgbackrest --log-level-console=info --stanza=test archive-get %f "%p"'
[bylee@localhost data]$ cd ..
[bylee@localhost agensgraph]$ ag_ctl -D data start
waiting for server to start.... done
server started
[bylee@localhost agensgraph]$ agens -d postgres
agens (AgensGraph 1.4devel, based on PostgreSQL 10.3)
Type "help" for help.
postgres=# set graph_path to p;
SET
postgres=# match (n) return n;
n
--------------------------
v[3.1]{"name": "bylee"}
v[3.2]{"name": "bylee1"}
v[3.3]{"name": "bylee2"}
(3 rows)
postgres=# 17. backup list에서 선택해서 restore
[bylee@localhost pgbackrest]$ pgbackrest --stanza=test --log-level-console=info --set=20180802-150232F_20180802-150612I restore