postgresql 백업툴
- 논리 백업 툴 : pg_dump, pg_dumpall
- 물리 백업 툴 : pg_basebackup, pg_rman 등등
논리 백업 툴
- pg_dump : 한 개의 디비를 백업, 여러 개 백업하려면 pg_dumpall 을 사용해야 함
- pg_dumpall :
클러스터 내의 모든 디비, roles, tablespaces, privilege grants for configuration parameters
pg_dumpall 유용할 것 같은 옵션
-c --if-exist : dump 구문을 생성하기 전에 있으면 삭제 하는 옵션. 여러 번 수행하는 경우에 요긴함.
--schema-only : 스키마만 dump
--data-only --disable-triggers : 데이터만 dump, 데이터 입력 전 trigger disable, 입력 후 enable
--lock-wait-timeout: 백업을 시작하기 위해 shared table locks 을 대기 위한 timeout
pg_dumpall 주의사항
복구 후에는 옵티마이져가 유용한 통계를 가질 수 있도록 ANALYZE 수행
vacuumdb -a -z 수행을 하는 것을 권장 한다.
pg_dumpall 전체 옵션
Options controlling the output content:
-a, --data-only schema를 제외한 데이터만
-c, --clean drop database 포함
$ pg_dumpall -c -f dumpall.sql
$ cat dumpall.sql 에 아래 구문 추가
-- Drop databases (except postgres and template1)
DROP DATABASE test;
DROP DATABASE test1;
DROP DATABASE test2;
-- Drop roles
DROP ROLE testusr; -E, --encoding=ENCODING dump the data in encoding ENCODING
-g, --globals-only dump only global objects, no databases
-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
# pg_dumpall --data-only --disable-triggers -f dumpall_distriger.sql
ALTER TABLE public.price DISABLE TRIGGER ALL;
COPY public.price (id, price) FROM stdin;
3 400
\.
ALTER TABLE public.price ENABLE TRIGGER ALL;--exclude-database=PATTERN 특정 디비를 제외하고 백업받거나 pattern에 매칭된 디비를 제외
$ pg_dumpall --exclude-database=test -f dumpall_exc_test.sql
$ cat dumpall.sql|grep 'CREATE DATABASE'
CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'C';
CREATE DATABASE test1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'C';
CREATE DATABASE test2 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'C';
$ pg_dumpall --exclude-database=test -f dumpall_exc_test.sql
$ cat dumpall_exc_test.sql |grep 'CREATE DATABASE'
CREATE DATABASE test1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'C';
CREATE DATABASE test2 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'C'; --extra-float-digits=NUM override default setting for extra_float_digits
--if-exists use IF EXISTS when dropping objects
$ pg_dumpall -c --if-exist -f dumpall_ifexist.sql
DROP DATABASE IF EXISTS test;
DROP ROLE IF EXISTS nhn; --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-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-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
--rows-per-insert=NROWS number of rows per INSERT; implies --inserts
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownership
