PostgreSQL Backup
1. SQL 덤프2. 파일 시스템 기반 백업
3. 아카이브 모드 백업
cf)
- Physical Backup
- Logical Backup
- Pg_dump backup
- Pg_dumpall Backup
- Pg_base Backup
physical backup과 logical backup의 차이점
https://backup.ninja/news/Database-Backups-101-Logical-vs-Physical-Backups
논리적 데이터베이스 백업은 바이너리 형식으로 추출 및 저장된 데이터베이스의 논리적 정보 복사본입니다.
물리적 데이터베이스 백업은 전체 데이터베이스 내용, 구조, 테이블 및 데이터베이스를 복원하는 데 필요한 모든 관련 트랜잭션의 복사본입니다.
| Physical Backup | Logical Backup |
|---|---|
| 물리적 데이터베이스 백업은 데이터베이스를 저장하고 복구하는 데 사용되는 물리적 파일의 백업 | 논리 데이터베이스 백업은 데이터베이스에서 검색된 논리 파일의 백업 |
| 여기에는 데이터 파일, 제어 파일 및 아카이브된 리두 로그가 포함됩니다. | 뷰, 프로시저, 함수 및 테이블을 포함 |
| 데이터가 실행 중이거나 중지되었을 때 데이터 파일 복사 | EXPORT 키워드를 사용하여 논리적 백업 완료 |
| 사용자는 단기간에 전체 데이터베이스를 복원 | 이는 사용자가 데이터베이스 복사본을 다른 위치로 복원하거나 전송하려는 경우에 유용 |
| 논리적 백업보다 더 안전 | 물리적 백업에 비해 덜 안전 |
pg_dump
pg_dump 백업
pg_dump -U username -Fp dbname > filename.txt
pg_dump 복구
psql -U username -f filename db_name
--For restoring custom format
$ pg_restore -Fc -U username -d dbname filename.bak
$ pg_restore -Fc -C -U username -d dbname filename.bak
--For restoring tar format
$ pg_restore -U username -d dbname filename.tar
$ pg_restore -C -U username -d dbname filename.tar
pg_dump Cluster level 백업
pg_dumpall -p portnumber > filename.txt
pg_dump Cluster level 복구
psql -f filename.txt
pg_dump 테이블 백업
pg_dump -U [user_name][db_name] -t [table_name] > [new_file]
pg_dump 테이블 복구
psql -U [user_name] -f [dump_file_location][db_name]
e.g.
pg_dump -U agens postgres -t oscar > ./oscar.sql
psql -U agens -f ./oscar.sql postgres
연습 테이블 생성
CREATE TABLE oscar (
index char(10),
year char(10),
age char(10),
name char(10),
movie char(10)
) ; DATA INSERT
INSERT INTO oscar VALUES
('1', '1920', '20', 'lee', 'dawn'),
('2', '1921 '24, 'kim', 'doom'),
('3', '1922, '25, 'park', 'down'),
('4', '1923, '27, 'son' 'deal')
;pg_dump 를 이용한 table 백업
# in terminal
pg_dump -U agens postgres -t oscar > ./oscar.sql
# in asql
drop table oscar;pg_dump 를 이용한 table 복구
# in termial
psql -U agens -f ./socar.sql postgres# psql 접속
psql -U agens -d postgres
# oscar 테이블 복구 확인
select * from oscar ;
위 내용은 pg_dump 기능을 알아보기 위한 내용으로 같은 DB를 사용하여 진행했다
같은 DB 내에서 테이블을 복사하고 싶다면 아래와 같은 기능을 사용하여 보다 편리하게 진행할 수 있다
CREATE TABLE [new_table_name] AS SELECT * FROM [table_name] ;
e.g. CREATE TABLE oscar_copy AS SELECT * FROM oscar;
Filesystem backup (cold backup)
ag_ctl -D $PGDATA stop # 서버 정지
tar cf backup.tar $AGHOME # 백업 실행
mkdir -m 700 -p $AGHOME/backup/data
tar xf backup.tar -C $AGHOME/backup/data
ag_ctl start -D $AGHOME/backup/datacf ) $PGDATA 는 AG의 data file 경로
Archive Mode Backup(Hot Backup)
PostgreSQL 은 미리쓰기기로그 (WAL, Write ahead log) 을 pg_xlog 디렉터리에서 관리합니다.
이 로그는 데이터베이스에 대한 모든 조작 기록을 보관하고 있어,
서버가 갑자기 비정상적으로 종료된경우 데이터파일에 적용하지 못한 작업은 이 로그파일을 읽어서 복구할 수 있습니다.
이 로그를 다른 서버로 보내, 이 로그의 내용을 그대로 실행하여 원본 서버와 똑같이 만들수 있습니다.
또한 특정시점 까지만 실행하도록 하여, 특정시점으로 복구할 수도 있습니다.
https://mozi.tistory.com/560
postgresql.conf 설정
# archive 설정
wal_level = replica
archive_mode = on
archive_command = 'cp %p $PGDATA/archive/%f.arc'
archive_timeout = 1mincf) $PGDATA 경로 밑에 archive 폴더 생성
Archive Mode 백업
# asql -U agens -d postgres
# archive 설정
show archive_mode;
SELECT name, setting FROM pg_settings where name IN ('archive_command');
# 백업 시작 (Hot Backup)
SELECT pg_start_backup(now()::text);
# 백업 현황 (in terminal)
cat backup_label
cat tablespace_map
# 백업 종료
SELECT pg_stop_backup(); Archive Mode 복구
1. 서버가 실행중인 경우
# 백업 시작
pg_basebackup -D $AGDATA
-D : 복구를 수행할 디렉토리 지정
# recovery.conf
restore_command = 'cp /ORADATA/data/archive/%f.arc %p'
%f : 적용할 WAL 파일 이름
%p : 서버에서 사용할 WAL 파일 경로
# 복구 및 DB 시작
ag_ctl -D $AGDATA start
# 복구 완료
recovery.conf → recovery.done
* pg_basebackup 명령어를 통해 새로운 data 폴더 생성 pg_basebackup은 database cluster의 backup을 쉽게 도와주는 유틸리티로 pg_basebackup 명령어를 수행하여 start/stop backup 및 db_cluster 복사본을 생성
* recovery.conf 파일을 data 폴더 내에 생성하고, restore_command 옵션을 사용해 WAL 파일을 복구
- 서버가 중지된 경우
# tar 명령어로 압축한 data 폴더를 압축해제
cd $AGDATA/data
tar cf backup.tar *
tar xf backup.tar $AGDATA/new/data
# 전체 증분 데이터 복구
restore_command = 'cp /ORADATA/data/archive/%f.arc %p'
# 특정 시점까지 증분 데이터 복구
restore_command = 'cp /ORADATA/data/archive/%f.arc %p'
recovery_target_time = '2019-02-13 13:50:00'
# 복구 및 DB 시작
ag_ctl -D $AGDATA start
# 복구 완료
recovery.conf → recovery.done
