postgresql 中的postgres_fdw

最新推荐文章于 2025-06-10 13:16:25 发布

奈何流年

最新推荐文章于 2025-06-10 13:16:25 发布

阅读量1.6k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： postgresql

本文链接：https://blog.youkuaiyun.com/silenceray/article/details/58599642

postgresql 专栏收录该内容

61 篇文章

订阅专栏

本文介绍了如何在PostgreSQL中配置远程数据访问，包括创建服务器、用户映射及外键表等步骤，实现从一台PostgreSQL服务器访问另一台服务器的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

两台主机，192.168.11.67
                192.168.11.70
67机器上的
[postgres@pg contrib]$ psql
psql (9.4.4)
Type "help" for help.

postgres=# select * from pg_foreign_data_wrapper;
   fdwname    | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions 
--------------+----------+------------+--------------+--------+------------
 dblink_fdw   |       10 |          0 |        16965 |        | 
 postgres_fdw |       10 |      16968 |        16969 |        | 
(2 rows)

postgres=# \dx
                                    List of installed extensions
     Name     | Version |   Schema   |                         Description                          
--------------+---------+------------+--------------------------------------------------------------
 dblink       | 1.1     | public     | connect to other PostgreSQL databases from within a database
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(3 rows)

postgres=# create server server_remote_70 foreign data wrapper postgres_fdw options(host '192.168.11.70',port '5432',dbname 'postgres');
CREATE SERVER
postgres=# select * from pg_foreign_server ;
     srvname      | srvowner | srvfdw | srvtype | srvversion | srvacl |                   srvoptions                   
------------------+----------+--------+---------+------------+--------+------------------------------------------------
 server_remote_70 |       10 |  16970 |         |            |        | {host=192.168.11.70,port=5432,dbname=postgres}
(1 row)

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

--for后面的postgres是本地登录执行的用户名,option里存储的是远程的用户密码
postgres=# create user mapping for postgres server server_remote_70 options(user 'postgres',password 'postgres');
CREATE USER MAPPING
postgres=# select * from pg_user_mappings;
 umid  | srvid |     srvname      | umuser | usename  |             umoptions             
-------+-------+------------------+--------+----------+-----------------------------------
 17022 | 17021 | server_remote_70 |     10 | postgres | {user=postgres,password=postgres}
(1 row)

postgres=# \d
              List of relations
 Schema |  Name   |     Type      |  Owner   
--------+---------+---------------+----------
 public | a       | table         | postgres
 public | tbl_fdw | foreign table | postgres
(2 rows)
postgres=# CREATE FOREIGN TABLE tbl_fdw(id int) server server_remote_70 options (schema_name 'public',table_name 'tb1');
CREATE FOREIGN TABLE
postgres=# select * from tbl_fdw ;
 id 
----
  1
  2
  3
(3 rows)


70中的数据
postgres=# \d tb1
      Table "public.tb1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 

postgres=# select * from tb1;
 id 
----
  1
  2
  3
(3 rows)