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


총 게시물 11건, 최근 0 건
   

SINGLE 데이터베이스 복구하기

글쓴이 : PostgresDBA 날짜 : 2012-12-02 (일) 00:20 조회 : 13694
-- 백업본을 확인합니다.
[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:~]$ 

오. 복구가 잘 됐습니다.

   

postgresdba.com