Jul 30

EnterpriseDb图形化的表空间备份恢复测试

admin , 15:13 , 数据库 » 数据库(EnterpriseDb) , 评论(0) , 引用(0) , 阅读(1815) , Via 本站原创 | |
搜索
我已经获得阿里云幸运券,准备分享给您。请点击获取  

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;