반응형
이번 Posting은 PostgreSQL에서 제공하는 DB Backup/Restore에 대한 내용이다. 물론 PGAdmin과 같은 GUI 기반의 Client Tool을 사용해서 손쉽게 Backup/Restore를 할 수 있지만, 필자가 업무상 PostgreSQL을 다루는 환경은 Linux Terminal인 경우가 많아 shell을 기반으로 할 때 Backup/Restore를 어떻게 하는 지에 대해 정리하였다.
Backup
PostgreSQL에서는 DB Backup을 위해 pg_dump와 pg_dumpall 프로그램을 제공한다. dg_dump는 단일 Database를 요청한 Format으로 Bakup을 만들고, pg_dumpall는 전체 Database Cluster를 SQL script로 Backup 한다. (여기서는 pg_dump에 관해서만 설명하려 한다.) pg_dump의 사용 방법은 아래와 같다.
[browndwarf@localhost temp]# 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.
Report bugs to .
그리고 개인적인 경험으로는 아래 Option 및 인자들이 주로 사용되었다.
- -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 : 진행 과정 표시.
사용 예) 'galaxy' Database를 tar 형식으로 Backup 해서 Public 폴더 아래 galaxy_1M.tar라는 tar 형식으로 Backup 하는 예(-f Option 대신 output file을 직접 명시했다.)이다. -v 옵션을 사용하지 않으면 진행 상황에 대한 Display없이 실행되며, 실행후에 Target Folder를 조회해보니 약 93M의 Backup 파일이 만들어진 것을 확인할 수 있다.
...
[browndwarf@localhost /]# pg_dump -d galaxy -h localhost -U browndwarf -F t > ~/Public/galaxy_1M.tar
[browndwarf@localhost /]# 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
...
Restore
pg_restore는 pg_dump를 이용해서 만들어진 Database Backup File을 Restore할 때 사용한다.(pg_dumpall을 통해 만들어진 Backup File은 SQL Script file이기 때문에 pg_restore를 사용하지 않는다.) pg_restore의 사용방법은 아래와 같다.
[browndwarf@localhost temp]# 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.
Report bugs to .
개인적으로 자주 사용했던 Option 및 인자들은 다음과 같다.
- -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를 명시하지 않고 진행하게 함.
사용 예)
...
[browndwarf@localhost temp]# pg_restore -h localhost -U postgres -C -d postgres -F t ~/Public/galaxy_db2_back.tar
[browndwarf@localhost temp]# pg_restore -h localhost -U browndwarf -d "GALAXY2" -F t ~/Public/galaxy_db2_back.tar
[browndwarf@localhost temp]# pg_restore -h localhost -U browndwarf -a -d "GALAXY2" -F t ~/Public/galaxy_db2_back.tar
...
- 첫번쨰 예 : Backup한 DB를 Restore하는 위치에 새로 생성하면서 복구. (이를 위해 -C option을 사용했으며, 이 경우에는 root 계정을 이용해서 restore해야한다. Target DB 명 없이 Backup한 Database 명을 그대로 사용한다)
- 두번째 예 : Restore할 Database Instance를 이미 존재하는 상태에서 Schema를 비롯한 Database Object들을 복구.
- 세번째 예 : 이미 Database내에 Object가 존재하는 경우, Table 내에 Data만을 복구.
그리고, Backup및 Restore시에 Machine이 마치 정지한 것처럼 보여질 수 있는데, 이를 막기 위해서는 -v Option을 활용해서 진행 상황을 확인할 수 있게 하는 것을 권장한다. 아래는 Restore시에 -v 옵션을 통해 중간 진행 상황을 확인하는 예이다.
...
[browndwarf@localhost temp]# pg_restore -v -h localhost -U aims -d "GALAXY2" -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"
...
Restore가 완료된 후 psql로 접속해서 DB List를 조회해보면 위의 예를 수행했을 때 약 1G정도의 Backup Data가 복원되었음을 확인할 수 있다.
...
[browndwarf@localhost temp]# psql -h localhost -U postgres
...
# Restore 전
postgres=# \list+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
---------------+------------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
galaxy | browndwarf | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | | 256 MB | pg_default |
postgres | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | | 7965 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +| 7825 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +| 7825 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
...
# Restore 후
postgres=# \list+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
---------------+------------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
GALAXY2 | browndwarf | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | | 1007 MB | pg_default |
galaxy | browndwarf | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | | 256 MB | pg_default |
postgres | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | | 7965 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +| 7825 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +| 7825 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)
...
Debugging 관련 정보 공유
백업 과정에서는 오류가 발생한 적이 거의 없는데 Restore시에는 다양하게 오류를 경험했었다. 이 때 경험한 오류들은 해결하는데 약간씩 시간이 걸렸었는데 다음과 같은 사례들이 있었다.
Case 1) -C 옵션을 사용해서 Database를 생성하면서 Restore할 때에는 root 권한을 가진 사용자(Default로는 'postgres')로 실행해야 된다. 그렇지 않을 경우 아래와 같은 오류가 발생한다.
...
[browndwarf@localhost temp]# pg_restore -h localhost -U browndwarf -C -d GALAXY2 -F t ~/Public/galaxy_db2_back.tar
pg_restore: [archiver (db)] connection to database "GALAXY2" failed: FATAL: database "GALAXY2" does not exist
...
Case 2) root권한을 가진 사용자가 -C 옵션을 사용해서 Restore할 때에는 Target Database를 'postgres'로 설정해야 한다. 그렇지 않으면 오류가 발생하거나, 진행이 되는 것 같지만 완료 후에 확인해보면 restore되어 있지 않은 것을 확인할 수 있다.
...
[browndwarf@localhost temp]# pg_restore -h localhost -U aims -C -v ~/Public/galaxy_db2_back.tar > out.txt
pg_restore: creating DATABASE "GALAXY2"
pg_restore: connecting to new database "GALAXY2"
pg_restore: creating FUNCTION "public.star_register_transmission_sequence(bigint)"
...
... 복원이 잘 되는 것 같이 보여진다.
...
pg_restore: creating FK CONSTRAINT "public.planet fk_us1qgsab9n6eodsok1db3j3ad"
# 완료 후 psql으로 접속해서 복원 결과를 확인해 보면 복원 시도 전과 상황이 똑같다.
[browndwarf@localhost ~]# psql -h localhost -U postgres
...
postgres=# \list+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
---------------+------------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
galaxy | browndwarf | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | | 256 MB | pg_default |
postgres | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | | 7965 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +| 7825 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +| 7825 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
Case 3) 원본 DB의 Owner와 복원하려는 User가 상이할 때 DB Object를 새로 생성하는 단계에서 기존 Owner가 없다고 오류를 발생한다. 이럴 경우 복원하려는 위치에 Database를 먼저 생성한(생성할 때에 Owner를 복원하려는 곳에 있는 User로 명기) 후에 복원하거나, -O 혹은 --no-owner 옵션을 사용해야 한다.
...
[browndwarf@localhost temp]# pg_restore -h localhost -U browndwarf -C -d GALAXY2 -F t ~/Public/galaxy_db2_back.tar
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 395; 1255 16396 FUNCTION star_register_transmission_sequence(bigint) browndwarf2
pg_restore: [archiver (db)] could not execute query: ERROR: role "browndwarf2" does not exist
Command was: ALTER FUNCTION public.star_register_transmission_sequence(star_id bigint, OUT next_transmission_sequence bigint) OWNER TO browndwarf2;
pg_restore: [archiver (db)] Error from TOC entry 396; 1255 16397 FUNCTION star_register_jobnumber(bigint) browndwarf2
pg_restore: [archiver (db)] could not execute query: ERROR: role "browndwarf2" does not exist
Command was: ALTER FUNCTION public.star_register_jobnumber(station_id bigint, OUT next_jobnumber bigint) OWNER TO aims;
...
Case 4) Window에서 실행하던 PostgreSQL을 Linux 환경에서 복원시킬 때 Encoding 명이 맞지 않아 오류를 발생하는 예이다. Window에서 한글 encoding 명이 Korean_Korea.949였는데, Linux에서는 이런 Encoding 방식이 없어서 발생하는 예이다. Linux에서 지원하는 Encoding 방식을 명시적으로 포함시켜서(LC_COLLATE와 LC_CTYPE option 추가. 참조) 동일한 이름을 가지는 Database를생성한 후에 그 DB를 target으로 해서 복원해야 한다. (-C 옵션 제거)
...
[browndwarf@localhost temp]# pg_restore -h localhost -U postgres -C -d postgres -F t ~/Public/galaxy_db2_back.tar
pg_restore: [archiver (db)] could not execute query: ERROR: invalid locale name: "Korean_Korea.949"
Command was: CREATE DATABASE "GALAXY2" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'Korean_Korea.949' LC_CTYPE = 'Korean_Korea.949';
...
Tip) Auto Daily Backup 설정의 예
가끔 고객이 POC 단계에서 DB 이중화를 위한 별도의 기기를 제공하기 힘들다고 하면서, 그래도도 재해 시 복구에 대한 대안을 제시해 달라고 '말인지 방구인지' 스러운 요구를 들을 때가 많다. 그래서 대안으로 제시하는 게 POC 기간동안에는 매일 1회씩 Daily Backup을 해서 보관하고 문제 발생시에 가장 최종본으로 복구를 하겠다는 방식으로 얘기한다. (약간의 Data Loss는 감안해 달라 요청) 그 떄 사용하는 방법이 아래와 같이 crontab을 활용한 매일 1회 Daily Backup이다. (Backup된 파일의 유지 관리는 미포함)
...
[browndwarf@localhost temp]# crontab -e
05 01 * * * pg_dump -d galaxy -h localhost -U browndwarf -F t > ~/Public/database_name_$(date +\%Y\%m\%d).tar
...
crontab: installing new crontab
...
[browndwarf@localhost ~]# cd ~/Public/
[browndwarf@localhost Public]# ls -la
total 561596
drwxr-xr-x. 2 browndwarf browndwarf 4096 Jan 4 09:32 .
drwxr-xr-x. 3 browndwarf browndwarf 4096 Jan 4 09:32 ..
-rw-r--r--. 1 browndwarf browndwarf 191385088 Jan 1 01:05 database_name_20190101.tar
-rw-r--r--. 1 browndwarf browndwarf 191837696 Jan 2 01:05 database_name_20190102.tar
-rw-r--r--. 1 browndwarf browndwarf 191837696 Jan 3 01:05 database_name_20190103.tar
...
참조
'Database > PostgreSQL' 카테고리의 다른 글
| PostgreSQL Log로 DB 성능 분석 (feat. pgBadger) (2) | 2019.06.03 |
|---|---|
| PostgreSQL Log 설정 - 2 (10) | 2019.05.10 |
| PostgreSQL Log 설정 - 1 (2) | 2018.12.04 |
| PostgreSQL HA 구성 - 3. PGPool을 활용한 FailOver 구현 (7) | 2018.10.12 |
| PostgreSQL HA 구성 - 2. PGPool을 활용한 Load Balance 구현 (1) | 2018.10.05 |