注册
登录
| 关键词： nbsp postgres Dblink kenyon extension localhost psql home make test |
习惯了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'. www.2cto.com 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]$ www.2cto.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.2cto.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.2cto.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.2cto.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.2cto.com 好处：可以跨库进行Select，简化了不少工作 劣处：感觉还不是很完善，使用上比Oracle的要麻烦一点,最后的查询表t1结果集取决于前面查询test的结果集。 PS：在编译dblink的时候，发生了一些波折，之前因为安装的源码文件被动过，编译安装后创建extension时一直报load dblink.so文件失败。后来在德哥的帮助下，重新安装DB，不变动文件情况下正常生成。这也提了个醒，安装DB成功后不管源文件，还是安装文件等就不要去动了，前期要有一套规范的DB设计。 作者 kenyon |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
