瀚高数据库(HGDB)不同schema间的数据迁移
来源:互联网 发布:2017nba数据统计 编辑:程序博客网 时间:2024/06/06 05:43
本实验将瀚高数据库(HighGo Database)下的public.test表导入到schema lk下
1、验证public.test表存在及其表中数据
[highgo@sourcedb ~]$ psql -d highgo -U highgopsql (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.sqlSET search_path = public, pg_catalog;
修改为
SET search_path = lk, pg_catalog;
5、执行导入
[highgo@sourcedb ~]$ psql highgo < /tmp/test.sqlSET
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)
阅读全文
0 0
- 瀚高数据库(HGDB)不同schema间的数据迁移
- postgresql不同schema下的数据迁移
- HGDB瀚高数据库各进制之间的转换
- 瀚高数据库(HGDB 3.1.4)常用管理命令
- 不同服务器的数据库(mysql、SqlServer)间数据迁移
- 高性能java实现不同服务器直接的数据库迁移。
- Oracle用数据泵转移SCHEMA(不同版本数据库)
- PG(HGDB)中创建schema及修改schema的属主及名称
- 不同系统间的数据库迁移
- 不同字符集的数据库之间的数据迁移问题
- 常用的数据库schema迁移语句
- Oracle schema 级别的数据迁移
- Oracle schema 级别的数据迁移
- 不同数据库之间的数据迁移方案设计及迁移工具选择
- HGDB重建模板数据库的方法(适用于PG)
- 从HGDB 1.3版本开始,瀚高数据库中添加了dual表
- 数据库不同版本的相互迁移问题(DTS/SSIS)
- mysql和postgresql数据库间的数据迁移(注释迁移)
- jquery缓存
- 网卡配置bond
- FastJSON、Gson和Jackson性能对比
- LeetCode-----First Unique Character in a String
- C语言笔记——变量的存储类型
- 瀚高数据库(HGDB)不同schema间的数据迁移
- shopnc模型的坑点
- 【Oracle 12c 多租户专题】PDB的内存资源管理
- Python基础02--迭代器、生成器、列表解析
- PDF转CAD怎么转换?(所有格式格式转换方法通用)
- 【安全牛学习笔记】利用配置不当提权
- Android轻松实现代码混淆
- java共享锁实现原理及CountDownLatch解析
- tesseract-ocr 4.0 安装及使用