postgres_fdw的安装和使用

PostgreSQL的数据库逻辑上是相互独立的，和Oracle类似，如果要访问其他数据库，需要做跨库操作，Postgres本身提供了一些扩展，比如dblink,pgsql_fdw等，高版本的建议使用postgres_fdw，也就是pgsql_fdw的升级版。fdw是foreign-data wrapper的一个简称，可以叫外部封装数据，之前介绍过file_fdw,dblink。而postgres_fdw实现的是各个postgresql数据库及远程数据库之间的跨库操作，功能和dblink一样。  首先是安装问题，因为这个工具是自带的扩展工具，可以直接到postgres的解压目录下。我测试用的是postgres9.4.4，放在了/tmp下。所以我的目录是   /tmp/postgresql-9.4.4/contrib。cd /tmp/postgresql-9.4.4/contribmake make install运行完上面的命令后，可以直接进入数据库，然后使用命令select * from pg_available_extensions;    查看是否安装成功。如果可以看到postgres_fdw，说明已经安装成功。然后创建该扩展工具：create extension postgres_fdw;     就可以正常使用了。查看已经安装并创建的扩展工具：test=# \dx                                       List of installed extensions     Name     | Version |   Schema   |                           Description                           --------------+---------+------------+----------------------------------------------------------------- pg_bulkload  | 1.0     | public     | pg_bulkload is a high speed data loading utility for PostgreSQL plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL serversselect * from pg_foreign_data_wrapper;  （这个命令也可以）postgres_fdw使用很简单，分为四部。第一步创建远程服务器，这里需要定义主机地址，数据库名和端口。第二步指定连接远程数据库的用户，和创建的远程服务器一起封装。第三步创建远程表，这里也需要把远程表的字段和类型都列一下。第四步和操作本地表一样操作远程表。下面开始正式测试：环境：  red hat 6.5 数据库：postgres 9.4.4IP：    192.168.10.8本次测试使用一台服务器，有两个库test和postgres在test库中创建外部表，查看postgres中的表tb1（也可以做其他操作）postgres中：postgres=# select * from tb1 ; a --- 1 2 3 7(4 rows)test中：创建远程服务器：test=# create server server_remote_70 foreign data wrapper postgres_fdw options(host '192.168.10.8',port '5432',dbname 'postgres');CREATE SERVERtest=# select * from pg_foreign_server ;       srvname      | srvowner | srvfdw | srvtype | srvversion | srvacl |                  srvoptions                   ------------------+----------+--------+---------+------------+--------+----------------------------------------------- server_remote_70 |       10 |  33291 |         |            |        | {host=192.168.10.8,port=5432,dbname=postgres}(1 row)指定连接远程数据库的用户和密码：test=# create user mapping for postgres server server_remote_70 options(user 'postgres',password 'postgres');CREATE USER MAPPINGtest=# select * from pg_user_mappings;   umid  | srvid |     srvname      | umuser | usename  |             umoptions             -------+-------+------------------+--------+----------+----------------------------------- 33311 | 33310 | server_remote_70 |     10 | postgres | {user=postgres,password=postgres}(1 row)创建远程表并查看数据：test=# CREATE FOREIGN TABLE tbl_fdw(a int) server server_remote_70 options (schema_name 'public',table_name 'tb1');  CREATE FOREIGN TABLEtest=# select * from tbl_fdw ; a --- 1 2 3 7(4 rows)操作远程表：test=# insert into tbl_fdw values (9);INSERT 0 1在postgres查看tb1表。postgres=# select * from tb1 ; a --- 1 2 3 7 9(5 rows)成功！   最后需要注意的一点是：本次我使用的是同一台服务器上的两套库之间查询。假如使用两台服务器的话，那么需要两台服务器之间的数据库能够互相远程才可以，也就是说需要修改postgres.conf中的listen_addresses = '*'  以及pg_hba.conf中在IPV4下面增加一列：host all all 0.0.0.0/0 trust我的这次测试其实可能不需要修改默认的listen_addresses = ‘locahost’,因为是使用本机连接。但是我的这次测试还是失败了，原因是我的这台服务器在建库以后修改过IP地址。所以locahost可能代表的是原来的IP地址（192.168.1.8），而不是修改以后的。为什么会做出这样的推测，是因为我用修改后的IP 直接进入数据库会报错：（修改以前的IP地址是192.168.1.8）[postgres@pg2 data]$ psql -h 192.168.10.8  -U postgres -d postgrespsql: could not connect to server: Connection refused        Is the server running on host "192.168.10.8" and accepting        TCP/IP connections on port 5432?那么我之前是怎么进入数据库的呢？直接使用psql 或者psql dbname。