[유지보수] 백업과 복원 : CLI와 GUI를 사용하여

백업과 복원

유지보수에 중요한 부분인 백업과 복원에 대해서

터미널을 사용한 방법과 DBeaver를 사용한 방법을 각각 톺아본다

---

pg_dump | pg_restore

데이터베이스 유틸리티를 사용한 백업 및 복원을 알아본다.

---

 

데이터베이스 유틸리티

• 데이터베이스 관리 및 유지보수를 위해 사용되는 도구나 프로그램
• 종류
  - psql : 쿼리 수행 및 데이터베이스 관리를 위한 대화형 셸
  - pg_dump : 백업에 사용되는 명령어
  - pg_restore : pg_dump으로 백업한 파일로 복원하는 데 사용되는 명령어
  - pg_ctl : PostgreSQL 서버의 시작, 정지, 재시작 등을 관리하는 명령어
  - pg_stat : 통계 정보를 확인하는데 사용되는 다양한 명령어
              (pg_stat_database, pg_stat_bgwriter 등)
  - pg_config : PostgreSQL 컴파일 및 설치 정보를 확인하는 데 사용되는 명령어입니다.
  - vacuumdb :불필요한 공간을 정리하는데 사용되는 명령어
  - pgbench :성능을 테스트하는데 사용되는 벤치마킹 도구

 

 

백업 : pg_dump / pg_dumpall

• PostgreSQL 데이터베이스에서 백업을 생성하는 데 사용되는 명령어
• 기본구조
  pg_dump -h 호스트 -U 사용자 -d 데이터베이스 > 백업파일명
  -h : 호스트 (ex: localhost)
  -U : PostgreSQL 사용자 
  -d : 백업 대상 데이터베이스
  > : 리다이렉션을 사용하여 백업 내용을 파일로 저장
      * 리다이렉션 : 명령어의 결과나 출력을 파일로 저장하거나 다른 프로세스에 전달하는 기능
  백업파일명 : 백업 파일의 이름 확장자, 경로를 지정(경로지정관련 : 281번 포스팅 참조)
       * '> 백업파일명'을 생략하면 터미널에 결과가 출력된다.
• pg_dumpall
  - PostgreSQL 클러스터 전체의 정보를 하나의 파일로 백업
  - 명시적으로 데이터베이스 이름을 받지 않고 모든 DB와 관련 모든 객체 백업
  pg_dumpall -h 호스트 -U 사용자 > 백업파일명
  
• 출력형식지정
  -F 형식 백업파일명 : 출력 형식 지정하는 옵션
     >> 형식에는 p, t, c가 올 수 있다.
     -F p 파일명.sql : SQL 스크립트로 생성, -F p는 디폴트값으로 생략할 수 있다.
     -F c 파일명.dump : PostgreSQL전용 바이너리 파일로 생성한다. 이때 확장자는 임의로 지정할 수 있다
• 추가옵션
  -p : 포트번호 지정
  -s : 데이터 없이 스키마 만을 백업
  -a : 스키마 없이 데이터만을 백업
  -c : 드롭구문을 추가해서 테이블 충돌 방지
  -t 테이블명 : 특정 테이블만 백업
  | gzip > 백업파일명.gz : gzip으로 파이프 되어 압축저장 
            * xz, lz4 등 다른 압축프로그램 사용 가능
  -O : no-owner, 개체 소유권 백업 건너뛰기
• 주의사항
  - 백업 파일은 텍스트 형식으로 저장
    >> 보안에 취약
    >> 암호화된 파일이나 접근 권한이 엄격히 제한된 파일 시스템에 저장
  - 로컬호스트가 아닌경우 유저에 대한 비밀번호를 입력해야함

##### 도커 컨테이너 DB서버 백업과정
# 1. postgreSQL 서버 터미널 실행
docker exec -it 컨테이너명 /bin/bash # 디버그 : 경로를 못찾는 오류가 발생한다면 경로를 bin/bash로 입력한다 (Docker Desktop 사용시 확인된 버그)
# 2. 스키마만 백업하기
pg_dump -s -c -h localhost -U postgres -d db명 > 스키마백업.sql
# 3. 데이터만 백업하기
pg_dump -Fc -a -h localhost -U postgres -d db명 > 데이터백업.dump
# 4. 컨테이너에서 원격로컬로 파일 복사
docker cp 컨테이너명:파일경로 로컬경로
# 5. 원격로컬에서 로컬로 복사 * 로컬PC에서 실행
ssh 계정@원격IP "cat 원격파일명" > 로컬파일명
# 6. 로컬에서 원격로컬로 복사 * 로컬PC에서 실행
scp 로컬파일명 계정@원격IP:원격파일명

 

 

 

