postgresql利用fdw来实现不同数据库之间数据互通-互联网品牌圈oem365

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

　项目招商找A5 快速获取精准代理名单

前言

最近在工作的时候需要用数据库A中的表a1来更新插入数据库B中的b2表由于数据库都是postgres数据库，我想到了 postgres_fdw插件。

准备工作

首先需要保证这两个数据库之间网是通的

数据库A 名字 AA ip 10.10.2.100（虚构）端口 5432 用户名 postgres

数据库B 名字 BB ip 100.20.0.120（虚构）端口 6688 用户名 postgres

一利用fdw创建2个数据库之间的连接

1.1 安装 postgres_fdw插件

1Create extension "postgres_fdw";

1.2 创建外部连接

无论你实在A数据库中创建B数据库的连接，还是在B数据库中创建A数据库的连接从本质上是一样的，都是为了打通2个数据库，实现数据互通

1.2.1 在A数据库创建b数据库的连接

--创建外部服务器-- 括号里的三个参数，分别是数据库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 '数据库密码');

1.2.2 在b数据库创建a数据库的连接

--创建外部服务器-- 括号里的三个参数，分别是数据库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 '数据库密码');

1.2.3 查询外部链接

1select * from pg_foreign_server;

1.3 创建外部表

在A数据库创建b1表的外部链接

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 '时序库的表名');

查看外部表是否有数，有数就表示ok了

1select * from tb_fdw_b1;

在数据库b上创建a1表的外部链接也是一样的

二插入数据

2.1 往外部表tb_fdw_b1 插入表a1的数据

因为俩个表都是一样的表结构，可以直接插入

1INSERT into tb_fdw_b1 SELECT * from a1;

因为外部表是实时的在数据库a中更改了外部表tb_fdw_b1 的数据，数据库B中的b1表中的数据也随之改变达成了我们一开始的目的

更新 UPDATE 删除DELETE 也是一样的在这里就不说了

三利用定时任务来达成自动同步

每天都需要执行显然不符合我们的“懒人”形象，所以用定时任务来实现明天自动同步是必不可少的我让他每天早上8点自动执行

3.1 利用 pgadmin实现定时同步（截图）

3.1.1

3.1.2

3.1.3

3.1.4

3.2 直接上语句实现定时同步

DO $$DECLAREjid integer;scid integer;BEGIN-- Creating a new jobINSERT 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 scheduleINSERT 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,-- MinutesARRAY[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[],-- HoursARRAY[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 daysARRAY[false,false,false,false,false,false,false]::boolean[],-- Month daysARRAY[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[],-- MonthsARRAY[false,false,false,false,false,false,false,false,false,false,false,false]::boolean[]) RETURNING jscid INTO scid;END$$;

文章来源：脚本之家

来源地址：https://www.jb51.net/article/205688.htm

上一篇: Linux上定时备份postgresql数据库的方法返回下一篇:详解PostgreSQL实现定时任务的4种方法