ITLife365 生活 互动学习空间 - 互动学习空间

EnterpriseDb 图形化的表空间备份恢复测试：
 有数据库test,表student,主键pk_student 放在testDb 表空间,索引。。。放在test_indx 表空间

1 在异机建立数据库test2(即和备份的数据库不同名称)：执行恢复前清空数据 情况：会把表相关弄过去，但是但是会是在默认的模式里面默认的表空间进行，同时新建我们原来的模式test,但是里面没有数据，再次执行时才有：以下是返回的结果
-- Table: test.student

-- DROP TABLE test.student;

CREATE TABLE test.student
(
  sno integer NOT NULL,
  sname character varying(50),
  CONSTRAINT pk_student PRIMARY KEY (sno) -- sdf
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test.student OWNER TO "admin";
COMMENT ON CONSTRAINT pk_student ON test.student IS 'sdf';
；

-- Index: test.student_index_name
-- DROP INDEX test.student_index_name;

CREATE UNIQUE INDEX student_index_name
  ON test.student
  USING btree
  (sname);
COMMENT ON INDEX test.student_index_name IS '根据name 索引';

-- Table: student

-- DROP TABLE student;

CREATE TABLE student
(
  sno integer NOT NULL,
  sname character varying(50),
  CONSTRAINT pk_student PRIMARY KEY (sno) -- sdf
)
WITH (
  OIDS=FALSE
);
ALTER TABLE student OWNER TO "admin";
COMMENT ON CONSTRAINT pk_student ON student IS 'sdf';

-- Index: student_index_name

-- DROP INDEX student_index_name;

CREATE UNIQUE INDEX student_index_name
  ON student
  USING btree
  (sname);
COMMENT ON INDEX student_index_name IS '根据name 索引';

2 在异机建立数据库test(即和备份的数据库相同名称)：执行恢复前清空数据 情况：会把表相关弄过去，但是但是会是在默认的模式里面默认的表空间进行，同时新建我们原来的模式test,但是里面没有数据，再次执行时才有：以下是返回的结果
C:\PostgresPlus\8.3R2AS\dbserver\bin\pg_restore.exe --host 192.168.1.35 --port 5444 --username "admin" --dbname test --clean --verbose "C:\Documents and Settings\dell\桌面\test_20100730_0934.backup"
pg_restore: 正在连接到数据库以进行恢复
pg_restore: 正在删除 COMMENT INDEX student_index_name
pg_restore: [存档程序] PROCESSING TOC 时出错:
pg_restore: [存档程序] 错误来自 TOC 条目 2490；0 0 COMMENT INDEX student_index_name enterprisedb
pg_restore: [存档程序] could not set search_path to "test": ERROR:  schema "test" does not exist
pg_restore: 正在删除 INDEX student_index_name
pg_restore: [归档 (db)] 错误来自 TOC 条目 2483；1259 52242 INDEX student_index_name enterprisedb
pg_restore: [归档 (db)] could not execute query: ERROR:  schema "test" does not exist
    Command was:
DROP INDEX test.student_index_name;
pg_restore: 正在删除 COMMENT CONSTRAINT pk_student ON student
pg_restore: 正在删除 CONSTRAINT pk_student
pg_restore: [归档 (db)] 错误来自 TOC 条目 2482；2606 52240 CONSTRAINT pk_student enterprisedb
pg_restore: [归档 (db)] could not execute query: ERROR:  schema "test" does not exist
    Command was: ALTER TABLE ONLY test.student DROP CONSTRAINT pk_student;
pg_restore: 正在删除 TABLE student
pg_restore: [归档 (db)] 错误来自 TOC 条目 2033；1259 52237 TABLE student enterprisedb
pg_restore: [归档 (db)] could not execute query: ERROR:  schema "test" does not exist
    Command was: DROP TABLE test.student;
pg_restore: 正在删除 COMMENT SCHEMA test
pg_restore: 正在删除 SCHEMA test
pg_restore: [归档 (db)] 错误来自 TOC 条目 7；2615 52236 SCHEMA test enterprisedb
pg_restore: [归档 (db)] could not execute query: ERROR:  schema "test" does not exist
    Command was: DROP SCHEMA test;
pg_restore: 正在创建 SCHEMA test
pg_restore: [归档 (db)] could not execute query: ERROR:  role "enterprisedb" does not exist
    Command was: ALTER SCHEMA test OWNER TO enterprisedb;
pg_restore: 正在创建 COMMENT SCHEMA test
pg_restore: 正在创建 TABLE student
pg_restore: [存档程序] 错误来自 TOC 条目 2033；1259 52237 TABLE student enterprisedb
pg_restore: [存档程序] could not set default_tablespace to "testTB": ERROR:  tablespace "testTB" does not exist
pg_restore: [归档 (db)] could not execute query: ERROR:  relation "test.student" does not exist
    Command was: ALTER TABLE test.student OWNER TO enterprisedb;
pg_restore: 正在为表 "student" 恢复数据
pg_restore: 正在创建 CONSTRAINT pk_student
pg_restore: 正在创建 COMMENT CONSTRAINT pk_student ON student
pg_restore: 正在创建 INDEX student_index_name
pg_restore: [存档程序] 错误来自 TOC 条目 2483；1259 52242 INDEX student_index_name enterprisedb
pg_restore: [存档程序] could not set default_tablespace to test_indx: ERROR:  tablespace "test_indx" does not exist
pg_restore: 正在创建 COMMENT INDEX student_index_name
pg_restore: 正在为 SCHEMA test 设置所有者和权限
pg_restore: 正在为 COMMENT SCHEMA test 设置所有者和权限
pg_restore: 正在为 TABLE student 设置所有者和权限
pg_restore: 正在为 CONSTRAINT pk_student 设置所有者和权限
pg_restore: 正在为 COMMENT CONSTRAINT pk_student ON student 设置所有者和权限
pg_restore: 正在为 INDEX student_index_name 设置所有者和权限
pg_restore: 正在为 COMMENT INDEX student_index_name 设置所有者和权限
警告: 恢复时忽略了错误:9

表的定义：

-- Table: test.student

-- DROP TABLE test.student;

CREATE TABLE test.student
(
  sno integer NOT NULL,
  sname character varying(50),
  CONSTRAINT pk_student PRIMARY KEY (sno) -- sdf
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test.student OWNER TO "admin";
COMMENT ON CONSTRAINT pk_student ON test.student IS 'sdf';

-- Index: test.student_index_name

-- DROP INDEX test.student_index_name;

CREATE UNIQUE INDEX student_index_name
  ON test.student
  USING btree
  (sname);
COMMENT ON INDEX test.student_index_name IS '根据name 索引';

进程退出并返回 1。

3

C:\PostgresPlus\8.3R2AS\dbserver\bin\pg_restore.exe --host 192.168.1.35 --port 5444 --username "admin" --dbname test --clean --verbose "C:\Documents and Settings\dell\桌面\test_20100730_0934.backup"
pg_restore: 正在连接到数据库以进行恢复
pg_restore: 正在删除 COMMENT INDEX student_index_name
pg_restore: 正在删除 INDEX student_index_name
pg_restore: 正在删除 COMMENT CONSTRAINT pk_student ON student
pg_restore: 正在删除 CONSTRAINT pk_student
pg_restore: 正在删除 TABLE student
pg_restore: 正在删除 COMMENT SCHEMA test
pg_restore: 正在删除 SCHEMA test
pg_restore: 正在创建 SCHEMA test
pg_restore: [归档 (db)] PROCESSING TOC 时出错:
pg_restore: [归档 (db)] 错误来自 TOC 条目 7；2615 52236 SCHEMA test enterprisedb
pg_restore: [归档 (db)] could not execute query: ERROR:  role "enterprisedb" does not exist
    Command was: ALTER SCHEMA test OWNER TO enterprisedb;
pg_restore: 正在创建 COMMENT SCHEMA test
pg_restore: 正在创建 TABLE student
pg_restore: [存档程序] 错误来自 TOC 条目 2033；1259 52237 TABLE student enterprisedb
pg_restore: [存档程序] could not set default_tablespace to "testTB": ERROR:  tablespace "testTB" does not exist
pg_restore: [归档 (db)] could not execute quer
进程退出并返回 1。

4 创建test库和相应的test 模式

C:\PostgresPlus\8.3R2AS\dbserver\bin\pg_restore.exe --host 192.168.1.35 --port 5444 --username "admin" --dbname test --no-owner --clean --verbose "C:\Documents and Settings\dell\桌面\test_20100730_0934.backup"
pg_restore: 正在连接到数据库以进行恢复
pg_restore: 正在删除 COMMENT INDEX student_index_name
pg_restore: 正在删除 INDEX student_index_name
pg_restore: 正在删除 COMMENT CONSTRAINT pk_student ON student
pg_restore: 正在删除 CONSTRAINT pk_student
pg_restore: 正在删除 TABLE student
pg_restore: 正在删除 COMMENT SCHEMA test
pg_restore: 正在删除 SCHEMA test
pg_restore: 正在创建 SCHEMA test
pg_restore: 正在创建 COMMENT SCHEMA test
pg_restore: 正在创建 TABLE student
pg_restore: [存档程序] PROCESSING TOC 时出错:
pg_restore: [存档程序] 错误来自 TOC 条目 2033；1259 52237 TABLE student enterprisedb
pg_restore: [存档程序] could not set default_tablespace to "testTB": ERROR:  tablespace "testTB" does not exist
pg_restore: 正在为表 "student" 恢复数据
pg_restore: 正在创建 CONSTRAINT pk_student
pg_restore: 正在创建 COMMENT CONSTRAINT pk_student ON student
pg_restore: 正在创建 INDEX student_index_name
pg_restore: [存档程序] 错误来自 TOC 条目 2483；1259 52242 INDEX student_index_name enterprisedb
pg_restore: [存档程序] could not set default_tablespace to test_indx: ERROR:  tablespace "test_indx" does not exist
pg_restore: 正在创建 COMMENT INDEX student_index_name
pg_restore: 正在为 SCHEMA test 设置所有者和权限
pg_restore: 正在为 COMMENT SCHEMA test 设置所有者和权限
pg_restore: 正在为 TABLE student 设置所有者和权限
pg_restore: 正在为 CONSTRAINT pk_student 设置所有者和权限
pg_restore: 正在为 COMMENT CONSTRAINT pk_student ON student 设置所有者和权限
pg_restore: 正在为 INDEX student_index_name 设置所有者和权限
pg_restore: 正在为 COMMENT INDEX student_index_name 设置所有者和权限
警告: 恢复时忽略了错误:2

5 当所有的环境都和原先备份的差不多的时候：ok
C:\PostgresPlus\8.3R2AS\dbserver\bin\pg_restore.exe --host localhost --port 5444 --username enterprisedb --dbname test --clean --verbose "D:\28服务器备份\EnterpriseDb\gk\test_20100730_0934.backup"

进程退出并返回 0。

把表移动到另外一个表空间：

ALTER TABLE distributors SET TABLESPACE fasttablespace;

把表移动到另外一个模式：

ALTER TABLE myschema.distributors SET SCHEMA yourschema;