考虑成本因素，给客户安装的数据库是开源的postgresql数据库，公司本部用的是oracle。客户本地服务器通过3G无线路由解决了连入Internet的问题。
远程本地数据库环境：windows server 2008 R2 64bit; postgresql_9.3.4_win64(由postgresql_9.2.1_win64升级而来，为什么要upgrade以后再说)
本部数据中心srv1环境： windows server 2008 R2 64bit; postgresql_9.3.4_win64
本部数据中心srv2环境： windows server 2008 32bit; oracle_11.1.0.7.0_win32
目标： 实现远程pg与本部srv2 oracle关键业务的数据同步
一 远程pg操作
\c changkan postgres
\dx
create extension postgres_fdw;
\dx
create server srv_changsha foreign data wrapper postgres_fdw
options(host '***.***.***.***', port '***', dbname 'changkan');
grant usage on foreign server srv_changsha to jinchuan;
create user mapping for jinchuan server srv_changsha
options(user 'jinchuan', password '***');
create user mapping for postgres server srv_changsha
options(user 'jinchuan', password '***');
\c changkan jinchuan
drop foreign table ft_surveydata;
create foreign table ft_surveydata(
dataid numeric(10,0),
pointid numeric(5,0),
surveytypeid numeric(3,0),
sensorid numeric(5,0),
value1 numeric(15,5),
value2 numeric(15,5),
value3 numeric(15,5),
value4 numeric(15,5),
change1 numeric(15,5),
change2 numeric(15,5),
change3 numeric(15,5),
change4 numeric(15,5),
observedate timestamp without time zone,
speed1 numeric(15,5),
speed2 numeric(15,5),
speed3 numeric(15,5),
speed4 numeric(15,5),
submineid numeric(5,0))
server srv_changsha
options (schema_name 'jinchuan', table_name 'surveydata');
--insert into ft_surveydata(pointid,surveytypeid,sensorid,value1,value2,value3,value4,change1,change2,change3,change4,observedate,speed1,speed2,speed3,speed4,submineid) select pointid,surveytypeid,sensorid,value1,value2,value3,value4,change1,change2,change3,change4,observedate,speed1,speed2,speed3,speed4,submineid from surveydata where observedate >= '2013-08-06 10:00:00';
insert into ft_surveydata select * from surveydata where observedate > '2014-01-23 09:00:00';
insert into ft_surveydata(pointid,surveytypeid,sensorid,value1,value2,value3,value4,change1,change2,
change3,change4,observedate,speed1,speed2,speed3,speed4,submineid) select pointid,surveytypeid,sensorid,
value1,value2,value3,value4,change1,change2,change3,change4,current_timestamp,speed1,speed2,speed3,speed4,submineid
from surveydata where observedate >= '2013-08-06 10:00:00'
----------------------------------------------------------------------------
二 本部srv1 pg操作
\c changkan postgres
\dx
create extension oracle_fdw;
\dx
\q
tnsping jinchuan
$oracle_home\network\admin\tnsnames.ora
JINCHUAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv2)(PORT = ***))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = changkan)
)
)
create server oracle_srv foreign data wrapper oracle_fdw
options(dbserver 'JINCHUAN');
grant usage on foreign server oracle_srv to jinchuan;
create user mapping for jinchuan server oracle_srv
options (user 'JINCHUAN', password '***');
create user mapping for postgres server oracle_srv
options (user 'JINCHUAN', password '***');
\c changkan jinchuan
drop foreign table ft_surveydata;
create foreign table ft_surveydata(
dataid numeric(10,0),
pointid numeric(5,0),
surveytypeid numeric(3,0),
sensorid numeric(5,0),
value1 numeric(15,5),
value2 numeric(15,5),
value3 numeric(15,5),
value4 numeric(15,5),
change1 numeric(15,5),
change2 numeric(15,5),
change3 numeric(15,5),
change4 numeric(15,5),
observedate timestamp without time zone,
speed1 numeric(15,5),
speed2 numeric(15,5),
speed3 numeric(15,5),
speed4 numeric(15,5),
submineid numeric(5,0))
server oracle_srv
options (schema 'JINCHUAN', table 'SURVEYDATA');
------------------------------------------------------------
insert into ft_surveydata
(pointid,surveytypeid,sensorid,value1,value2,value3,value4,change1,change2,change3,change4,observedate,speed1,speed2,speed3,speed4,submineid)
select pointid,surveytypeid,sensorid,value1,value2,value3,value4,change1,change2,change3,change4,observedate,speed1,speed2,speed3,speed4,submineid
from surveydata where observedate > (current_date - 1);
三 使用window计划任务定时执行
1 远程pg端：psql -U postgres -d changkan -w -L fdw.log -c "insert into jinchuan.ft_surveydata select * from jinchuan.surveydata where observedate > (current_date - 0);"
2 本部pg端：psql -U postgres -d changkan -w -c "insert into jinchuan.ft_surveydata (surveytypeid,sensorid,value1,value2,value3,value4,change1,change2,change3,change4,observedate,speed1,speed2,speed3,speed4,submineid) select surveytypeid,sensorid,value1,value2,value3,value4,change1,change2,change3,change4,observedate,speed1,speed2,speed3,speed4,submineid from jinchuan.surveydata where observedate > (current_date - 0);"
本来是可以直接通过oracle_fdw实现远程pg与本部oracle数据同步的，这里使用了本部srv1的pg做了一下“中转”，原因是远程pg上没有安装oracle客户端，且在执行create extension oracle_fdw时报错。所以才搞出来这么一个乖乖的方案出来：
远程pg-------------------->本部srv1 pg------------------------>本部srv2 oracle
(postgres_fdw) (oracle_fdw)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25406498/viewspace-1154753/ ，如需转载，请注明出处，否则将追究法律责任。
