pg 外部数据封装

外部数据封装是一种管理远程连接,服务和用户的机制,在所有的受支持的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