백업(backup) 및 복구(restore)

 

본 문서에서는 PostgreSQL Database에 대하여 백업(Backup) 및 복구 (Restore) 방법에 대하여 설명합니다.

데이터베이스 백업 : Bakcup

pg_dumpall : 전체백업

PostgreSQL Database 전체 백업을 수행하려면, 간단하게 "pg_dumpall" 명령을 이용하면 됩니다.

일반적으로 다음과 같은 형태로 데이터베이스에 대한 전체 백업을 수행합니다.

# pg_dumpall -f all-backup.sql -U postgres -W
Password:

"-f" 또는 "--file" 옵션을 주지 않으면, 백업 스크립트 내용이 표준 출력(standard output)으로 나오게 됩니다.

"-U" 옵션은 전체 데이터베이스에 대한 접근 권한을 갖고 있는 "postgres" 계정을 입력하고, 환경변수에 계정 비밀번호를 입력해 두지 않았다면, "-W" 또는 "--password" 옵션으로 계정에 대한 비밀번호를 입력받을 수 있도록 합니다.

다음은 "pg_dumpall" 명령에 줄 수 있는 옵션들입니다.

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -c, --clean                  clean (drop) databases before recreating
  -g, --globals-only           dump only global objects, no databases
  -o, --oids                   include OIDs in dump
  -O, --no-owner               skip restoration of object ownership
  -r, --roles-only             dump only roles, no databases or tablespaces
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in the dump
  -t, --tablespaces-only       dump only tablespaces, no databases or roles
  -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
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --no-publications            do not dump publications
  --no-role-passwords          do not dump passwords for roles
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-sync                    do not wait for changes to be written safely to disk
  --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
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=CONNSTR     connect using connection string
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -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

"-s" 옵션은 데이터를 제외한 테이블 스키마만을 백업 받을 경우, 유용합니다.

---

pg_dump : 데이터베이스 백업

"pg_dump"는 데이터베이스 서버의 모든 DB를 백업 받지 않고, 특정 데이터베이스나 특정 테이블만 선별적으로 백업할 경우 사용합니다.
다음은 "sample"이라는 DB만 백업받는 예제입니다.

# pg_dump -h 127.0.0.1 -p 5432 -U postgres -W --compress=5 -f sample_db-20201219.sql.gz sample
Password:

"--compress" 옵션은 백업시 생성되는 스크립트 파일을 바로 압축하여 줍니다. 압축률은 0 ~ 9로 지정할 수 있고, 높은 숫자일 수록 압축률이 높아 생성되는 파일의 크기는 작지만 그만큼 백업 속도는 느립니다.

다음은 DB내의 특정 테이블만 백업하는 경우입니다.

# pg_dump -h 127.0.0.1 -p 5432 -U postgres -W -d sample -t sample_table -f sample_table-20201219.sql
Password:

"-d" 옵션으로 테이블이 속한 데이터베이스를 지정하고, "-t" 옵션에 백업할 테이블을 지정합니다.
"-h" 옵션은 데이베이스 서버의 IP를 입력하고, -p는 데이터베이스 서버의 운영 포트를 지정합니다.

다음은 "pg_dump" 명령의 옵션입니다.

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
  --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

---

복구 : Restore

앞서 백업 받은 파일을 이용하여 데이터베이스에 장애 등이 발생하여 복구할 경우 기본적으로 다음과 같이 "psql" 명령을 이용하여 데이터베이스 혹은 테이블을 복구하게 됩니다.
"pg_dumpall" 또는 "pg_dump" 명령으로 백업 받은 백업 스크립트 파일을 "psql" 명령으로 실행해주면, 백업 스크립트의 내용대로 복구 작업이 이루어 집니다.

# psql -h 127.0.0.1 -p 5432 -U postgres -W -d sample -f sample_db-20201219.sql
Password for user postgres:

다음은 "pgsql" 명령의 도움말입니다.

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "root")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit
      --help=variables     list special variables, then exit

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING
                           field separator for unaligned output (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           record separator for unaligned output (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator for unaligned output to zero byte
  -0, --record-separator-zero
                           set record separator for unaligned output to zero byte

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "root")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)