HOT Backup & Recovery 테스트
V12부터 recovery.conf 파일이 없어졌으므로 9.6 버전과 12버전만 테스트를 진행 함
########################
Backup & Archive 설정
########################
mkdir -p /archive/pg96
mkdir -p /archive/pg96/temp
mkdir -p /backup/pg96
mkdir -p /archive/pg12
mkdir -p /archive/pg12/temp
mkdir -p /backup/pg12
chown -R postgres:postgres /archive /backup
##############
Hot backup
##############
1. 기존에 백업 받았던 파일들 삭제
rm -rf /backup/pg96/*
2. Begin backup 수행
select pg_start_backup('hotbackup',true);
$PGDATA 안에 backup_label 파일이 생성되면서 정보가 저장 됨
3. O/S 상에서 File Copy
cp -a $PGDATA /backup/pg96/
4. End backup 수행
select pg_stop_backup();
$PGDATA 안에 backup_label 파일이 없어 짐
##########
9.6.24
##########
아카이브 설정
echo "
wal_level = replica
archive_mode = on
archive_command = 'dd conv=fdatasync bs=256k if=%p of=/archive/pg96/temp/%f && mv -f /archive/pg96/temp/%f /archive/pg96' " >> $PGDATA/postgres.conf
PG start
archive가 정상적으로 동작 되는지 테스트
[local]:5496 postgres@postgres # select pg_current_xlog_location();
+--------------------------+
| pg_current_xlog_location |
+--------------------------+
| 0/1589CE0 |
+--------------------------+
[postgres@postgres pg96]$ ls $PGDATA/pg_xlog
000000010000000000000001 archive_status
[local]:5496 postgres@postgres # select pg_switch_xlog() ;
+----------------+
| pg_switch_xlog |
+----------------+
| 0/1589CF8 |
+----------------+
[postgres@postgres pg96]$ ls $PGDATA/pg_xlog
000000010000000000000001 000000010000000000000002 archive_status
[postgres@postgres pg96]$ ls /archive/pg96
000000010000000000000001 temp
Hot backup 시작
[local]:5496 postgres@postgres # select pg_start_backup('hotbackup',true);
+-----------------+
| pg_start_backup |
+-----------------+
| 0/3000028 |
+-----------------+
(1 row)
[postgres@postgres pg96]$ cat $PGDATA/backup_label
START WAL LOCATION: 0/3000028 (file 000000010000000000000003)
CHECKPOINT LOCATION: 0/3000060
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2022-04-22 16:35:46 KST
LABEL: hotbackup
File Copy
[postgres@postgres pg96]$ cp -a $PGDATA /backup/pg96/
[postgres@postgres pg96]$ ls /backup/pg96
pg9.6
[postgres@postgres pg96]$ ls /backup/pg96/pg9.6
backup_label global pg_clog pg_dynshmem pg_ident.conf pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase pg_xlog postgresql.conf postmaster.pid
base logfile pg_commit_ts pg_hba.conf pg_log pg_multixact pg_replslot pg_snapshots pg_stat_tmp pg_tblspc PG_VERSION postgresql.auto.conf postmaster.opts
End backup
[local]:5496 postgres@postgres # select pg_stop_backup();
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
+----------------+
| pg_stop_backup |
+----------------+
| 0/3000130 |
+----------------+
(1 row)
[postgres@postgres pg96]$ cat $PGDATA/backup_label
cat: /data/pg9.6/backup_label: No such file or directory
Test Data 추가
[local]:5496 postgres@postgres # \l
List of databases
+-----------+----------+----------+-------------+-------------+-----------------------+
| Name | Owner | Encoding | Collate | Ctype | Access privileges |
+-----------+----------+----------+-------------+-------------+-----------------------+
| postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
| template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +|
| | | | | | postgres=CTc/postgres |
| template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +|
| | | | | | postgres=CTc/postgres |
+-----------+----------+----------+-------------+-------------+-----------------------+
(3 rows)
[local]:5496 postgres@postgres # create database test ;
CREATE DATABASE
Time: 543.756 ms
[local]:5496 postgres@postgres # \c test
You are now connected to database "test" as user "postgres".
[local]:5496 postgres@test # create table test ( id serial , insert_date timestamp without time zone ) ;
CREATE TABLE
Time: 46.379 ms
[local]:5496 postgres@test # insert into test (insert_date) values (current_timestamp) ;
INSERT 0 1
Time: 44.350 ms
[local]:5496 postgres@test # insert into test (insert_date) values (current_timestamp) ;
INSERT 0 1
Time: 42.240 ms
[local]:5496 postgres@test # insert into test (insert_date) values (current_timestamp) ;
INSERT 0 1
Time: 12.298 ms
[local]:5496 postgres@test # insert into test (insert_date) values (current_timestamp) ;
INSERT 0 1
Time: 13.200 ms
[local]:5496 postgres@test # insert into test (insert_date) values (current_timestamp) ;
INSERT 0 1
Time: 12.544 ms
[local]:5496 postgres@test # select * from test ;
+----+----------------------------+
| id | insert_date |
+----+----------------------------+
| 1 | 2022-04-22 16:45:40.541072 |
| 2 | 2022-04-22 16:45:44.366287 |
| 3 | 2022-04-22 16:45:45.156317 |
| 4 | 2022-04-22 16:45:46.165845 |
| 5 | 2022-04-22 16:45:46.999159 |
+----+----------------------------+
(5 rows)
장애 발생
장애가 발생한 것으로 가정하기 위하여 PG Down
archive가 어디까지 적용 되었는지 확인
[postgres@postgres pg9.6]$ ls /archive/pg96
000000010000000000000001 000000010000000000000002 000000010000000000000003 000000010000000000000003.00000028.backup 000000010000000000000004 temp
xlog는 어디까지 생성되어 있는지 확인
[postgres@postgres pg9.6]$ ls $PGDATA/pg_xlog
000000010000000000000003.00000028.backup 000000010000000000000004 000000010000000000000005 000000010000000000000006 archive_status
[postgres@postgres pg9.6]$ ls $PGDATA/pg_xlog/archive_status
000000010000000000000003.00000028.backup.done 000000010000000000000004.done
Recovery
월래 사용하던 $PGDATA /data/pg9.6 대신에 새로운 디렉터리를 생성 후 데이터 복구 테스트
[root@postgres ~]# mkdir /recovery
[root@postgres ~]# chown -R postgres:postgres /recovery
[postgres@postgres pg96]$ cp -a /backup/pg96/pg9.6/ /recovery
[postgres@postgres pg96]$ ls /recovery
pg9.6
postmaster.pid 삭제
[postgres@postgres pg9.6]$ cd /recovery/pg9.6
[postgres@postgres pg9.6]$ cat postmaster.pid
1473
/data/pg9.6
1650611620
5496
/tmp
*
5496001 0
[postgres@postgres pg9.6]$ rm -rf postmaster.pid
불필요한 기존 로그 삭제
[postgres@postgres pg9.6]$ rm -rf pg_log/*
recovery.conf 파일 생성
[postgres@postgres pg9.6]$ vi recovery.conf
restore_command = 'cp /archive/pg96/%f %p'
[postgres@postgres pg9.6]$ export PGDATA=/recovery/pg9.6
[postgres@postgres pg9.6]$ echo $PGDATA
/recovery/pg9.6
PG 기동
[postgres@postgres pg9.6]$ pg_ctl start -D $PGDATA -w
waiting for server to start....2022-04-22 17:11:19 KST [1647]: [1-1] user=,db=,remote=LOG: redirecting log output to logging collector process
2022-04-22 17:11:19 KST [1647]: [2-1] user=,db=,remote=HINT: Future log output will appear in directory "pg_log".
. done
server started
Recovery가 완료 되면 recovery.conf 파일이 recovery.done으로 변경 됨
[postgres@postgres pg9.6]$ ls $PGDATA/recovery*
/recovery/pg9.6/recovery.done
[postgres@postgres pg9.6]$ cat $PGDATA/postmaster.pid
1647
/recovery/pg9.6
1650615079
5496
/tmp
*
5496001 1
[postgres@postgres pg9.6]$ ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1592 0.0 0.0 115652 2188 pts/1 S 16:59 0:00 -bash
postgres 1716 0.0 0.0 155448 1840 pts/1 R+ 17:32 0:00 \_ ps -u postgres uf
postgres 1424 0.0 0.0 115648 2156 pts/0 S+ 16:07 0:00 -bash
postgres 1647 0.0 0.1 275460 15448 pts/1 S 17:11 0:00 /engine/pg9.6/bin/postgres -D /recovery/pg9.6
postgres 1648 0.0 0.0 126108 708 ? Ss 17:11 0:00 \_ postgres: logger process
postgres 1651 0.0 0.0 275580 2868 ? Ss 17:11 0:00 \_ postgres: checkpointer process
postgres 1652 0.0 0.0 275460 2128 ? Ss 17:11 0:00 \_ postgres: writer process
postgres 1659 0.0 0.0 275460 5028 ? Ss 17:11 0:00 \_ postgres: wal writer process
postgres 1660 0.0 0.0 275864 1984 ? Ss 17:11 0:00 \_ postgres: autovacuum launcher process
postgres 1661 0.0 0.0 128228 904 ? Ss 17:11 0:00 \_ postgres: archiver process last was 00000002.history
postgres 1662 0.0 0.0 128228 1012 ? Ss 17:11 0:00 \_ postgres: stats collector process
데이터 확인
[local]:5496 postgres@postgres # \l
List of databases
+-----------+----------+----------+-------------+-------------+-----------------------+
| Name | Owner | Encoding | Collate | Ctype | Access privileges |
+-----------+----------+----------+-------------+-------------+-----------------------+
| postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
| template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +|
| | | | | | postgres=CTc/postgres |
| template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +|
| | | | | | postgres=CTc/postgres |
| test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
+-----------+----------+----------+-------------+-------------+-----------------------+
(4 rows)
[local]:5496 postgres@postgres # \c test
You are now connected to database "test" as user "postgres".
[local]:5496 postgres@test # \d
List of relations
+--------+-------------+----------+----------+
| Schema | Name | Type | Owner |
+--------+-------------+----------+----------+
| public | test | table | postgres |
| public | test_id_seq | sequence | postgres |
+--------+-------------+----------+----------+
(2 rows)
[local]:5496 postgres@test # select * from test ;
+----+----------------------------+
| id | insert_date |
+----+----------------------------+
| 1 | 2022-04-22 16:45:40.541072 |
| 2 | 2022-04-22 16:45:44.366287 |
| 3 | 2022-04-22 16:45:45.156317 |
| 4 | 2022-04-22 16:45:46.165845 |
| 5 | 2022-04-22 16:45:46.999159 |
+----+----------------------------+
(5 rows)
기존 DataDirectory로 복구 파일 cpoy
PG 종료
[postgres@postgres pg9.6]$ pg_ctl -D $PGDATA stop -mf -w
기존 datadirectory안의 파일 삭제
[postgres@postgres pg9.6]$ rm -rf /data/pg9.6/*
복구한 파일 복사
[postgres@postgres pg9.6]$ cp -r /recovery/pg9.6/* /data/pg9.6/
PG 기동
[postgres@postgres pg9.6]$ export PGDATA=/data/pg9.6
[postgres@postgres pg9.6]$ pg_ctl -D $PGDATA logfile start
#######
12.10
#######
아카이브 설정
echo "
wal_level = replica
archive_mode = on
archive_command = 'dd conv=fdatasync bs=256k if=%p of=/archive/pg12/temp/%f && mv -f /archive/pg12/temp/%f /archive/pg12' " >> $PGDATA/postgres.conf
PG 기동
archive가 정상적으로 동작 되는지 테스트
[local]:5412 postgres@postgres # select pg_current_wal_lsn();
+--------------------+
| pg_current_wal_lsn |
+--------------------+
| 0/1686B50 |
+--------------------+
(1 row)
[postgres@postgres pg12]$ ls $PGDATA/pg_wal
000000010000000000000001 archive_status
[local]:5412 postgres@postgres # select pg_switch_wal();
+---------------+
| pg_switch_wal |
+---------------+
| 0/1688528 |
+---------------+
(1 row)
[postgres@postgres pg12]$ ls $PGDATA/pg_wal
000000010000000000000001 000000010000000000000002 archive_status
[postgres@postgres pg12]$ ls $PGDATA/pg_wal/archive_status
000000010000000000000001.done
[postgres@postgres pg12]$ ls /archive/pg12
000000010000000000000001 temp
Hot backup 시작
[local]:5412 postgres@postgres # select pg_start_backup('hotbackup',true);
+-----------------+
| pg_start_backup |
+-----------------+
| 0/3000028 |
+-----------------+
(1 row)
[postgres@postgres pg12]$ cat $PGDATA/backup_label
START WAL LOCATION: 0/3000028 (file 000000010000000000000003)
CHECKPOINT LOCATION: 0/3000060
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2022-04-22 17:53:13 KST
LABEL: hotbackup
START TIMELINE: 1
File Copy
[postgres@postgres pg12]$ cp -a $PGDATA /backup/pg12/
[postgres@postgres pg12]$ ls /backup/pg12
pg12.10
[postgres@postgres pg12]$ ls /backup/pg12/pg12.10/
backup_label current_logfiles logfile pg_dynshmem pg_ident.conf pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase pg_wal postgresql.auto.conf postmaster.opts
base global pg_commit_ts pg_hba.conf pg_log pg_multixact pg_replslot pg_snapshots pg_stat_tmp pg_tblspc PG_VERSION pg_xact postgresql.conf postmaster.pid
End backup
[local]:5412 postgres@postgres # select pg_stop_backup();
NOTICE: all required WAL segments have been archived
+----------------+
| pg_stop_backup |
+----------------+
| 0/3000138 |
+----------------+
(1 row)
[postgres@postgres pg12]$ cat $PGDATA/backup_label
cat: /data/pg12.10/backup_label: No such file or directory
Test Data 추가
[local]:5412 postgres@postgres # \l
List of databases
+-----------+----------+----------+-------------+-------------+-----------------------+
| Name | Owner | Encoding | Collate | Ctype | Access privileges |
+-----------+----------+----------+-------------+-------------+-----------------------+
| postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
| template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +|
| | | | | | postgres=CTc/postgres |
| template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +|
| | | | | | postgres=CTc/postgres |
+-----------+----------+----------+-------------+-------------+-----------------------+
(3 rows)
[local]:5412 postgres@postgres # create database test ;
CREATE DATABASE
[local]:5412 postgres@postgres # \c test
You are now connected to database "test" as user "postgres".
[local]:5412 postgres@test # create table test ( id serial , insert_date timestamp without time zone ) ;
CREATE TABLE
[local]:5412 postgres@test # insert into test (insert_date) values (current_timestamp) ;
INSERT 0 1
[local]:5412 postgres@test # insert into test (insert_date) values (current_timestamp) ;
INSERT 0 1
[local]:5412 postgres@test # insert into test (insert_date) values (current_timestamp) ;
INSERT 0 1
[local]:5412 postgres@test # select * from test ;
+----+----------------------------+
| id | insert_date |
+----+----------------------------+
| 1 | 2022-04-22 17:56:43.653391 |
| 2 | 2022-04-22 17:56:44.822594 |
| 3 | 2022-04-22 17:56:45.835276 |
+----+----------------------------+
(3 rows)
장애 발생
장애가 발생한 것으로 가정하기 위하여 PG Down
archive가 어디까지 적용 되었는지 확인
[postgres@postgres pg12]$ ls /archive/pg12
000000010000000000000001 000000010000000000000002 000000010000000000000003 000000010000000000000003.00000028.backup 000000010000000000000004 temp
wal로그 파일 확인
[postgres@postgres pg12]$ ls $PGDATA/pg_wal
000000010000000000000003.00000028.backup 000000010000000000000005 000000010000000000000006 000000010000000000000007 archive_status
[postgres@postgres pg12]$ ls $PGDATA/pg_wal/archive_status
000000010000000000000003.00000028.backup.done
Recovery
월래 사용하던 $PGDATA /data/pg12.10 대신에 새로운 디렉터리를 생성 후 데이터 복구 테스트
[root@postgres ~]# mkdir /recovery
[root@postgres ~]# chown -R postgres:postgres /recovery
[postgres@postgres pg12]$ cp -a /backup/pg12/pg12.10/ /recovery
[postgres@postgres pg12]$ ls /recovery/
pg12.10
postmaster.pid 삭제
[postgres@postgres pg12]$ cd /recovery/pg12.10
[postgres@postgres pg12.10]$ cat postmaster.pid
1802
/data/pg12.10
1650617120
5412
/tmp
*
5412001 3
ready
[postgres@postgres pg12.10]$ rm -rf postmaster.pid
불필요한 기존 로그 삭제
[postgres@postgres pg12.10]$ rm -rf pg_log/*
기존버전과 다르게 restore_command는 postgresql.conf 파일에 추가 하고, recovery.signal 파일을 생성해 준다.
echo "
restore_command = 'cp /archive/pg12/%f %p'" >> /recovery/pg12.10/postgresql.conf
touch /recovery/pg12.10/recovery.signal
PG 기동
[postgres@postgres pg12.10]$ pg_ctl start -D $PGDATA -w
waiting for server to start....2022-04-22 18:29:46 KST [1945]: [1-1] user=,db=,remote=LOG: starting PostgreSQL 12.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-04-22 18:29:46 KST [1945]: [2-1] user=,db=,remote=LOG: listening on IPv4 address "0.0.0.0", port 5412
2022-04-22 18:29:46 KST [1945]: [3-1] user=,db=,remote=LOG: listening on IPv6 address "::", port 5412
2022-04-22 18:29:46 KST [1945]: [4-1] user=,db=,remote=LOG: listening on Unix socket "/tmp/.s.PGSQL.5412"
2022-04-22 18:29:46 KST [1945]: [5-1] user=,db=,remote=LOG: redirecting log output to logging collector process
2022-04-22 18:29:46 KST [1945]: [6-1] user=,db=,remote=HINT: Future log output will appear in directory "pg_log".
. done
server started
Recovery가 완료 되면 recovery.signal 파일 없어진다.
[postgres@postgres pg12.10]$ ls /recovery/pg12.10/recovery.signal
ls: cannot access /recovery/pg12.10/recovery.signal: No such file or directory
[postgres@postgres pg12.10]$ cat postmaster.pid
1945
/recovery/pg12.10
1650619786
5412
/tmp
*
5412001 6
ready
[postgres@postgres pg12.10]$ ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1813 0.0 0.0 115664 2164 pts/1 S 17:45 0:00 -bash
postgres 1968 0.0 0.0 155448 1836 pts/1 R+ 18:32 0:00 \_ ps -u postgres uf
postgres 1777 0.0 0.0 115660 2164 pts/0 S+ 17:45 0:00 -bash
postgres 1945 0.0 0.6 402944 51164 ? Ss 18:29 0:00 /engine/pg12.10/bin/postgres -D /recovery/pg12.10
postgres 1946 0.0 0.0 129260 908 ? Ss 18:29 0:00 \_ postgres: logger
postgres 1950 0.0 0.0 403088 2944 ? Ss 18:29 0:00 \_ postgres: checkpointer
postgres 1951 0.0 0.0 403104 2160 ? Ss 18:29 0:00 \_ postgres: background writer
postgres 1953 0.0 0.0 131380 1008 ? Ss 18:29 0:00 \_ postgres: stats collector
postgres 1958 0.0 0.0 402944 5060 ? Ss 18:29 0:00 \_ postgres: walwriter
postgres 1959 0.0 0.0 403532 2064 ? Ss 18:29 0:00 \_ postgres: autovacuum launcher
postgres 1960 0.0 0.0 131380 988 ? Ss 18:29 0:00 \_ postgres: archiver last was 00000002.history
postgres 1961 0.0 0.0 403528 1628 ? Ss 18:29 0:00 \_ postgres: logical replication launcher
데이터 확인
[local]:5412 postgres@postgres # \l
List of databases
+-----------+----------+----------+-------------+-------------+-----------------------+
| Name | Owner | Encoding | Collate | Ctype | Access privileges |
+-----------+----------+----------+-------------+-------------+-----------------------+
| postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
| template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +|
| | | | | | postgres=CTc/postgres |
| template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +|
| | | | | | postgres=CTc/postgres |
| test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
+-----------+----------+----------+-------------+-------------+-----------------------+
(4 rows)
[local]:5412 postgres@postgres # \c test
You are now connected to database "test" as user "postgres".
[local]:5412 postgres@test # select * from test ;
+----+----------------------------+
| id | insert_date |
+----+----------------------------+
| 1 | 2022-04-22 17:56:43.653391 |
| 2 | 2022-04-22 17:56:44.822594 |
| 3 | 2022-04-22 17:56:45.835276 |
+----+----------------------------+
(3 rows)
기존 DataDirectory로 복구 파일 cpoy
PG 종료
[postgres@postgres pg12.10]$ pg_ctl -D $PGDATA stop -mf -w
기존 datadirectory안의 파일 삭제
[postgres@postgres pg12.10]$ rm -rf /data/pg12.10/*
복구한 파일 복사
[postgres@postgres pg12.10]$ cp -r /recovery/pg12.10/* /data/pg12.10/
PG 기동
[postgres@postgres pg12.10]$ export PGDATA=/data/pg12.10/
[postgres@postgres pg12.10]$ pg_ctl -D $PGDATA logfile start
분류 전체보기
- Hot Backup & Recovery 2022.04.22
Hot Backup & Recovery
2022. 4. 22. 18:38
728x90
반응형
반응형