PostgreSQL 은 특별합니다.
단순한 데이터베이스가 아니라, 하나의 어플리케이션 플랫폼이기 때문입니다.

알림

공지사항

	총 게시물 11건, 최근 0 건

SINGLE 데이터베이스 백업받기

글쓴이 : PostgresDBA 날짜 : 2012-12-01 (토) 16:30 조회 : 11409

[지식창고>SQL>오라클의 SCOTT 계정 흉내내기] 글에서

생성한 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 스키마명 을 지정해주면 해당 스키마 대상으로만 덤프가 이루어 집니다.

E-MAIL: root@postgresdba.com