PostgreSQL DBLink的使用
习惯了Oracle的Dblink带来的便捷，我们来看一下PostgreSQL的DBLINK，该工具需要编译安装一下使用，倒也不是很复杂。 DB版本：9.1.3 1.编译安装
[postgres@localhost dblink]$ pwd
/home/postgres/postgresql-9.1.3/contrib/dblink
[postgres@localhost dblink]$ make
make -C ../../src/inte易做图ces/libpq all
make[1]: Entering directory `/home/postgres/postgresql-9.1.3/src/inte易做图ces/libpq'
make[1]: Nothing to be done for `all'. www.zzzyk.com
make[1]: Leaving directory `/home/postgres/postgresql-9.1.3/src/inte易做图ces/libpq'
[postgres@localhost dblink]$ make install
make -C ../../src/inte易做图ces/libpq all
make[1]: Entering directory `/home/postgres/postgresql-9.1.3/src/inte易做图ces/libpq'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/postgres/postgresql-9.1.3/src/inte易做图ces/libpq'
/bin/mkdir -p '/home/postgres/lib'
/bin/mkdir -p '/home/postgres/share/extension'
/bin/sh ../../config/install-sh -c -m 755 dblink.so '/home/postgres/lib/dblink.so'
/bin/sh ../../config/install-sh -c -m 644 ./dblink.control '/home/postgres/share/extension/'
/bin/sh ../../config/install-sh -c -m 644 ./dblink--1.0.sql ./dblink--unpackaged--1.0.sql '/home/postgres/share/extension/'
[postgres@localhost dblink]$ www.zzzyk.com
我们可以在$PGHOME/share/extension下看到3个dblink文件在$PGHOME/lib下也生成一个dblink.so的动态文件 2.创建DBLINK的扩展
[postgres@localhost dblink]$ psql -d test -U postgres
psql (8.4.9, server 9.1.3)
WARNING: psql version 8.4, server version 9.1.
Some psql features might not work.
Type "help" for help.
test=# create extension dblink;
CREATE EXTENSION
test=#
3.查看当前数据库里的扩展包
test=# select extname,extversion from pg_extension;
extname | extversion
-----------+------------
plpgsql | 1.0
tablefunc | 1.0
dblink | 1.0
(3 rows) www.zzzyk.com
4.工具栏中得体现
5.DBLINK使用 a.数据库中情况如下：
[postgres@localhost etc]$ psql
psql (8.4.9, server 9.1.3)
WARNING: psql version 8.4, server version 9.1.
Some psql features might not work.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------+-----------------------
kenyon | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres
: postgres=CTc/postgres
test | postgres | UTF8 | C | C |
(5 rows) www.zzzyk.com
postgres=# \c kenyon
psql (8.4.9, server 9.1.3)
WARNING: psql version 8.4, server version 9.1.
Some psql features might not work.
You are now connected to database "kenyon".
kenyon=# select * from test;
id | name
----+-------------------
1 | kenyon
2 | God in your heart
(2 rows)
b.建立dblink连接
test=# select dblink_connect('kenyon_dblink','dbname=kenyon host=localhost port=1949 user=postgres password=postgres');
dblink_connect www.zzzyk.com
----------------
OK
(1 row)
c.使用
test=# select * from dblink('kenyon_dblink','select * from test') as t1 (id integer,name varchar);)
id | name
----+-------------------
1 | kenyon
2 | God in your heart
(2 rows)
涉及PG的dblink函数见上面的图，下面不介绍。
简单使用的总结 www.zzzyk.com
好处：可以跨库进行Select，简化了不少工作
劣处：感觉还不是很完善，使用上比Oracle的要麻烦一点,最后的查询表t1结果集取决于前面查询test的结果集。
PS：在编译dblink的时候，发生了一些波折，之前因为安装的源码文件被动过，编译安装后创建extension时一直报load dblink.so文件失败。后来在德哥的帮助下，重新安装DB，不变动文件情况下正常生成。这也提了个醒，安装DB成功后不管源文件，还是安装文件等就不要去动了，前期要有一套规范的DB设计。
作者 kenyon
