설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


Powered by EnterpriseDB
총 게시물 11건, 최근 0 건
 

SINGLE 데이터베이스 백업받기

글쓴이 : PostgresDBA 날짜 : 2012-12-01 (토) 16:30 조회 : 5171
생성한 SCOTT 유저의 데이터를 백업받아 보겠습니다.

싱글 데이터베이스를 백업받아 압축하는 명령어입니다. ( c옵션: custom)
[postgres@olmaster:~]$ pg_dump -d scottdb -U scott -F c -b -v -f scott_scottdb.backup
Password: 
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading extensions
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: finding extension members
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "dept"
pg_dump: finding the columns and types of table "emp"
pg_dump: finding the columns and types of table "bonus"
pg_dump: finding the columns and types of table "salgrade"
pg_dump: finding the columns and types of table "dummy"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "dept"
pg_dump: reading indexes for table "emp"
pg_dump: reading constraints
pg_dump: reading foreign key constraints for table "dept"
pg_dump: reading foreign key constraints for table "emp"
pg_dump: reading triggers
pg_dump: reading triggers for table "dept"
pg_dump: reading triggers for table "emp"
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition
pg_dump: dumping contents of table bonus
pg_dump: dumping contents of table dept
pg_dump: dumping contents of table dummy
pg_dump: dumping contents of table emp
pg_dump: dumping contents of table salgrade
[postgres@olmaster:~]$ 

[postgres@olmaster:~]$ ls -l
합계 16
drwx------. 4 postgres postgres 4096 2012-10-31 22:33 9.2/
drwxr-xr-x. 2 postgres postgres 4096 2012-11-18 14:57 oradba/
-rw-r--r--. 1 postgres postgres 5258 2012-12-01 16:27 scott_scottdb.backup

[postgres@olmaster:~]$ file scott_scottdb.backup 
scott_scottdb.backup: PostgreSQL custom database dump - v1.12-0
[postgres@olmaster:~]$ cat scott_scottdb.backup |more   
PGDMP^L                                  # 생성된 백업 화일은 바이너리 화일이네요.
[postgres@olmaster:~]$ 

SQL 구문의 텍스트 화일 형태로 백업받아 보겠습니다. (p 옵션 : plain)

[postgres@olmaster:~]$ pg_dump scottdb -U scott -F p -b -v -f scott_scottdb_backup.sql
Password: 
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading extensions
.........
[postgres@olmaster:~]$ ls -alrt
합계 72
-rw-r--r--.  1 postgres postgres   131 2012-10-30 13:30 .bashrc
drwx------.  4 postgres postgres  4096 2012-10-31 22:33 9.2/
drwxr-xr-x. 51 root     root      4096 2012-11-10 23:37 ../
drwxr-xr-x.  2 postgres postgres  4096 2012-11-18 14:57 oradba/
-rw-------.  1 postgres postgres  1238 2012-11-18 14:57 .bash_profile
-rw-r--r--.  1 postgres postgres   532 2012-11-22 09:33 .psqlrc
-rw-------.  1 postgres postgres 13520 2012-12-01 16:14 .psql_history
-rw-------.  1 postgres postgres 11345 2012-12-01 16:26 .bash_history
-rw-r--r--.  1 postgres postgres  5258 2012-12-01 16:27 scott_scottdb.backup
drwx------.  4 postgres postgres  4096 2012-12-01 16:33 ./
-rw-r--r--.  1 postgres postgres  4920 2012-12-01 16:33 scott_scottdb_backup.sql
[postgres@olmaster:~]$ file scott_scottdb_backup.sql 
scott_scottdb_backup.sql: ASCII text
[postgres@olmaster:~]$ cat scott_scottdb_backup.sql |more  # 생성된 백업화일은 TEXT 화일이네요.
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.2.1
-- Dumped by pg_dump version 9.2.1
-- Started on 2012-12-01 16:33:08 KST

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
.......

참고로 -s  옵션을 붙이면 데이터는 백업되지 않고 스키마만 덤프합니다.
또한 -n 스키마명 을 지정해주면 해당 스키마 대상으로만 덤프가 이루어 집니다.

 

postgresdba.com