postgresql12-create postgres_fdw

1. 目的
   在node2中查询node1的t1表

select * from t1;
 id | name 
----+------
  1 | aa

2. 实施
   node1数据准备

[postgres@localhost ~]$ psql
Password for user postgres: 
psql (12.4)
Type "help" for help.
postgres=# create database db_1;
postgres=# create user user1 password 'Aa111';
postgres=# alter database db_1 owner to user1;
db_1=# \q
[postgres@localhost ~]$ psql -U user1 -d db_1
Password for user user1: 
psql (12.4)
Type "help" for help.
db_1=> create table t1(id int,name varchar) tablespace pg_default;
db_1=> insert into t1 values(1,'aa');
db_1=> select * from t1;
 id | name 
----+------
  1 | aa
(1 row)

node2设置

-bash-4.1$ psql
psql (12.3)
Type "help" for help.

postgres=# create user user2 password 'Aa111';
postgres=# create database db_2;
postgres=# alter database db_2 owner to user2;

postgres=# \c db_2
You are now connected to database "db_2" as user "postgres".
db_2=# create extension postgres_fdw;
db_2=# grant usage on foreign data wrapper postgres_fdw to user2;
db_2=# create server fnode1 foreign data wrapper postgres_fdw options (host 'node1', port '5432', dbname 'db_1');
db_2=# grant usage on foreign server fnode1 to user2; 

-bash-4.1$ psql -U user2 -d db_2 -h node2
Password for user user2: 
psql (12.3)
Type "help" for help.

db_2=> # create user mapping for user2 server fnode1 options (user 'user1', password 'Aa111');
db_2=> # create foreign table t1 (id int,name varchar) server fnode1 options (schema_name 'public', table_name 't1');
db_2=> select * from t1;
 id | name 
----+------
  1 | aa
(1 row)

3. 测试

db_2=> insert into t1 values(2,'bb');
INSERT 0 1
db_2=> select  * from t1;
 id | name 
----+------
  1 | aa
  2 | bb
(2 rows)

db_2=> create table t2 (id int);
CREATE TABLE
db_2=> insert into t2 values(2),(3);
INSERT 0 2
db_2=> select * from t1,t2 where t1.id=t2.id;
 id | name | id 
----+------+----
db_2=> explain select * from t1,t2 where t1.id=t2.id;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Merge Join  (cost=401.81..669.70 rows=17404 width=40)
   Merge Cond: (t1.id = t2.id)
   ->  Sort  (cost=222.03..225.44 rows=1365 width=36)
         Sort Key: t1.id
         ->  Foreign Scan on t1  (cost=100.00..150.95 rows=1365 width=36)
   ->  Sort  (cost=179.78..186.16 rows=2550 width=4)
         Sort Key: t2.id
         ->  Seq Scan on t2  (cost=0.00..35.50 rows=2550 width=4)
(8 rows)

4. 扩展
   9.4版本之前的版本是不支持 postgres_fdw，可以使用dblink

-bash-4.1$ psql
psql (12.3)
Type "help" for help.

postgres=# \c db_2
You are now connected to database "db_2" as user "postgres".
db_2=# create extension dblink;
CREATE EXTENSION
db_2=# select * from dblink('host=node1 port=5432 dbname=db_1 user=user1 password=Aa111','select id,name from t1')t (id int,name varchar);
 id | name 
----+------
  1 | aa
  2 | bb
(2 rows)

参考：
https://www.modb.pro/db/23622
https://www.postgresql.org/docs/12/postgres-fdw.html
https://www.postgresql.org/docs/9.2/dblink.html