postgres_fdw实例

原创

岳麓丹枫 2022-07-12 11:58:54 博主文章分类：PostgreSQL ©著作权

文章标签 postgresql sql 数据库表数据数据查询 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者岳麓丹枫的原创作品，请联系作者获取转载授权，否则将追究法律责任

环境

node1: 207.207.35.134
node2: 207.207.35.99

node2:

添加数据

create table test(id int);
create table person(id int, name varchar);
insert into test select n from generate_series(1,10) n;
insert into person select n , n||'name' from generate_series(1,10) n;

node1

create server / user mappping/ foreign table (外表)

-- create server / user mappping/ foreign table 

create server fnode2
         foreign data wrapper postgres_fdw
         options (host '207.207.35.99', port '5432', dbname 'test');
 
create user mapping for postgres
         server fnode2
         options (user 'postgres', password 'passwd'); 
create foreign table test(
         id int
)
server fnode2
options (schema_name 'public', table_name 'test');


create foreign table person (
         id int,
         name varchar 
)
server fnode2
options (schema_name 'public', table_name 'person');

外表数据查询

test=# select * from test;
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=# select * from person;
 id |  name  
----+--------
  1 | 1name
  2 | 2name
  3 | 3name
  4 | 4name
  5 | 5name
  6 | 6name
  7 | 7name
  8 | 8name
  9 | 9name
 10 | 10name
(10 rows)

外表数据更新

test=# update person set name='name10' where id=10;
UPDATE 1
test=# select * from person;
 id |  name  
----+--------
  1 | 1name
  2 | 2name
  3 | 3name
  4 | 4name
  5 | 5name
  6 | 6name
  7 | 7name
 8 | 8name
  9 | 9name
 10 | name10
(10 rows)

外表数据删除

test=# truncate table person;
ERROR:  "person" is not a table
test=# rollback;
ROLLBACK
test=# 
test=# truncate person;
ERROR:  "person" is not a table
test=# 
test=# begin;
BEGIN
test=# delete from person;
DELETE 10
test=# select * from person;
 id | name 
----+------
(0 rows)

test=# rollback;
ROLLBACK
test=# select * from person;
 id |  name  
----+--------
  1 | 1name
  2 | 2name
  3 | 3name
  4 | 4name
  5 | 5name
  6 | 6name
  7 | 7name
  8 | 8name
  9 | 9name
 10 | name10
(10 rows)

查看当前数据库中有哪些外表

test=# select * from pg_foreign_table;
 ftrelid | ftserver |               ftoptions                
---------+----------+----------------------------------------
   51527 |    51525 | {schema_name=public,table_name=test}
   51530 |    51525 | {schema_name=public,table_name=person}
(2 rows)