PostgreSQL外部表postgres

PostgreSQL外部表postgres"/>

PostgreSQL外部表postgres

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

highgo=# create extension postgres_fdw ;           
CREATE EXTENSION
highgo=# \dxList of installed extensionsName     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural languagepostgres_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=# \desList of foreign serversName         | 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=# \dList of relationsSchema     | Name |     Type      | Owner 
----------------+------+---------------+--------oracle_catalog | dual | view          | highgopublic         | a    | table         | highgopublic         | 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;
GRANThighgo=# IMPORT FOREIGN SCHEMA public FROM SERVER server_remote_dblink into public;
IMPORT FOREIGN SCHEMA
highgo=# \dList of relationsSchema     | Name |     Type      | Owner 
----------------+------+---------------+--------oracle_catalog | dual | view          | highgopublic         | a    | table         | highgopublic         | aa   | foreign table | highgo
(3 rows)

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