Postgres-FDW部署及配置

IT那活兒發(fā)布于2023-01-11 13:20 / 1728人閱讀

Postgres-FDW部署及配置

概述

fdw是foreign-data wrapper的一個簡稱，可以叫外部封裝數(shù)據(jù)。

postgres_fdw實現(xiàn)的是各個postgresql數(shù)據(jù)庫及遠(yuǎn)程數(shù)據(jù)庫之間的跨庫操作，同時PG也支持與其它數(shù)據(jù)庫進行外表查詢，如使用oracle_FDW可與Oracle數(shù)據(jù)庫進行數(shù)據(jù)訪問，本文介紹postgres-FDW部署及配置實現(xiàn)兩個postgres數(shù)據(jù)庫之間進行數(shù)據(jù)訪問。

拓?fù)浣Y(jié)構(gòu)

服務(wù)器配置：

主機 ip 端口

Pg1 192.168.18.11 15432

Pg2 192.168.18.12 15432

數(shù)據(jù)庫配置：

1. 配置pg2

pg2上創(chuàng)建數(shù)據(jù)庫賬號pg_fw2

pg2上建立兩個表: t1, t2

1. 使用超級用戶登錄pg2，在pg2上創(chuàng)建數(shù)據(jù)庫賬號pg_fw2

psql -h pg2 -p 15432 -d postgres
CREATE ROLE pg_fw2 WITH LOGIN PASSWORD pg_fw2;
GRANT ALL PRIVILEGES ON DATABASE postgres to pg_fw2;

2. 更改pg2:pg_hba.conf，允許pg1使用賬號pg_fw2訪問pg2，必須使用md5認(rèn)證方式

host   postgres   pg_fw2   pg1   md5

3. 在pg2上，以pg_fw2身份，創(chuàng)建表: t1, t2

psql -h pg2 -p 15432 -d postgres -U pg_fw2
create table t1 ( id int, name varchar(20));
create table t2 ( id int, name varchar(20));

2. 配置pg1

pg1: 創(chuàng)建數(shù)據(jù)庫賬號’pg_fw1’

pg1: 建立一個本地表t0，兩個外表: fw_t1,fw_t2，分別指向pg2上的t1, t2

1. postgres-fdw插件安裝

使用超級用戶，在pg1上安裝postgres-fdw擴展, 創(chuàng)建數(shù)據(jù)庫賬號pg_fw1

psql -h pg1 -p 15432 -d postgres
create extension postgres_fdw;

2. pg1上創(chuàng)建數(shù)據(jù)庫賬號pg_fw1

CREATE ROLE pg_fw1 WITH LOGIN PASSWORD pg_fw1;
GRANT ALL PRIVILEGES ON DATABASE postgres to pg_fw1;
grant usage on foreign data wrapper postgres_fdw to pg_fw1 ; --注意需要多帶帶授權(quán)

3. pg1上,創(chuàng)建本地表: t0

psql -h pg1 -p 15432 -d postgres -U pg_fw1
create table t0 ( id int, name varchar(20));

4. pg1上,創(chuàng)建FDW服務(wù)、用戶映射、外部表：

--外表t1,t2對應(yīng)關(guān)系

pg1.fw_t1 ---> pg2.t1

pg1.fw_t2 ---> pg2.t2

創(chuàng)建fdw服務(wù)器

▼▼▼
create server fw_pg2
         foreign data wrapper postgres_fdw 
         options (host 192.168.18.12, port 15432, dbname postgres);

創(chuàng)建用戶映射

▼▼▼
create user mapping for pg_fw1
         server fpg2 
         options (user pg_fw2, password pg_fw22021);

創(chuàng)建外部表fw_t1

▼▼▼
create foreign table fw_t1 (
         id int,
         name varchar(100)
)
server fw_pg2
options (schema_name public, table_name t1);

創(chuàng)建外部表fw_t2

▼▼▼
create foreign table fw_t2 (
         id int,
         name varchar(100)
)
server fw_pg2
options (schema_name public, table_name t2);

postgres=> d fw_t2
                           Foreign table "public.fw_t2"
 Column | Type | Collation | Nullable | Default | FDW options 
--------+------------------------+-----------+----------+---------+-------------
 id | integer | | | | 
 name | character varying(100) | | | | 
Server: fpg2
FDW options: (schema_name public, table_name t2)