복원 : pg_restore / psql

• PostgreSQL 데이터베이스를 백업 파일로부터 복원하는 데 사용되는 명령어
• 기본구조
  pg_restore -h 호스트 -U 사용자 -d 데이터베이스 < 백업파일.sql
  -h : 호스트 (ex: localhost)
  -U : PostgreSQL 사용자 
  -d : 백업 대상 데이터베이스
  < : 리다이렉션을 사용하여 파일에서 읽어들인 내용을 복원
     ** 파일을 못찾을 경우 <를 -f로 바꾼다
  백업파일.sql : 복원할 백업 파일의 이름 및 경로를 지정
• psql
  - 백업파일이 sql문으로 구성된 경우 pg_restore를 psql로 바꾸어 명령한다.
    * 일반 터미널에서 진행
• 추가옵션
  -t 테이블명 : 특정 테이블만 복원
  -j : 병렬 작업 수 지정(복원속도 높임)
  -v : 자세한 출력 활성화
  -O : no-owner, 개체 소유권 복원 건너뛰기
  
• 주의사항
  - 데이터베이스를 복원하기 전에는 복원 대상 데이터베이스가 존재하지 않아야 한다
  - 압축된 파일은 먼저 풀어준다

 

 

---

DBeaver

GUI를 사용한 백업 및 복원을 알아본다

---

 

저장하기 : 백업 및 덤프

* 예비 목적의 전체 복사본을 만들면 백업, 이관 목적의 특정 객체 혹은 데이터를 추출하는 경우는 덤프라고 표현한다.

1. 스키마 오브젝트(기본 생성시 public) 오른클릭 > 도구 > 백업
2. 복사뜨고자하는 테이블들을 선택한다
3. Setting 값을 설정한다.(다음단락 참고)
4. Start로 복사 시작

 

 

저장하기 : Setting 값 설정

• Format
  - 백업 파일의 형식 선택
  1. Plain
    - 텍스트 형태로 SQL 쿼리를 담고 있으며, 텍스트 에디터로 열어 볼 수 있다. 
    - 대량의 데이터의 경우 파일 크기가 커질 수 있음
    >> 가장 보편적으로 쓰인다
  2. Custom
    -  데이터베이스 종류 또는 특정 백업 도구에 의존적인 형식으로 
    - 장점: 데이터베이스 제품에 특화된 백업 형식을 사용할 수 있습니다.
  단점: 다른 데이터베이스 시스템으로 이동할 때 호환성이 제한될 수 있습니다.
  3. Tar
    - Tar는 여러 파일을 하나의 아카이브 파일로 묶는 포맷
    - 여러 파일을 하나의 압축 파일로 만들어 관리
    - 복원이 까다로울 수 있다.
  4. Directory
    - 데이터베이스 객체를 디렉토리 구조로 저장
      (각 테이블이나 객체에 대한 파일이 별도의 디렉토리에 저장)
    - 장점 : 객체 단위로 파일이 구분되어 있어 필요한 특정 객체만을 복원할 수 있다.
    - 단점 : 파일 수가 많아지고, 파일 시스템의 특정 기능에 의존적
• 체크박스
  1. Use SQL INSERT instead of COPY :
    - 데이터 복원 방식에서 INSERT를 사용할지 COPY를 사용할지 결정
    - INSERT 문 사용시 가독성이 생기나 시간이 오래 걸리며, COPY 문 사용시 이진 데이터를 사용하여 백업 및 로드 속도가 빨라진다.
  2. Do not backup privileges(GRANT/REVOKE) :
    - 사용자에게 부여된 권한과 권한취소를 생략한다. 보통 다른 환경으로 복원할 때 사용한다.
  3. Discard objects owner : 
    - 데이터베이스 객체(테이블, 뷰, 프로시저 등)의 소유자 정보가 생략되고 복원시 복원하는 사용자의 권한으로 객체가 생성된다.
  4. Add drop database statement : 
    - 복원 전에 테이블을 삭제한다.

 

 

불러오기 : 복원

1. 스키마 오브젝트(기본 생성시 public) 오른클릭 > 도구 > 복원
2. 백업파일 탐색버튼 클릭
3. 확장자 '*(*) '선택
4. Start로 복사 시작

 

 

불러오기: 오류

• 에러로그 확인하기
  - 불러오기시 오류가 나타난다면 로그를 확인한다
  - 윈도우 > Show View > Error Log