PostgreSQL如何通过postgres_fdw实现跨库查询

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)

postgres=# 

postgres=# create user t1 login password 't1';
CREATE ROLE
postgres=# create database  t1 owner t1;
CREATE DATABASE
t2=> \c t1 t1
You are now connected to database "t1" as user "t1".
t1=> create schema t1;
CREATE SCHEMA
t1=> create table emp(id int,name varchar(10));
CREATE TABLE
t1=> insert into emp values(1,'jack');
INSERT 0 1
t1=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 t1     | emp  | table | t1
(1 row)
​
t1=> select * from emp ;
 id | name 
----+------
  1 | jack
(1 row)
​
t1=> 

postgres=# create user t2 login password 't2';
CREATE ROLE
postgres=# create database t2 owner t2;
CREATE DATABASE
postgres=# \c t2 t2;
You are now connected to database "t2" as user "t2".
t2=> create schema t2;
CREATE SCHEMA        
t2=> create table dept(id int,name varchar(10),emp_id int);
CREATE TABLE
t2=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 t2     | dept | table | t2
(1 row)
t2=> insert into dept values(1,'sales',1);
INSERT 0 1
t2=> select * from dept ;
 id | name  | emp_id 
----+-------+--------
  1 | sales |      1
(1 row)
​
t2=> 

postgres=# \c t1
You are now connected to database "t1" as user "pg12".
t1=# create extension postgres_fdw;
CREATE EXTENSION
t1=# 

t1=# \c
You are now connected to database "t1" as user "pg12".
t1=# CREATE SERVER foreign_server1                                                              
t1-#         FOREIGN DATA WRAPPER postgres_fdw  
t1-#         OPTIONS (host '172.16.11.35', port '5412', dbname 't2');  
CREATE SERVER
t1=# grant USAGE on FOREIGN server foreign_server1 to t1;
GRANT
t1=# 

t1=# \c
You are now connected to database "t1" as user "pg12".
t1=# CREATE USER MAPPING FOR t1      
t1-#         SERVER foreign_server1  
t1-#         OPTIONS (user 't2', password 't2'); 
CREATE USER MAPPING
t1=>

t1=# \c t1 t1
You are now connected to database "t1" as user "t1".
t1=> CREATE FOREIGN TABLE dept (id int,name varchar(10),emp_id int) SERVER foreign_server1
        OPTIONS (schema_name 't2', table_name 'dept');
CREATE FOREIGN TABLE
t1=> \d 
           List of relations
 Schema | Name |     Type      | Owner 
--------+------+---------------+-------
 t1     | dept | foreign table | t1
 t1     | emp  | table         | t1
(2 rows)
​
t1=>

t1=# \c t1 t1
You are now connected to database "t1" as user "t1".
t1=> \d 
           List of relations
 Schema | Name |     Type      | Owner 
--------+------+---------------+-------
 t1     | dept | foreign table | t1
 t1     | emp  | table         | t1
(2 rows)
​
t1=> select e.id emp_id,e.name,d.id dept_id,d.name dept_name from emp e, dept d where e.id=d.emp_id;
 emp_id | name | dept_id | dept_name 
--------+------+---------+-----------
      1 | jack |       1 | sales
(1 rows)
​
t1=> 

t1=> \c t2 t2
You are now connected to database "t2" as user "t2".
t2=> insert into dept values(2,'dev','1');
INSERT 0 1
t2=> \c t1 t1
You are now connected to database "t1" as user "t1".
t1=> select e.id emp_id,e.name,d.id dept_id,d.name dept_name from emp e, dept d where e.id=d.emp_id;
 emp_id | name | dept_id | dept_name 
--------+------+---------+-----------
      1 | jack |       1 | sales
      1 | jack |       2 | dev
(2 rows)
​
t1=>

postgres=# \c t1 t1
You are now connected to database "t1" as user "t1".
t1=> \d
           List of relations
 Schema | Name |     Type      | Owner 
--------+------+---------------+-------
 t1     | dept | foreign table | t1
 t1     | emp  | table         | t1
(2 rows)
​
t1=> select * from dept ;
 id | name  | emp_id 
----+-------+--------
  1 | sales |      1
  2 | dev   |      1
(2 rows)
​
t1=> delete from dept where id=2;
DELETE 1
t1=> \c t2 t2
You are now connected to database "t2" as user "t2".
t2=> select * from dept ;
 id | name  | emp_id 
----+-------+--------
  1 | sales |      1
(1 row)
​
t2=> 

postgres=# drop database t1;
DROP DATABASE
postgres=# drop database t2;
DROP DATABASE
postgres=# drop user t1;
DROP ROLE
postgres=# drop user t2;
DROP ROLE
postgres=# 

​
--1超管用户初始化t1库、t1用户，以及初始化数据
\c postgres pg12
create user t1 login password 't1';
create database  t1 owner t1;
CREATE DATABASE
\c t1 t1
create schema t1;
create table emp(id int,name varchar(10));
insert into emp values(1,'jack');
select * from emp ;
​
--2超管用户初始化t2库、t2用户，以及初始化数据
\c postgres pg12
create user t2 login password 't2';
create database t2 owner t2;
\c t2 t2;
create schema t2;
create table dept(id int,name varchar(10),emp_id int);
insert into dept values(1,'sales',1);
select * from dept ;
​
--3 超级用户在t1库上创建postgres_fdw extension、foreign server并授权usage给t1用户、创建USER MAPPING、
\c t1 pg12
create extension postgres_fdw;
CREATE SERVER foreign_server1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '172.16.11.35', port '5412', dbname 't2');  
grant USAGE on FOREIGN server foreign_server1 to t1;
CREATE USER MAPPING FOR t1 SERVER foreign_server1  OPTIONS (user 't2', password 't2'); 
​
--4 t1用户在t1库上创建和使用外部表
\c t1 t1
CREATE FOREIGN TABLE dept (id int,name varchar(10),emp_id int) SERVER foreign_server1 OPTIONS (schema_name 't2', table_name 'dept');
select e.id emp_id,e.name,d.id dept_id,d.name dept_name from emp e, dept d where e.id=d.emp_id;
​
--5 超管用户清除测试库、用户
drop database t1;
drop database t2;
drop user t1;
drop user t2;