如何在postgresql中利用fdw實現不同數據庫之間數據互通

這篇文章給大家介紹如何在postgresql中利用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實現不同數據庫之間數據互通就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。