PostgreSQL 9.5 Alpha 版都已经出来近一个月了，新增了不少重量级的功能，最近比较忙，一直没空来测， 挤点时间，测着玩下。以后博客更新不会像之前频繁，好在现在了解 PG 或者对 PG 有兴趣的人越来越多，而且阿里云也上了 PG 的 RDS, 还希望大家多写博客分享，推动 PG 的中文化。
之前版本 PG 在定义外部表时需要一张一张表定义，例如：
1 2 3 4 5
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 版本可以通过一个命令定义源库所有外部表，如下：
1 2 3 4 5
IMPORT FOREIGN SCHEMA remote_schema [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ] FROM SERVER server_name INTO local_schema [ OPTIONS ( option 'value' [, ... ] ) ]
备注：接着实验演示。
环境准备
创建建用户
1
CREATE ROLE fdb LOGIN ENCRYPTED PASSWORD 'fdb' nosuperuser noinherit nocreatedb nocreaterole ;
创建表空间
1 2
mkdir -p /database/pg95/pg_tbs/tbs_fdb_card create tablespace tbs_fdb_card owner postgres LOCATION '/database/pg95/pg_tbs/tbs_fdb_card' ;
创建数据库
1
CREATE DATABASE fdb WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = tbs_fdb_card;
赋权
1
grant all on database fdb to fdb with grant option ;
创建模式
1 2
/c fdb fdb create schema fdb;
服务器上另一个版本为 pg9.3.3 实例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
[pg95@db1 ~]$ psql -h 127.0 .0 .1 -p 1921 francs francs psql (9.5 alpha1, 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 的外部表。
创建外部表
创建外部服务器
1 2 3 4 5 6 7 8 9 10 11 12 13 14
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 之前版本: 创建外部表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
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 | 1 a (1 row) fdb=> select count (*) from ft_test_1; count -------- 100000 (1 row)
9.5 版本: 一次性创建源库所有外部表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
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 过滤功能
1 2 3
IMPORT FOREIGN SCHEMA public EXCEPT (reports, audit) FROM SERVER dest_server INTO remote;
5.2 指定某些表
1 2 3
IMPORT FOREIGN SCHEMA public LIMIT TO (customers, purchases) FROM SERVER dest_server INTO remote;
备注：这两个功能暂未测试，有兴趣的朋友可以测试下。
参考
原创文章，作者：kepupublish，如若转载，请注明出处：https://blog.ytso.com/239626.html
