注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL9.5:IMPORT FOREIGN SCHEMA  

2015-07-30 11:59:43|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

PostgreSQL9.5: IMPORT FOREIGN SCHEMA - francs - PostgreSQL DBA
      
         PostgreSQL 9.5 Alpha 版都已经出来近一个月了,新增了不少重量级的功能,最近比较忙,一直没空来测, 挤点时间,测着玩下。以后博客更新不会像之前频繁,好在现在了解 PG 或者对 PG 有兴趣的人越来越多,而且阿里云也上了 PG 的 RDS, 还希望大家多写博客分享,推动 PG 的中文化。
 
  之前版本 PG 在定义外部表时需要一张一张表定义,例如:

CREATE FOREIGN TABLE ft_test_1 (
id    integer  ,
name  text )
SERVER pgsql_srv
OPTIONS (schema_name 'francs', table_name 'test_1');

       假如源库有多张表,那么这样定义比较费力而且容易出错,记得之前做一个 MySQL 转 PG 的项目,MySQL 库不大, 100 来 GB 左右,表总数在 40 张左右,不包含分区表,当初这个项目的数据迁移是通过 mysql_fdw 外部表来做的,虽然只有 40 来张表,脚本量却很大,实施起来也费劲,好在当初经过多次测试,在项目组配合下最终完成了这个项目。
 
  闲话少说,PostgreSQL 9.5 版本可以通过一个命令定义源库所有外部表,如下:

IMPORT FOREIGN SCHEMA remote_schema
[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
FROM SERVER server_name
INTO local_schema
[ OPTIONS ( option 'value' [, ... ] ) ]

备注:接着实验演示。
 
一  初始化

创建建用户
CREATE ROLE fdb LOGIN  ENCRYPTED PASSWORD 'fdb' nosuperuser noinherit nocreatedb nocreaterole ;

创建表空间
mkdir -p /database/pg95/pg_tbs/tbs_fdb_card
create tablespace tbs_fdb_card owner postgres LOCATION '/database/pg95/pg_tbs/tbs_fdb_card';

创建数据库
CREATE DATABASE fdb WITH  OWNER = postgres TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = tbs_fdb_card;
      
赋权
grant all on database fdb to fdb  with grant option;

创建模式
\c fdb fdb
create schema fdb;



二 服务器上另一个版本为 pg9.3.3 实例

[pg95@db1 ~]$ psql -h 127.0.0.1 -p 1921 francs francs
psql (9.5alpha1, server 9.3.3)
Type "help" for help.

francs=> \dt+
                          List of relations
 Schema |      Name      | Type  | Owner  |    Size    | Description
--------+----------------+-------+--------+------------+-------------
 francs | events         | table | francs | 8192 bytes |
 francs | invoice_items  | table | francs | 8192 bytes |
 francs | mkt_bean_shell | table | francs | 16 kB      |
 francs | order_sn       | table | francs | 0 bytes    | 订单号表
 francs | tbl_user_json  | table | francs | 26 MB      |
 francs | test_1         | table | francs | 3960 kB    |
 francs | test_2         | table | francs | 16 kB      |
 francs | test_3         | table | francs | 16 kB      |
 francs | test_4         | table | francs | 16 kB      |
 francs | test_bytea     | table | francs | 16 kB      |
 francs | test_count     | table | francs | 0 bytes    |
 francs | test_dup       | table | francs | 8192 bytes |
 francs | test_time1     | table | francs | 8192 bytes |
 francs | test_time2     | table | francs | 8192 bytes |
 francs | test_trash     | table | francs | 5912 kB    |
 francs | user_ini       | table | francs | 12 MB      |
(16 rows)

备注: 目标在 pg 9.5 实例上创建到 pg9.3.3 的外部表。


三 创建外部服务器

fdb=# create extension postgres_fdw ;
CREATE EXTENSION

fdb=# grant usage on foreign data wrapper postgres_fdw to fdb;
GRANT

fdb=# \c fdb fdb
You are now connected to database "fdb" as user "fdb".

fdb=> CREATE SERVER pgsql_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '1921', dbname 'francs');
CREATE SERVER

fdb=> CREATE USER MAPPING FOR fdb SERVER pgsql_srv OPTIONS (user 'francs', password 'francs');
CREATE USER MAPPING


四 创建外部表
--9.5 之前版本:创建外部表

CREATE FOREIGN TABLE ft_test_1 (
 id    integer  ,
 name  text )
SERVER pgsql_srv
OPTIONS (schema_name 'francs', table_name 'test_1');

fdb=> select * from ft_test_1 limit 1;
 id | name
----+------
  1 | 1a
(1 row)

fdb=> select count(*) from ft_test_1;
 count  
--------
 100000
(1 row)


--9.5 版本:一次性创建源库所有外部表

fdb=> drop foreign table ft_test_1;
DROP FOREIGN TABLE

fdb=> IMPORT FOREIGN SCHEMA francs FROM SERVER pgsql_srv INTO fdb;
IMPORT FOREIGN SCHEMA

fdb=> \dE
                List of relations
 Schema |      Name      |     Type      | Owner
--------+----------------+---------------+-------
 fdb    | events         | foreign table | fdb
 fdb    | invoice_items  | foreign table | fdb
 fdb    | mkt_bean_shell | foreign table | fdb
 fdb    | order_sn       | foreign table | fdb
 fdb    | tbl_user_json  | foreign table | fdb
 fdb    | test_1         | foreign table | fdb
 fdb    | test_2         | foreign table | fdb
 fdb    | test_3         | foreign table | fdb
 fdb    | test_4         | foreign table | fdb
 fdb    | test_bytea     | foreign table | fdb
 fdb    | test_count     | foreign table | fdb
 fdb    | test_dup       | foreign table | fdb
 fdb    | test_time1     | foreign table | fdb
 fdb    | test_time2     | foreign table | fdb
 fdb    | test_trash     | foreign table | fdb
 fdb    | user_ini       | foreign table | fdb
(16 rows)

备注:太棒了!

五 附  IMPORT FOREIGN SCHEMA 更多功能
--5.1 过滤功能

 IMPORT FOREIGN SCHEMA public
   EXCEPT (reports, audit)
   FROM SERVER dest_server INTO remote;


--5.2 指定某些表

 IMPORT FOREIGN SCHEMA public
   LIMIT TO (customers, purchases)
   FROM SERVER dest_server INTO remote;

备注:这两个功能暂未测试,有兴趣的朋友可以测试下。


六 参考
  评论这张
 
阅读(731)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016