[Postgresql] 데이터 베이스 복구

-- 백업본을 확인합니다.

[postgres@olmaster:~]$ lst

합계 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 11345 2012-12-01 16:26 .bash_history

-rw-------.  1 postgres postgres 13641 2012-12-01 16:57 .psql_history

-rw-r--r--.  1 postgres postgres  5258 2012-12-01 23:56 scott_scottdb.backup

-rw-r--r--.  1 postgres postgres  4920 2012-12-01 23:57 scott_scottdb_backup.sql

drwx------.  4 postgres postgres  4096 2012-12-01 23:57 ./

[postgres@olmaster:~]$ 

-- 존재하는 scottdb 데이터베이스를 삭제합니다. 나중에 이 scottdb 를 복구할겁니다.

[postgres@olmaster:~]$ psql -U postgres

Password for user postgres: 

Null display is "NULL".

Timing is on.

Pager is always used.

psql (9.2.1)

Type "help" for help.

postgres@[local]:5432 postgres#SQL> drop database scottdb;

DROP DATABASE

Time: 124.638 ms

postgres@[local]:5432 postgres#SQL> \q

-- 백업본을 이용하여 scottdb 데이터베이스 복구를 시도합니다.

[postgres@olmaster:~]$ pg_restore -U scott --dbname=scottdb --verbose scott_scottdb.backup 

pg_restore: connecting to database for restore

Password: 

pg_restore: [archiver (db)] connection to database "scottdb" failed: FATAL:  database "scottdb" does not exist

-- scottdb가 존재하지 않는다고 에러를 내뱉네요.

-- createdb 스크립트를 이용하여 scottdb 데이터베이를 생성합니다. 빈껍데기 일뿐이지요.

[postgres@olmaster:~]$ createdb -O scott scottdb

암호:          # scott 유저가 아닌 postgres 유저의 패스워드를 입력해야 합니다.

[postgres@olmaster:~]$ 

-- 다시 복구를 시도합니다.

[postgres@olmaster:~]$ pg_restore -U scott --dbname=scottdb --verbose scott_scottdb.backup 

pg_restore: connecting to database for restore

Password: 

pg_restore: creating SCHEMA public

pg_restore: creating COMMENT SCHEMA public

pg_restore: creating EXTENSION plpgsql

pg_restore: creating COMMENT EXTENSION plpgsql

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 2813; 0 0 COMMENT EXTENSION plpgsql 

pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql

    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

pg_restore: creating TABLE bonus

pg_restore: creating TABLE dept

pg_restore: creating TABLE dummy

pg_restore: creating TABLE emp

pg_restore: creating TABLE salgrade

pg_restore: restoring data for table "bonus"

pg_restore: restoring data for table "dept"

pg_restore: restoring data for table "dummy"

pg_restore: restoring data for table "emp"

pg_restore: restoring data for table "salgrade"

pg_restore: creating CONSTRAINT dept_pkey

pg_restore: creating CONSTRAINT emp_pkey

pg_restore: creating FK CONSTRAINT emp_deptno_fkey

pg_restore: setting owner and privileges for DATABASE scottdb

pg_restore: setting owner and privileges for SCHEMA public

pg_restore: setting owner and privileges for COMMENT SCHEMA public

pg_restore: setting owner and privileges for ACL public

pg_restore: WARNING:  no privileges could be revoked for "public"

pg_restore: WARNING:  no privileges could be revoked for "public"

pg_restore: WARNING:  no privileges were granted for "public"

pg_restore: WARNING:  no privileges were granted for "public"

pg_restore: setting owner and privileges for EXTENSION plpgsql

pg_restore: setting owner and privileges for COMMENT EXTENSION plpgsql

pg_restore: setting owner and privileges for TABLE bonus

pg_restore: setting owner and privileges for TABLE dept

pg_restore: setting owner and privileges for TABLE dummy

pg_restore: setting owner and privileges for TABLE emp

pg_restore: setting owner and privileges for TABLE salgrade

pg_restore: setting owner and privileges for TABLE DATA bonus

pg_restore: setting owner and privileges for TABLE DATA dept

pg_restore: setting owner and privileges for TABLE DATA dummy

pg_restore: setting owner and privileges for TABLE DATA emp

pg_restore: setting owner and privileges for TABLE DATA salgrade

pg_restore: setting owner and privileges for CONSTRAINT dept_pkey

pg_restore: setting owner and privileges for CONSTRAINT emp_pkey

pg_restore: setting owner and privileges for FK CONSTRAINT emp_deptno_fkey

WARNING: errors ignored on restore: 1

[postgres@olmaster:~]$ 

오. 복구가 잘 됐습니다.