PostgreSQL外部表postgres_fdw

作者：瀚高PG实验室 （Highgo PG Lab）- 徐云鹤
本文简单探讨下PostgreSQL外部表postgres_fdw。
添加extension。

highgo=# create extension postgres_fdw ;           
CREATE EXTENSION
highgo=# \dx
                               List of installed extensions
     Name     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(2 rows)

创建远端数据库。

highgo=# create database dblink TEMPLATE template0;
CREATE DATABASE

本地创建server。

highgo=# select * from pg_foreign_data_wrapper;
   fdwname    | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
--------------+----------+------------+--------------+--------+------------
 postgres_fdw |       10 |      24582 |        24583 |        |
(1 row)
highgo=# create server server_remote_dblink foreign data wrapper postgres_fdw options(host '127.0.0.1',port '5866',dbname 'highgo');
CREATE SERVER
highgo=#  select * from pg_foreign_server ;
       srvname        | srvowner | srvfdw | srvtype | srvversion | srvacl |                srvoptions               
----------------------+----------+--------+---------+------------+--------+------------------------------------------
 server_remote_dblink |       10 |  24584 |         |            |        | {host=127.0.0.1,port=5866,dbname=highgo}
(1 row)

highgo=# \des
               List of foreign servers
         Name         | Owner  | Foreign-data wrapper
----------------------+--------+----------------------
 server_remote_dblink | highgo | postgres_fdw
(1 row)

创建用户匹配信息

highgo=# create user mapping for highgo server server_remote_dblink options(user 'highgo',password 'highgo');
CREATE USER MAPPING
highgo=#  select * from pg_user_mappings;
 umid  | srvid |       srvname        | umuser | usename |            umoptions             
-------+-------+----------------------+--------+---------+----------------------------------
 24592 | 24591 | server_remote_dblink |     10 | highgo  | {user=highgo,password=highgo123}
(1 row)

创建外部表。

highgo=# CREATE FOREIGN TABLE bb(name char(2)) server server_remote_dblink options (schema_name 'public',table_name 'aa');
CREATE FOREIGN TABLE
highgo=# select * from bb;
 name
------
(0 rows)

在dblink库里插一条数据后再次查询。

highgo=# select * from bb;
  name 
--------
 信息
(1 row)

查看类型。

highgo=# \d
               List of relations
     Schema     | Name |     Type      | Owner 
----------------+------+---------------+--------
 oracle_catalog | dual | view          | highgo
 public         | a    | table         | highgo
 public         | bb   | foreign table | highgo
(3 rows)

删除外部表。

highgo=# DROP FOREIGN TABLE bb;
DROP FOREIGN TABLE

导入整个schema下的所有表。

highgo=# grant all on foreign server server_remote_dblink to highgo;
GRANT

highgo=# IMPORT FOREIGN SCHEMA public FROM SERVER server_remote_dblink into public;
IMPORT FOREIGN SCHEMA
highgo=# \d
               List of relations
     Schema     | Name |     Type      | Owner 
----------------+------+---------------+--------
 oracle_catalog | dual | view          | highgo
 public         | a    | table         | highgo
 public         | aa   | foreign table | highgo
(3 rows)

如果远端的表有drop或者create,在本地不会同步更新。
所以如果远端删除了表，再通过外部表查询，会提示不存在。