PostgreSQL-11.3-FDW-配置笔记

1 实验环境

• 两个数据库节点：node1, node2，使用postgresql 11.3
• node1是主节点，供客户端(psql)访问；node2是外节点，由node1直接访问。

1.1 node1

• hostname=node1
• port=5432
• 应用程序(psql)访问该节点
• table:'test1'，外表，指向node2的表’test2‘
• user: 'user1'

1.2 node2

• hostname=node2
• port=5432
• node1访问该节点
• table:'test2',本地表
• user: 'user2'

2 配置node2

psql -h node2 -d postgres

2.1 创建表，添加数据

• 创建表'test2'，用于node1访问

create table test2( id int, name varchar(100));
insert into test2 values (generate_series(1,10), md5(random()::text));

2.2 创建用户，授予权限

create role user2 with login password 'ks839#@s';

grant select, insert, update, delete  on test2 to user2;

3 配置node1

psql -h node1 -d postgres

3.1 创建账号

create role user1 with login password '56ak!@#$';

3.2 创建fdw扩展

create extension postgres_fdw;

3.3 创建外表，授予权限

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 'ks839#@s');

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

grant select, insert, update, delete  on test1 to user1;

4 在node1上对外表执行读写

psql -h node1 -Uuser1 -d postgres

select * from test1;
insert into test1 values (100,'100');
select * from test1 where id=100;
update test1 set id=200 where id=100;
select * from test1 where id=200;
delete from test1 where id=200;