postgres_fdw 的使用

postgres_fdw模块

Postgresql 9.3 add postgres_fdw extension for accessing remote tables
Postgresql 9.3新增了一个postgres_fdw模块,已经整合在源码包中. 用于创建postgres外部表.

此前名为pgsql_fdw,pgsql_fdw未整合到contrib中,使用方法如下.，请移步到专业大牛的链接查看：
http://blog.163.com/digoal@126/blog/static/163877040201231514057303/

postgres_fdw 使用

举例说明：

一个是本地数据库，一个是远程数据库，如下表格：

1. 远程执行代码

-- 创建一个枚举类型
 CREATE TYPE db_enum AS ENUM ('postgres','sqlserver','MysqL');

-- 查看枚举类型的oid
 select oid from pg_type where typname='db_enum';

-- 创建一个架构HMADB
 CREATE SCHEMA HMADB;

-- 创建表test1
 CREATE TABLE HMADB.test1 ( id int NOT NULL,name text,create_time timestamptz,remark varchar(10),chart_type char(10),enum_type db_enum,CONSTRAINT t1_pkey PRIMARY KEY (c1) );

-- 创建表test2
 CREATE TABLE HMADB.test2 ( id int NOT NULL,CONSTRAINT t2_pkey PRIMARY KEY (c1) );

-- 插入数据
 INSERT INTO HMADB.test1 SELECT id,to_char(id,'FM00000'),'1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,id % 10,'postgres'::db_enum FROM generate_series(1,1000) id;
 INSERT INTO HMADB.test2 SELECT id,'cwm' || to_char(id,'FM000') FROM generate_series(1,100) id;

-- 分析 :  ANALYZE HMADB.test1;
 ANALYZE HMADB.test2;

注意是创建在db2 数据库 public下面的.

2. 本地执行代码

-- 安装 postgres_fdw 插件
  CREATE EXTENSION postgres_fdw;

-- 创建远程服务 
  CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw;

-- 查看远程服务
  select * from pg_foreign_server ;

-- 修改远程服务 
 alter server remote_server options ( add hostaddr '192.168.10.12',add port '5432',add dbname 'db2');

-- SERVER赋权 : 
 grant usage on foreign server remote_server to amdin;

-- 查看远程服务
 select * from pg_foreign_server ;

-- 在本地数据库中创建user mapping : 
 CREATE USER MAPPING FOR amdin server remote_server options (user 'postgres',password 'xxxx');

-- 同样创建枚举
 CREATE TYPE db_enum AS ENUM ('postgres','MysqL');

-- 查看枚举类型的oid
 select oid from pg_type where typname='user_enum';

-- 创建外部表lodtest1
  CREATE FOREIGN TABLE lodtest1( id int NOT NULL,) SERVER remote_server options (schema_name 'hmadb',table_name 'test1');

-- 查看外部表
 select * from lodtest1limit 1;

-- 创建外部表lodtest1
 CREATE FOREIGN TABLE lodtest2(id int not null,name text) SERVER remote_server options(schema_name 'hmadb',table_name 'test2');

-- 查看外部表
 select * from lodtest2 limit 1;

注意是创建在db1 数据库 public下面的.
– 设置SERVER 参数语法 :
ALTER SERVER name [ VERSION ‘new_version’ ]
[ OPTIONS ( [ ADD | SET | DROP ] option [‘value’] [,… ] ) ]
ALTER SERVER name OWNER TO new_owner
ALTER SERVER name RENAME TO new_name
– 可用的server参数,请参考 :
http://www.postgresql.org/docs/devel/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS
– 设置SERVER 参数(常用的是地址,端口,库名) :
alter server remote_server options ( add hostaddr ‘xxx’,add port ‘xxx’,add dbname ‘xxx’);
–创建外部表,注意外部表的列顺序无所谓,但是列名必须在远程表中已经存在.

– 注意上面创建外部表时,可用的options为schema_name,table_name,use_remote_estimate(后面会测试到). 那万一列名不一致怎么处理呢?
– 可用使用alter来修改. 如下 :
CREATE FOREIGN TABLE lodtest3 (name text,id3 int not null) SERVER remote_server options(schema_name ‘HMADB’,table_name ‘test2’);
select * from lodtest3 limit 1;
ERROR: column “id3” does not exist
CONTEXT: Remote sql command: SELECT name,id3 FROM hmadb.test2
由于id3列在远程表中不存在,所以报错. 修改后正常 :
alter foreign table lodtest3 alter column id3 options (column_name ‘id’);
ALTER FOREIGN TABLE
select * from lodtest3 limit 1;

总结

基本可以满足postgersql 垮库表同步数据了

– 常用语句

-- # 清理
 drop user mapping for admin server server_remote ;

 drop server server_remote1;

 drop extension postgres_fdw ;

 drop foreign table test1;

-- 相关系统表
 select * from pg_extension;
 select * from pg_foreign_data_wrapper;
 select * from pg_foreign_server;
 select * from pg_foreign_table;

-- 查看有有架构
 SELECT nspname FROM pg_namespace; 

 drop SCHEMA hmauser;

 select * from information_schema.schemata;

 select * from information_schema.tables where table_schema='public';

献上自己参考的原文链接，感谢前辈们的辛苦付出，有你们领路前行，是我们源源不断的动力.

http://blog.163.com/digoal@126/blog/static/163877040201312544919858/