[PostgreSQL] dump & restore

식빵·2025년 1월 15일
PostgreSQLbackupdumppg_dumppg_restorepostgrespsqlrestore

postgresql-memo

목록 보기
36/39

Postgresql 16 버전에서 테스트해보고 작성한 글입니다.

🍞 pg_dump 로 덤프내기

옵션 파라미터

  • -U : username
  • -h : host (생략 시 => localhost)
  • -d : database name
  • -n : 덤프 받을 테이블이 포함된 스키마
  • -p : 포트번호 (생략 시 => 5432)
  • -t : 덤프 받을 테이블 이름 (wildcard 사용가능)
  • -T : 제외할 테이블 명 (wildcard 사용가능)
  • -F : 덤프를 내는 방식 지정
    • -F p : sql 파일로 덤프 (default)
    • -F c : 압축한 아카이브 파일로 덤프
    • -F d : 디렉토리에 덤프 파일 생성
  • -f : 덤프 결과 파일명
  • -v : 덤프 작업 내역 출력하기
  • --no-tablespaces : 테이블스페이스 할당을 덤프하지 않음
  • --no-privileges : 접근 권한 (grant/revoke) 정보는 덤프 안 함
    -- --no-owner : 일반 텍스트 형식(ex: *.sql)에서 개체 소유권 복원 건너뛰기

옵션 파라미터 중에서 -F 에 따라 다른 방식의 덤프를 지원합니다.
이 중에서 SQL 파일로 덤프, 압축한 아카이브 파일로 덤프 를 만드는 방법만 보도록 하겠습니다.



방법 1. SQL 덤프

### 형식:
# pg_dump -U [사용자 명] -h [호스트] -d [데이터베이스] -n [스키마] -p [포트번호] \
#	-t [(스키마).테이블 명]
#   -f [덤프 결과 파일명].sql;

### 예시:
pg_dump -U postgres -h localhost -d postgres -n public -p 5432 \
	-t public.tl_scco_ctprvn \
    -f tl_scco_ctprvn.sql;



방법 2. 압축 아카이브 덤프

방법 1. SQL 덤프 와 거의 유사하지만 다른 점이 몇가지 있습니다.

  1. -F c 옵션 사용
  2. -f 에 *.sql 이 아닌 다른 확장자 사용 (헷갈림 방지용)

### 형식:
# pg_dump -U [사용자 명] -h [호스트] -d [데이터베이스] -n [스키마] -p [포트번호] -F c \
#	-t [(스키마).테이블 명]
#   -f [덤프 결과 파일명].dump;


### 예시:
pg_dump -U postgres -h localhost -d postgres -n public -p 5432 \
	-F c \
	-t public.tl_scco_ctprvn_202412 \
	-f tl_scco_202412_bck.dump;

개인적으로는 압축 아카이브 방식이 더 좋은 거 같습니다.
SQL 로 덤프 낼 때보다 파일 크기가 확연히 작아집니다.



보충:

여러 테이블을 하나의 덤프 파일로!

-t 옵션을 연달아서 작성하면 됩니다.

## SQL 덤프
pg_dump -U postgres -h localhost -d postgres -n public -p 5432 \
	-t public.tl_scco_ctprvn \
	-t public.tl_scco_sig \
    -t public.tl_scco_emd \
    -t public.tl_scco_li \
	-f total_bck.sql;

## 압축 아카이브 덤프 방식도 똑같이 동작합니다!



특정 스키마 전체를 덤프 내기

-t 옵션을 전부 빼고, -n 옵션만 정확히 명시하면 됩니다. 

pg_dump -U postgres -h localhost -d dump -p 5432 -n public -F c -f dump_public.dump



wildcard 로 테이블 지정

pg_dump -U postgres -h localhost -d dump -p 5432 \
-t 'public.TABLE_*' \
-F c -f dump_public.dump



특정 스키마에서 특정 테이블만 제외하기

-t 옵션을 전부 빼고, -n 옵션만 정확히 명시하면서
-T 로 제외할 테이블들을 지정합니다. wildcard 사용 가능

pg_dump -U postgres -h localhost -d dump -p 5432 -n public \
-T 'exclude_table_nm' -T 'exclude_tb_*' \
-F c -f dump_public.dump



