postgresql_fdw 跨数据库查询

--PostgreSQL的数据库逻辑上是相互独立的，和Oracle类似，如果要访问其他数据库，需要做跨库操作，

--Postgres本身提供了一些扩展，比如dblink,pgsql_fdw等，高版本的建议使用postgres_fdw，也就是pgsql_fdw的升级版。 

远端数据准备

postgres=# show search_path;

 search_path 

-------------

 schema_fdw

(1 row)

postgres=# create table tbl_kenyon (id int,remark text);

CREATE TABLE

postgres=# insert into tbl_kenyon select generate_series(1,100),'Kenyon Go!';

INSERT 0 100

二、安装使用 

 安装分4步走 

1.本地安装extension 

--安装的扩展名是来自于share/extension/*.control中的文件名*，比如postgres_fdw.control

postgres=# create extension postgres_fdw;

CREATE EXTENSION

postgres=# select * from pg_extension ;

 extname     | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition   -------------------+----------+--------------+----------------+------------+-----------+

 plpgsql            |       10 |           11 | f              | 1.0        |           | 

 pg_stat_statements |       10 |         2200 | t              | 1.1        |           | 

 postgres_fdw       |       10 |         2200 | t              | 1.0        |           | 

(3 rows)

postgres=# select * from pg_foreign_data_wrapper;

  fdwname    | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions 

  ------------+----------+------------+--------------+--------+------------

 postgres_fdw |       10 |     154356 |       154357 |        | 

(1 row)

或

postgres=# \dx

                                     List of installed extensions

      Name        | Version |  Schema |                        Description                     -----------------+---------+------------+-----------------------------------------------------

 pg_stat_statements | 1.1     | public |track execution statistics of all SQL statements executed

 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language

 postgres_fdw       | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers

(3 rows)

2.本地创建server并查看 

该server作用是在本地配置一个连接远程的信息，下面的配置是要连接到远程DB名称是postgres数据库

postgres=# create server server_remote_rudy_01 foreign data wrapper postgres_fdw options(host 'rudy_01',port '5432',dbname 'postgres');

CREATE SERVER

postgres=# select * from pg_foreign_server ;

     srvname      | srvowner | srvfdw | srvtype | srvversion | srvacl |   srvoptions  

--------------+----------+--------+---------+------------+--------+--------------------------

server_remote_rudy_01 |       10 | 154358 |         |     |{host=10.1.11.71,port=5432,dbname=postgres}

(1 row)

或者

postgres=# \des

              List of foreign servers

       Name       |  Owner   | Foreign-data wrapper 

------------------+----------+----------------------

 server_remote_rudy_01 | postgres | postgres_fdw

(1 row)

3.创建用户匹配信息并查看,在本地

--for后面的postgres是本地登录执行的用户名,option里存储的是远程的用户密码

postgres=# create user mapping for postgres server server_remote_rudy_01 options(user 'postgres',password '123456');

CREATE USER MAPPING

postgres=# select * from pg_user_mappings;

  umid  | srvid  |     srvname      | umuser | usename  |             umoptions             

--------+--------+------------------+--------+----------+-----------------------------------

 154360 | 154359 | server_remote_rudy_01 |     10 | postgres | {user=usr_pg_fdw,password=123456}

(1 row)

或

postgres=# \deu+

                          List of user mappings

      Server      | User name |               FDW Options                

------------------+-----------+------------------------------------------

 server_remote_rudy_01 | postgres  | ("user" 'usr_pg_fdw', password '123456')

(1 row)

4.本地创建外部表，指定server

postgres=#  CREATE FOREIGN TABLE test1(id int,remark text) server server_remote_rudy_01 options (schema_name 'public',table_name 'tbl_kenyon');

CREATE FOREIGN TABLE

--导入整个schem下面的表

create schema test;

--视用户不同授予不同的权限

grant all on schema test to rudy_02;

grant all on foreign data wrapper postgres_fdw to rudy_02;                     

grant all on foreign server server_remote_rudy_01 to rudy_02;    

--导入指定的表，也可以不导入指定的表，也可以导入整个schema下面的表        

IMPORT FOREIGN SCHEMA public FROM SERVER server_remote_rudy_01 INTO test;

IMPORT FOREIGN SCHEMA public limit to(t1) FROM SERVER server_remote_rudy_01 INTO test;

--通过使用\d查看表，注意不通过使用\dt查看表，而且如果远端的表有drop或者create,在本地会察觉不到

\d test.

postgres=> select * from test.t1 ;          

ERROR:  relation "test.t1" does not exist

--查看系统中有哪些外部表，可查看如下的sql

select * from pg_foreign_table;

--注意如果要删除外部表，千万千万不要直接删除pg_foreign_table表中的数据

--delete from pg_foreign_table ;

--否则不能再导入IMPORT FOREIGN 删除的表，也不能执行 select froeign table 查询删除的表，也不能drop server,都会报如下的错

ERROR:  cache lookup failed for foreign table 49251

--再插入删除的数据

postgres=# \d+ pg_foreign_table;

                  Table "pg_catalog.pg_foreign_table"

  Column   |  Type  | Modifiers | Storage  | Stats target | Description 

-----------+--------+-----------+----------+--------------+-------------

 ftrelid   | oid    | not null  | plain    |              |  在本地pg_class表中对应的 relfilenode

 ftserver  | oid    | not null  | plain    |              |  在本的pg_foreign_server表的oid

 ftoptions | text[] |           | extended |              |  对应foreign server的schema和table名字

postgres=> insert into pg_foreign_table values(49251,49247,array['schema_name=public','table_name=postgres']); 

--而后再删除外部表

postgres=> drop foreign table test.postgres_log ;

DROP FOREIGN TABLE

-- postgres_fdw 为了性能其会把where查询发送到远端

postgres_fdw attempts to optimize remote queries to reduce the amount of data transferred from foreign servers

The query that is actually sent to the remote server for execution can be examined using EXPLAIN VERBOSE