postgresql 利用fdw来实现不同数据库之间数据互通(推荐)

create extension "postgres_fdw" ;

--创建外部服务器

-- 括号里的三个参数，分别是数据库b的ip、端口和数据库名称

create server lianjiebb foreign data wrapper postgres_fdw options (host '100.20.0.120' , port '6688' , dbname 'bb' );

--创建用户映射·

-- 括号里的两个参数，分别是数据库b数据库的用户名、密码

create user mapping for postgres server lianjiebb options( user 'postgres' , password '数据库密码' );

--创建外部服务器

-- 括号里的三个参数，分别是数据库a的ip、端口和数据库名称

create server lianjieaa foreign data wrapper postgres_fdw options (host '10.10.2.100' , port '5432' , dbname 'aa' );

--创建用户映射·

-- 括号里的两个参数，分别是数据库a数据库的用户名、密码

create user mapping for postgres server lianjieaa options( user 'postgres' , password '数据库密码' );

select * from pg_foreign_server;

create foreign table tb_fdw_b1

  (

  collect_time timestamp (6),

  id varchar (36) ,

  列名1 numeric (12,2) ,

  列名2 int4 ,

列名3 timestamp (6)

  ) --跟b1 一模一样的表结构就行

  server lianjiebb --你创建的外部链接名字

  options (table_name '时序库的表名' );

select * from tb_fdw_b1;

insert into tb_fdw_b1 select * from a1;

do $$

declare

  jid integer ;

  scid integer ;

begin

-- creating a new job

insert into pgagent.pga_job(

  jobjclid, jobname, jobdesc, jobhostagent, jobenabled

) values (

  1:: integer , '更新表b1' ::text, '' ::text, '' ::text, true

) returning jobid into jid;

-- steps

-- inserting a step (jobid: null)

insert into pgagent.pga_jobstep (

  jstjobid, jstname, jstenabled, jstkind,

  jstconnstr, jstdbname, jstonerror,

  jstcode, jstdesc

) values (

  jid, '更新表b1' ::text, true , 's' :: character (1),

  '' ::text, '数据库a' :: name , 'f' :: character (1),

  ' insert into tb_fdw_b1 select * from a1;' ::text, '' ::text

) ;

-- schedules

-- inserting a schedule

insert into pgagent.pga_schedule(

  jscjobid, jscname, jscdesc, jscenabled,

  jscstart, jscend, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths

) values (

  jid, '更新表b1' ::text, '' ::text, true ,

  '2021-02-07 10:24:50 +08:00' :: timestamp with time zone, '2100-02-07 10:24:52 +08:00' :: timestamp with time zone,

  -- minutes

  array[ true , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false ]::boolean[],

  -- hours

  array[ false , false , false , false , false , false , false , false , true , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false ]::boolean[],

  -- week days

  array[ false , false , false , false , false , false , false ]::boolean[],

  -- month days

  array[ false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false , false ]::boolean[],

  -- months

  array[ false , false , false , false , false , false , false , false , false , false , false , false ]::boolean[]

) returning jscid into scid;

end

$$;