PostgreSQL DB BACKUP RESTORE
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 PostgreSQL DB백업 과 복구[편집]
- PGAdmin과 같은 GUI 기반의 Client Tool을 사용해서 손쉽게 Backup/Restore 가능
- Linux shell 기반 Backup/Restore 방법
1.1 Backup[편집]
- 명령어 pg_dump 와 pg_dumpall 사용
- dg_dump은 단일 Database를 요청한 Format으로 Bakup
- pg_dumpall은 전체 Database Cluster를 SQL script로 Backup
1.1.1 pg_dump 사용법[편집]
pg_dump --help
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, --encoding=ENCODING dump the data in encoding ENCODING
-n, --schema=SCHEMA dump the named schema(s) only
-N, --exclude-schema=SCHEMA do NOT dump the named schema(s)
-o, --oids include OIDs in dump
-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=TABLE dump the named table(s) only
-T, --exclude-table=TABLE do NOT dump the named 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=TABLE do NOT dump data for the named table(s)
--if-exists use IF EXISTS when dropping objects
--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-unlogged-table-data do not dump unlogged table data
--quote-all-identifiers quote all identifiers, even if not key words
--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.-d, --dbname : Backup할 Database 명. -h, --host : Database 주소. -U, --username : Database 접속 시 User ID -F, --format : Backup Format. 필자는 주로 tar 파일로 backup하기 때문에 't'를 사용한다. -f, --file : Backup File Name -t, --table : 특정 Table만 Backup하려할 때 대상이 되는 Table 명 -j, --jobs : Backup 시 병렬 처리 여부와 그 정도. -v, --verbose : 진행 과정 표시.
- 사용 예)
- 'dbcafe' Database를 tar 형식으로 Backup
- Public 폴더 아래 dbcafe_1M.tar라는 tar 형식으로 Backup 하는 예
- (-f Option 대신 output file을 직접 명시)
- -v 옵션을 사용하지 않으면 진행 상황에 대한 Display없이 실행
# pg_dump -d dbcafe -h localhost -U dbcafe -F t > ~/Public/dbcafe_1M.tar
# ls -la ~/Public/
total 1111456
drwxr-xr-x. 2 browndwarf browndwarf 4096 Dec 1 10:56 .
dr-xr-x---. 22 browndwarf browndwarf 4096 Dec 25 10:53 ..
-rw-r--r--. 1 browndwarf browndwarf 93506048 Dec 14 13:22 galaxy_1M.tar
...1.2 Restore[편집]
- pg_dump를 이용해서 만들어진 Database Backup File을 Restore할 때 사용
- (pg_dumpall을 통해 만들어진 Backup File은 SQL Script file이기 때문에 pg_restore를 사용하지 않는다.)
1.2.1 pg_restore 사용법[편집]
pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.
Usage:
pg_restore [OPTION]... [FILE]
General options:
-d, --dbname=NAME connect to database name
-f, --file=FILENAME output file name
-F, --format=c|d|t backup file format (should be automatic)
-l, --list print summarized TOC of the archive
-v, --verbose verbose mode
-V, --version output version information, then exit
-?, --help show this help, then exit
Options controlling the restore:
-a, --data-only restore only the data, no schema
-c, --clean clean (drop) database objects before recreating
-C, --create create the target database
-e, --exit-on-error exit on error, default is to continue
-I, --index=NAME restore named index
-j, --jobs=NUM use this many parallel jobs to restore
-L, --use-list=FILENAME use table of contents from this file for
selecting/ordering output
-n, --schema=NAME restore only objects in this schema
-N, --exclude-schema=NAME do not restore objects in this schema
-O, --no-owner skip restoration of object ownership
-P, --function=NAME(args) restore named function
-s, --schema-only restore only the schema, no data
-S, --superuser=NAME superuser user name to use for disabling triggers
-t, --table=NAME restore named relation (table, view, etc.)
-T, --trigger=NAME restore named trigger
-x, --no-privileges skip restoration of access privileges (grant/revoke)
-1, --single-transaction restore as a single transaction
--disable-triggers disable triggers during data-only restore
--enable-row-security enable row security
--if-exists use IF EXISTS when dropping objects
--no-comments do not restore comments
--no-data-for-failed-tables do not restore data of tables that could not be
created
--no-publications do not restore publications
--no-security-labels do not restore security labels
--no-subscriptions do not restore subscriptions
--no-tablespaces do not restore tablespace assignments
--section=SECTION restore named section (pre-data, data, or post-data)
--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:
-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 restore
The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified
multiple times to select multiple objects.
If no input file name is supplied, then standard input is used.-d, --dbname : Restore하는 Database 명. -h, --host : Database 주소. -U, --username : Database 접속 시 User ID -F, --format : Restore File의 Format. -t, --table : 특정 Table만 Restore하려할 때 대상이 되는 Table 명 -j, --jobs : Restore 시 병렬 처리 여부와 그 정도. -v, --verbose : 진행 과정 표시 -C, --create : Target DB를 새로 만들면서 Restoration 진행. -c, --clean : Restoration 시에 같은 이름의 Database Object가 발견되면 Drop 후에 Create하게 함. -O, --no-owner : 원본 DB의 Owner가 복구할 위치에 존재하지 않을 경우 복구 시 다량의 에러가 발생한다. 이를 막기 위해 DB 복구시 OWNER를 명시하지 않고 진행하게 함.
- restore 사용 예시)
-- Backup한 DB를 Restore하는 위치에 새로 생성하면서 복구. (-C option 사용, root 계정으로 restore. Target DB 명 없이 Backup한 Database 명을 그대로 사용)
pg_restore -h localhost -U postgres -C -d postgres -F t ~/Public/DBCAFE_db2_back.tar
-- Restore할 Database Instance를 이미 존재하는 상태에서 Schema,Object 복구.
pg_restore -h localhost -U dbcafe -d "DBCAFE" -F t ~/Public/DBCAFE_db2_back.tar
-- 이미 Database 내에 Object가 존재하는 경우, Table 내에 Data만 복구
pg_restore -h localhost -U dbcafe -a -d "DBCAFE" -F t ~/Public/DBCAFE_db2_back.tar- -v Option은 Verbose 모드로 진행 상황을 확인(권장) 한다.
# pg_restore -v -h localhost -U aims -d "DBCAFE" -F t ~/Public/core_db_back.tar
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION "public.star_register_transmission_sequence(bigint)"
pg_restore: creating FUNCTION "public.star_register_jobnumber(bigint)"
pg_restore: creating TABLE "public.star"
pg_restore: creating SEQUENCE "public.star_id_sequence"
pg_restore: creating TABLE "public.databasechangelog"
pg_restore: creating TABLE "public.databasechangeloglock"
...1.3 복구 중 오류 발생 관련 정보[편집]
1.3.1 Restore시에는 다양하게 오류 발생.[편집]
- -C 옵션을 사용해서 Database를 생성하면서 Restore할 때 root 권한을 가진 사용자(Default로는 'postgres')로 실행해야 된다. 그렇지 않을 경우 아래와 같은 오류가 발생한다.
- root권한을 가진 사용자가 -C 옵션을 사용해서 Restore할 때에는 Target Database를 'postgres'로 설정해야 한다.
- 그렇지 않으면 오류가 발생하거나, 진행이 되는 것 같지만 완료 후에 확인해보면 restore되어 있지 않은 것을 확인할 수 있다.
- 원본 DB의 Owner와 복원하려는 User가 상이할 때 DB Object를 새로 생성하는 단계에서 기존 Owner가 없다고 오류를 발생한다.
- 이럴 경우 복원하려는 위치에 Database를 먼저 생성한(생성할 때에 Owner를 복원하려는 곳에 있는 User로 명기) 후에 복원하거나, -O 혹은 --no-owner 옵션을 사용해야 한다.
- Window에서 실행하던 PostgreSQL을 Linux 환경에서 복원시킬 때 Encoding 명이 맞지 않아 오류 발생
- Linux에서 지원하는 Encoding 방식을 명시적으로 포함시켜서(LC_COLLATE와 LC_CTYPE option 추가. 참조) 동일한 이름을 가지는 Database를생성한 후에 그 DB를 target으로 해서 복원해야 한다. (-C 옵션 제거)
1.4 postgresql db 일일 백업 스크립트[편집]
#!/bin/bash
#
# Backup a Postgresql database into a daily file.
#
BACKUP_DIR=/pg_backup
DAYS_TO_KEEP=14
FILE_SUFFIX=_pg_backup.sql
DATABASE=
USER=postgres
export PGPASSWORD = input_your_database_password.here
FILE=`date +"%Y%m%d%H%M"`${FILE_SUFFIX}
OUTPUT_FILE=${BACKUP_DIR}/${FILE}
# do the database backup (dump)
# use this command for a database server on localhost. add other options if need be.
pg_dump -U ${USER} ${DATABASE} -F p -f ${OUTPUT_FILE}
# gzip the mysql database dump file
gzip $OUTPUT_FILE
# show the user the result
echo "${OUTPUT_FILE}.gz was created:"
ls -l ${OUTPUT_FILE}.gz
# prune old backups
find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*${FILE_SUFFIX}.gz" -exec rm -rf '{}' ';'1.5 Crontab 이용 Auto Daily Backup 설정[편집]
crontab -e
05 01 * * * pg_dump -d DBCAFE -h localhost -U dbcafe -F t > ~/Public/database_name_$(date +\%Y\%m\%d).tar
...