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

 更新时间:2021年2月9日 12:16  点击:2036

前言
最近在工作的时候 需要用数据库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插件

Create 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 查询 外部链接

select * 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了

select * from tb_fdw_b1;

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

二 插入数据

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

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

INSERT 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 $$
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
$$;

到此这篇关于postgresql 利用fdw来实现不同数据库之间数据互通的文章就介绍到这了,更多相关postgresql实现数据互通内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!

相关文章

  • PostgreSQL判断字符串是否包含目标字符串的多种方法

    这篇文章主要介绍了PostgreSQL判断字符串是否包含目标字符串的多种方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-02-23
  • postgresql 实现多表关联删除

    这篇文章主要介绍了postgresql 实现多表关联删除操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-02
  • postgresql数据添加两个字段联合唯一的操作

    这篇文章主要介绍了postgresql数据添加两个字段联合唯一的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
  • PostgreSQL TIMESTAMP类型 时间戳操作

    这篇文章主要介绍了PostgreSQL TIMESTAMP类型 时间戳操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-26
  • postgresql的jsonb数据查询和修改的方法

    这篇文章主要介绍了postgresql的jsonb数据查询和修改的方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-03
  • PostgreSQL 中的单引号与双引号用法说明

    这篇文章主要介绍了PostgreSQL 中的单引号与双引号用法说明,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-01
  • postgresql 补齐空值、自定义查询字段并赋值操作

    这篇文章主要介绍了postgresql 补齐空值、自定义查询字段并赋值操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
  • PostgreSQL之分区表(partitioning)

    通过合理的设计,可以将选择一定的规则,将大表切分多个不重不漏的子表,这就是传说中的partitioning。比如,我们可以按时间切分,每天一张子表,比如我们可以按照某其他字段分割,总之了就是化整为零,提高查询的效能...2020-07-11
  • Postgresql数据库之创建和修改序列的操作

    这篇文章主要介绍了Postgresql数据库之创建和修改序列的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
  • 基于Postgresql 事务的提交与回滚解析

    这篇文章主要介绍了基于Postgresql 事务的提交与回滚解析,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18
  • postgresql 实现查询出的数据为空,则设为0的操作

    这篇文章主要介绍了postgresql 实现查询出的数据为空,则设为0的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
  • 解决postgresql 自增id作为key重复的问题

    这篇文章主要介绍了解决postgresql 自增id作为key重复的问题,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
  • python 插入Null值数据到Postgresql的操作

    这篇文章主要介绍了python 插入Null值数据到Postgresql的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-03-06
  • PostgreSQL实现交叉表(行列转换)的5种方法示例

    这篇文章主要给大家介绍了关于PostgreSQL实现交叉表(行列转换)的5种方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-07-11
  • PostgreSQL 实现定时job执行(pgAgent)

    这篇文章主要介绍了PostgreSQL 实现定时job执行(pgAgent),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
  • PostgreSQL数据库事务出现未知状态的处理方法

    这篇文章主要给大家介绍了PostgreSQL数据库事务出现未知状态的处理方法,需要的朋友可以参考下...2020-07-11
  • PostgreSQL实现批量插入、更新与合并操作的方法

    这篇文章主要给大家介绍了关于PostgreSQL实现批量插入、更新与合并操作的相关资料,文中通过图文以及示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧...2020-07-11
  • Postgresql主从异步流复制方案的深入探究

    这篇文章主要给大家介绍了关于Postgresql主从异步流复制方案的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Postgresql具有一起的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-07-11
  • postgresql 利用xlog进行热备操作

    这篇文章主要介绍了postgresql 利用xlog进行热备操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-16
  • Postgresql 解决pg掉电后无法重启的问题

    这篇文章主要介绍了Postgresql 解决pg掉电后无法重启的问题,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18