瀚高数据库（HGDB）不同schema间的数据迁移

本实验将瀚高数据库（HighGo Database）下的public.test表导入到schema lk下

1、验证public.test表存在及其表中数据

[highgo@sourcedb ~]$ psql -d highgo -U highgo
psql (3.1.4)
Type "help" for help.

highgo=# \dt
         List of relations
 Schema |   Name   | Type  | Owner  
--------+----------+-------+--------
...
 public | persons  | table | highgo
 public | students | table | highgo
 public | t        | table | highgo
...
(17 rows)

highgo=# select * from pg_tables where tablename='test';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers 
------------+-----------+------------+------------+------------+----------+-------------
 public     | test      | highgo     |            | t          | f        | f
(1 row)

highgo=# select * from test;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
(3 rows)

2、导出test表数据

[highgo@sourcedb ~]$ pg_dump -d highgo -t test -f /tmp/test.sql

[highgo@sourcedb ~]$ cat /tmp/test.sql 
--
-- PostgreSQL database dump
--


SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;


SET search_path = public, pg_catalog;    --->该行决定导入时所要导入的schema


SET default_tablespace = '';


SET default_with_oids = false;


--
-- Name: test; Type: TABLE; Schema: public; Owner: highgo; Tablespace: 
--


CREATE TABLE test (
    id integer,
    name character(5)
);




ALTER TABLE test OWNER TO highgo;        ---->该行决定所要导入的表的所有者


--
-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: highgo
--


COPY test (id, name) FROM stdin;
1       \N
2       \N
3       \N
\.


--
-- Name: t_i_2; Type: INDEX; Schema: public; Owner: highgo; Tablespace: 
--


CREATE INDEX t_i_2 ON test USING btree (id);

--
-- Name: test; Type: ACL; Schema: public; Owner: highgo
--

REVOKE ALL ON TABLE test FROM PUBLIC;
REVOKE ALL ON TABLE test FROM highgo;
GRANT ALL ON TABLE test TO highgo;
GRANT SELECT ON TABLE test TO readonly;

--
-- PostgreSQL database dump complete
--

3、新建用户及schema lk：
[highgo@sourcedb ~]$ psql
psql (3.1.4)
Type "help" for help.
highgo=# create user lk with password 'lk';
CREATE ROLE
highgo=# create schema authorization lk;
CREATE SCHEMA

4、导入前修改如下内容

[highgo@sourcedb ~]$ vi /tmp/test.sql 
SET search_path = public, pg_catalog;
修改为
SET search_path = lk, pg_catalog;

5、执行导入

[highgo@sourcedb ~]$ psql highgo < /tmp/test.sql 
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT
[highgo@sourcedb ~]$


[highgo@sourcedb ~]$ psql -d highgo -U lk
psql (3.1.4)
Type "help" for help.


highgo=> \d
             List of relations
     Schema     |   Name   | Type  | Owner  
----------------+----------+-------+--------
 lk             | test     | table | highgo
 oracle_catalog | dual     | view  | highgo
 public         | a        | table | highgo
 public         | b        | table | highgo
 
 
highgo=> select * from lk.test;
错误:  对关系 test 权限不够
highgo=> 
highgo=> alter table lk.test owner to lk;
错误:  必须是关系 test 的属主
highgo=> \c highgo highgo
You are now connected to database "highgo" as user "highgo".
highgo=# alter table lk.test owner to lk;
ALTER TABLE
highgo=# \c highgo lk
You are now connected to database "highgo" as user "lk".
highgo=> select * from test;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
(3 rows)

6、第5步的替换方法
或者在导入前修改/tmp/test.sql中的：
ALTER TABLE test OWNER TO highgo;
为
ALTER TABLE test OWNER TO lk;

7、验证
[highgo@sourcedb ~]$ psql -d highgo -U lk
psql (3.1.4)
Type "help" for help.
highgo=> \dt
         List of relations
 Schema |   Name   | Type  | Owner  
--------+----------+-------+--------
 lk     | test     | table | lk
 public | a        | table | highgo
 
 
 highgo=# select tablename,tableowner,schemaname from pg_tables where tablename = 'test';
 tablename | tableowner | schemaname 
-----------+------------+------------
 test      | highgo     | public
 test      | lk         | lk
(2 rows)