[PostgresSQL] postgresql 백업 및 복원

/* 패스워드를 파일로 등록해 놔야 한다 */

C:\Users\cbw\AppData\Roaming\postgresql\pgpass.conf

파일 내용

localhost:5432:*:postgres:postgres

 

 

 

/* db전체백업 */

pg_dump.exe --file "D:\\temp\\invako.backup" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --section=pre-data --section=data --section=post-data --column-inserts --encoding "UTF8" "invakodb"

 

 

/* db전체복원 */

pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "invakodb" --section=pre-data --section=data --section=post-data --verbose "D:\\temp\\invako.backup"

 

/* db해당 스키마 백업 */

pg_dump.exe --file "D:\\temp\\invakoSchema.backup" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --section=pre-data --section=data --section=post-data --column-inserts --encoding "UTF8" --schema "invako" "invakodb"

 

/* db해당 스키마 복원 */

pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "invakodb" --section=pre-data --section=data --section=post-data --verbose --schema "invako" "D:\\temp\\invako.backup"

 

 

/* 테이블 백업 */

pg_dump.exe --file "D:\\temp\\test_member.backup" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs --section=pre-data --section=data --section=post-data --column-inserts --encoding "UTF8" --table "invako.test_member" "invakodb"

 

/* 테이블 복원 */

pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "invakodb" --section=pre-data --section=data --section=post-data --data-only --verbose --schema "invako" --table "test_member" "D:\\temp\\test_member.backup"

 

/* 여러 개 테이블 백업 */

pg_dump.exe --file "D:\\temp\\test_member.backup" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs --section=pre-data --section=data --section=post-data --column-inserts --encoding "UTF8" --table "invako.test_member" --table "invako.test_team" "invakodb"

 

/* 여러 개 테이블 복원 forgin키 관계시 순서되로 처리가 안되어서 에러가 발생함 부모가 먼저 처리 되고 자식이 처리되어야 하는데 순서가 보장이 안됨 */

pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "invakodb" --section=pre-data --section=data --section=post-data --data-only --verbose --schema "invako" --table "test_team" --table "test_member" "D:\\temp\\test_member.backup"

 

 

※ 해당 내용은 PostgreSQL 9.6의 내용을 기반으로 작성하였습니다.

명령어 구성

# pg_dump [옵션] [DB명]

옵션

-a

--data-only

테이블데이타, large objects(?), 시퀀스 만 백업

-E [encoding]

--encoding=[encoding]

백업 인코딩 설정(주로 UTF-8 사용)

-f [file]

--file=[file]

백업 파일 지정

-F [format]

--format=[format]

백업 포멧 선택

p

plain

SQL 평문으로 백업. 기본설정 옵션.

 

c

custom

custom-format 으로 백업. pg_restore를 통해 복원 가능. 기본적으로 압축되어 있음. 가장많이 사용.

 

d

directory

directory-format 으로 백업. pg_restore를 통해 복원 가능.기본적으로 압축되어 있음.

 

t

tar

tar-format 으로 백업. pg_restore를 통해 복원 가능. 압축되어 있지 않음.

-n [schema]

--schema=[schema]

입력한 스키마만 백업.

다중 스키마 입력 방법

# pg_dump -n schema1 -n schema2 -n schema3 # pg_dump -n 'schema*'

 

-N [schema]

--exclude-schema=[schema]

입력한 스키마를 제외하고 백업.

다중 스키마 입력 방법

# pg_dump -N schema1 -N schema2 -N schema3 # pg_dump -N 'schema*'

 

-t [table]

--table=[table]

입력한 테이블만 백업. 뷰도 포함. -t 옵션 사용시 -n 옵션은 적용되지 않음.

다중 테이블 입력 방법

# pg_dump -t table1 -t table2 -t table3 # pg_dump -t 'table*'

 

-T [table]

--exclude-table=[table]

입력한 테이블을 제외하고 백업. -t 옵션과 함께 사용 시 해당 테이블은 제외되지 않음.

다중 테이블 입력 방법

# pg_dump -T table1 -T table2 -T table3 # pg_dump -T 'table*'

 

-d [dbname]

--dbname=[dbname]

입력한 데이터베이스 백업.

-U [username]

--username=[username]

백업시 접속할 사용자명.

-w

--no-password

백업 시 비밀번호를 묻지않음. 서버가 비밀번호 인증이 되어있지 않다면 접속 실패. 해당 명령어는 스크립트로 백업 실행시 유용.

비밀번호 인증 방법

백업시 접속 계정의 홈디렉토리에 '.pgpass' 파일을 생성

ex) linux

# su - postgres # cd ~ # vi .pgpass [hostname]:[port]:[database]:[username]:[password] 입력 (* 입력 시 전체) # chmod 0600 ~/.pgpass

 

ex) window

%appdata%디렉토리에 postgresql 디렉토리 생성 (#cd %appdata% 로 디렉토리 확인)

pgpass.conf 파일 생성

[hostname]:[port]:[database]:[username]:[password] 입력 (* 입력 시 전체)

 

-W

--password

백업 시 비밀번호를 묻도록 강제.

예시

기본적인 백업 명령어

# pg_dump -Fc -d mydb -U myuser -f /tmp/db.dump

 

파일 포멧에 따라 확장자는 다르게 주는것을 권장

# pg_dump -Fp -d mydb -f /tmp/db.sql # pg_dump -Fc -d mydb -f /tmp/db.dump # pg_dump -Ft -d mydb -f /tmp/db.tar # pg_dump -Fd -d mydb -f /tmp/dumpdir

 

스크립트로 백업 시 명령어

# pg_dump -Fc -d mydb -U myuser -w -f /tmp/db.dump