grant, tablespace 정보 제외하기

dump 받고 추후 restore 할 때 tablespace 가 없어서 에러가 나는 경우가
빈번하고, 재수없으면 grant 관련해서도 에러를 일으킵니다.
그러니 웬만하면 빼는 게 좋습니다.

pg_dump -U postgres -h localhost -d dump -p 5432 -n public \
--no-tablespaces --no-privileges --no-owner \
-F c -f dump_public.dump

참고로 --no-owner 는 sql 파일로 덤프낼 때만 효과가 있지만,
전 그냥 헷갈려서 다 집어 넣습니다.

dump 파일로 만들 경우에는 추후 pg_restore 를 할 때 --no-owner 라는 옵션을 주면 됩니다.




🍞 덤프 복원하기

덤프 결과물이 SQL 인지, 압축 아카이브 인지에 따라
복원하는 명령어를 달리 해야합니다.

  • sql 덤프 : psql -f
  • 아카이브 덤프 : pg_restore

가볍게 아래 예시를 보면 대충 어떻게 하는지 감이 오실 겁니다.

SQL 덤프 복원

# 옵션 파라미터는 pg_dump 와 유사합니다. -v 옵션으로 진행상황 파악
psql -U postgres -d postgres -h localhost -p 5432 -v -f total_dump.sql

아카이브 덤프 복원

# 옵션 파라미터는 pg_dump 와 유사합니다. 마차간지로 -v 옵션으로 진행상황 파악
pg_restore -U postgres -d postgres -h localhost -p 5432 -v \
test_tl_scco_202412.dump

주의사항: 이상하게 느껴지겠지만 pg_restore --no-tablespaces 옵션이 먹히게 하려면
pg_dump 를 할 때도 --no-tablespaces 옵션을 지정해줘야 합니다! (참고 링크)




주의사항!

pg_dump -F c 사용 시 버전 호환성 조심!

예를 들어서 postgresql 16 환경에서 pg_dump 를 사용하여 생성한 dump 파일을
더 낮은 버전의 postgresql 환경에서 pg_restore 로 복원하려고 하면
다음과 같은 에러가 날 수도 있습니다.

pg_restore: error: unsupported version (1.15) in file header

이런 에러가 나질 않길 바라신다면 pg_restore 명령어를 사용하는 postgresql 데이터베이스의
버전과 똑같은 버전의 postgresql 이미지를 docker 로 pull 받고,

pull 받은 이미지를 실행시켜서 docker exec 로 컨테이너 내부에 들어가서
pg_dump 명령어를 사용하시면 됩니다.



버전에 따라 다른 pg_restore 동작방식

제 경험상 pg_dump -F c 로 만든 *.dump 파일을
서로 다른 postgresql 버전 대의 환경에서 pg_restore 하면 약간 씩 다르게 동작합니다.

이러한 경험을 하고 싶지 않다면 그냥 *.dump 파일로 덤프내기 보다는,
*.sql 로 덤프를 내는 것도 좋은 방법 같습니다.



보충: pg_dump 옵션들

pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -e, --extension=PATTERN      dump the specified extension(s) only
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -n, --schema=PATTERN         dump the specified schema(s) only
  -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=PATTERN          dump the specified table(s) only
  -T, --exclude-table=PATTERN  do NOT dump the specified table(s)
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects
  --include-foreign-data=PATTERN
                               include data of foreign tables on foreign
                               servers matching PATTERN
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  --no-tablespaces             do not dump tablespace assignments
  --no-toast-compression       do not dump TOAST compression methods
  --no-unlogged-table-data     do not dump unlogged table data
  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
  --quote-all-identifiers      quote all identifiers, even if not key words
  --restrict-key=RESTRICT_KEY  use provided string as psql \restrict key
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

이중에서 --data-only, --no-owner, --no-privileges, --no-tablespaces 는 쓸만하니 잘 봐두길 바랍니다!



참고 링크

profile
식빵
백엔드 개발자로 일하고 있는 식빵(🍞)입니다.
이전 포스트

[Postgresql] default tablespace 를 지정해서 table 생성 시 자동으로 tablespace 적용하는 법

다음 포스트

[PostgreSQL] Nested Partition , 파티션 속에 파티션 넣기

0개의 댓글