外部数据封装是一种管理远程连接,服务和用户的机制,在所有的受支持的postgresql版本中都存在.
一.使用dblink的外部数据封装
具体步骤分为3步
1.首先安装dblink模块
安装dblink模块很简单,在postgresq安装文件中的contrib/dblink下执行make,make install
[postgres@postgresql1 dblink]$ make
make -C ../../src/interfaces/libpq all
make[1]: Entering directory `/data/soft/postgresql-10.2/src/interfaces/libpq'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/data/soft/postgresql-10.2/src/interfaces/libpq'
[postgres@postgresql1 dblink]$ make install
make -C ../../src/interfaces/libpq all
make[1]: Entering directory `/data/soft/postgresql-10.2/src/interfaces/libpq'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/data/soft/postgresql-10.2/src/interfaces/libpq'
/bin/mkdir -p '/usr/local/pgsql/lib'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/usr/bin/install -c -m 755 dblink.so '/usr/local/pgsql/lib/dblink.so'
/usr/bin/install -c -m 644 ./dblink.control '/usr/local/pgsql/share/extension/'
/usr/bin/install -c -m 644 ./dblink--1.2.sql ./dblink--1.1--1.2.sql ./dblink--1.0--1.1.sql ./dblink--unpackaged--1.0.sql '/usr/local/pgsql/share/extension/'
然后在psql中安装dblink extension:
test=# create extension dblink;
CREATE EXTENSION
2.创建访问的相关定义,这里又分为三步
(1).定义一个外部数据封装.这个只能是超级用户才能创建
CREATE FOREIGN DATA WRAPPER creates a new foreign-data wrapper. The user who defines a foreign-data wrapper becomes its owner.
The foreign-data wrapper name must be unique within the database.
Only superusers can create foreign-data wrappers.
test=# create foreign data wrapper pgs;
CREATE FOREIGN DATA WRAPPER
(2).创建一个server.此server定义了远程连接数据库的参数.如果是普通用户,须有先授权可以使用上面的封装
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
test=> create server mysrv foreign data wrapper pgs options (host '192.168.56.131',dbname 'stgpdb',port '5432');
CREATE SERVER
(3).创建一个user mapping,定义了上面server连接的用户名和密码.
test=> create user mapping for brent server mysrv options (user 'postgres',password 'postgres');
CREATE USER MAPPING
3.现在就可以使用上面创建的server来连接dblink了:
test=> select dblink_connect('mysrv');
dblink_connect
----------------
OK
(1 row)
二.使用postgresql外部数据封装代替dblink
此种方法只适合9.3以后的版本.
1.同理创建extension
test=# create extension postgres_fdw;
CREATE EXTENSION
test=# \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator
--------------+----------+----------------------+------------------------
dblink_fdw | postgres | - | dblink_fdw_validator
pgs | postgres | - | -
postgres_fdw | postgres | postgres_fdw_handler | postgres_fdw_validator
2.创建server,不过创建的server使用的postgres_fdw
首先使用超级用户授权:
test=# grant all on FOREIGN DATA WRAPPER postgres_fdw to brent;
GRANT
然后在普通用户下创建server
test=> create server mysrv2 foreign data wrapper postgres_fdw options (host '192.168.56.131',dbname 'stgpdb',port '5432');
CREATE SERVER
3.创建用户映射
test=> create user mapping for brent server mysrv2 options (user 'brent',password 'brent');
CREATE USER MAPPING
4.创建外部表,表示对远程表的访问
test=> create foreign table ft(cjbh varchar(20),xgsj varchar(14))
test-> server mysrv2
test-> options (
test(> schema_name 'msg',
test(> table_name 'jcj_cjxx' );
CREATE FOREIGN TABLE
5.现在操作这个表基本就和本地表一样了
test=> select * from ft;
cjbh | xgsj
------+--------
001 | 11111
002 | 2222
003 | 333333
004 | 4444
005 | 4444
(5 rows)
test=> insert into ft values('006','8888');
INSERT 0 1