PostgreSQL-11.3-postgres-FDW-配置和体验

       node1                                  node2   
|--------------------|                 |-------------------|  
|        user=user1 --------------------> user=user2       |  
| local table:   t0  |                 |                   |  
| foreign table: t1 --------------------> local table: t1  |  
|                    |                 |                   |  
| foreign table: t2 --------------------> local table: t2  |   
|--------------------|                 |-------------------|

node2上创建数据库账号’user2'
node2上建立两个表: t1, t2

psql -h node2 -p 6432 -d postgres 
CREATE ROLE user2 WITH LOGIN PASSWORD 'pwd#@1';
GRANT ALL PRIVILEGES ON DATABASE postgres to user2;

node1的IP地址：192.168.199.110
host    postgres        user2           192.168.199.110/32      md5

psql -h node2 -p 6432 -d postgres -U user2 
create table t1 ( id int, name varchar(100));


create table t2 ( id int, name varchar(100));

node1: 创建数据库账号’user1'
node1: 建立一个本地表t0，两个外表: t1, t2，分别指向node2上的t1, t2

psql -h node1 -p 5432 -d postgres 
create extension postgres_fdw;
CREATE ROLE user1 WITH LOGIN PASSWORD '#@1qw';
GRANT ALL PRIVILEGES ON DATABASE postgres to user1;
grant usage on foreign data wrapper postgres_fdw to user1

psql -h node1 -p 5432 -d postgres -U user1

create table t0 ( id int, name varchar(100));

;;外表t1,t2
node1.t1 ---> node2.t1
node1.t2 ---> node2.t2
create server fnode2
         foreign data wrapper postgres_fdw         
         options (host 'node2', port '6432', dbname 'postgres'); 
create user mapping for user1
         server fnode2         
         options (user 'user2', password 'pwd#@1'); 
create foreign table t1 (
         id int,
         name varchar(100)
)
server fnode2
options (schema_name 'public', table_name 't1');


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

insert into t0 select generate_series(1,100), 'tom-'||generate_series(1,100) ;
insert into t1 select generate_series(1,100), 'tom-'||generate_series(1,100) ;
insert into t2 select generate_series(1,100), 'tom-'||generate_series(1,100) ;

id            name
----------------------
1             tom-1
2             tom-2
3             tom-3
...            ...

psql -h node1 -U user1 -d postgres -c "select * from t1 where id=1;"

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR SELECT id, name FROM public.t1 WHERE ((id = 1));
FETCH 100 FROM c1;CLOSE c1;COMMIT TRANSACTION;

psql -h node1 -U user1 -d postgres -c "select * from t0,t1 where t0.id=1 and t0.name=t1.name;"

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR SELECT id, name FROM public.t1;
FETCH 100 FROM c1;
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;

psql -h node1 -U user1 -d postgres -c "select * from t0,t1 where t1.id=1 and t0.name=t1.name;"

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;


DECLARE c1 CURSOR FOR
        SELECT id, name FROM public.t1 WHERE ((id = 1));
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;

psql -h node1 -U user1 -d postgres -c "select * from t1,t2 where t1.id=1 and t1.name=t2.name;"

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR
        SELECT r1.id, r1.name, r2.id, r2.name FROM (public.t1 r1 INNER JOIN public.t2 r2 ON (((r1.name = r2.name)) AND ((r1.id = 1))));
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;

psql -h node1 -U user1 -d postgres -c "select avg(id) from t1 where id<100;"

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR
        SELECT avg(id) FROM public.t1 WHERE ((id < 100));
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;

psql -h node1 -U user1 -d postgres -c "select * from t1 where id<100 order by name;"

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR
        SELECT id, name FROM public.t1 WHERE ((id < 100)) ORDER BY name ASC NULLS LAST;
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;