postgresql 中的postgres_fdw

本文主要是介绍postgresql 中的postgres_fdw，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

两台主机，192.168.11.67192.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=# \dxList of installed extensionsName     | Version |   Schema   |                         Description                          
--------------+---------+------------+--------------------------------------------------------------dblink       | 1.1     | public     | connect to other PostgreSQL databases from within a databaseplpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural languagepostgres_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=# \desList of foreign serversName       |  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=# \dList of relationsSchema |  Name   |     Type      |  Owner   
--------+---------+---------------+----------public | a       | table         | postgrespublic | 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 
----123
(3 rows)70中的数据
postgres=# \d tb1Table "public.tb1"Column |  Type   | Modifiers 
--------+---------+-----------id     | integer | postgres=# select * from tb1;id 
----123
(3 rows)

这篇关于postgresql 中的postgres_fdw的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---