PostgreSQL的DBLink使用

最新推荐文章于 2024-04-22 15:07:44 发布

weixin_33704591

最新推荐文章于 2024-04-22 15:07:44 发布

阅读量338

点赞数

文章标签：数据库 git

原文链接：https://my.oschina.net/Kenyon/blog/55294

版权

为什么80%的码农都做不了架构师？>>>

习惯了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/interfaces/libpq all
make[1]: Entering directory `/home/postgres/postgresql-9.1.3/src/interfaces/libpq'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/postgres/postgresql-9.1.3/src/interfaces/libpq'
[postgres@localhost  dblink]$ make install
make -C ../../src/interfaces/libpq all
make[1]: Entering directory `/home/postgres/postgresql-9.1.3/src/interfaces/libpq'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/postgres/postgresql-9.1.3/src/interfaces/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]$

我们可以在$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)

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)

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 
----------------
 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函数见上面的图，下面不介绍。
简单使用的总结
好处：可以跨库进行Select，简化了不少工作
劣处：感觉还不是很完善，使用上比Oracle的要麻烦一点,最后的查询表t1结果集取决于前面查询test的结果集。

PS：在编译dblink的时候，发生了一些波折，之前因为安装的源码文件被动过，编译安装后创建extension时一直报load dblink.so文件失败。后来在德哥的帮助下，重新安装DB，不变动文件情况下正常生成。这也提了个醒，安装DB成功后不管源文件，还是安装文件等就不要去动了，前期要有一套规范的DB安装设计。
德哥的BLOG： http://blog.163.com/digoal@126/blog/static/163877040201232692431920/

转载于:https://my.oschina.net/Kenyon/blog/55294