postgres_fdw執行個體

環境

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)