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 使用
举例说明:
一个是本地数据库,一个是远程数据库,如下表格:
server | IP | port | user | shcema | db |
---|---|---|---|---|---|
本地 | 192.168.10.11 | 5432 | amdin | public | db1 |
远程 | 192.168.10.12 | 5432 | postgres | public | db2 |
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,
name text,
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,
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', 'sqlserver', 'mysql');
-- 查看枚举类型的oid
select oid from pg_type where typname='user_enum';
-- 创建外部表lodtest1
CREATE FOREIGN TABLE lodtest1(
id int NOT NULL,
name text,
create_time timestamptz,
remark varchar(10),
chart_type char(10),
enum_type db_enum,
) 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/