greenplum数据库创建dblink

        最初使用greenplum的时候就想oracle有一个非常实用的跨库查询功能dblink，那么gp库有没有这个功能呢，也有很多同事问道gp库有没有dblink，答案是有。用了很久的gp库了一直没使用dblink，直到后面需要用到这个功能了才去创建，gp库的dblink创建与oracle完全不一样，实际上去创建dblink时可是吃尽苦头，网上查了很多，可是都没能成功。

 .首先要下载greenplum库版本所相对应的postgres包，比如gp4.3.8本人使用postgresql-8.3.23.tar.gz（https://download.csdn.net/download/jun_ming/10791970）

1.上传包创建dblink使用的包postgresql-8.3.23.tar.gz并解压

tar -xvf postgresql-8.3.23.tar.gz 

2. 进入contrib/dblink目录，找到Makefile文件修改内容PG_CPPFLAGS = -I$(libpq_srcdir) -w 保存

cd postgresql-8.3.23/contrib/dblink/

[****dblink]$ vi Makefile 

# $PostgreSQL: pgsql/contrib/dblink/Makefile,v 1.15 2007/11/10 23:59:50 momjian Exp $

MODULE_big = dblink
PG_CPPFLAGS = -I$(libpq_srcdir) -w
OBJS    = dblink.o
SHLIB_LINK = $(libpq)

DATA_built = dblink.sql
DATA = uninstall_dblink.sql
REGRESS = dblink

3.安装

[***** dblink]$ make USE_PGXS=1 install
sed 's,MODULE_PATHNAME,$libdir/dblink,g' dblink.sql.in >dblink.sql
gcc -m64 -O3 -funroll-loops -fargument-noalias-global -fno-omit-frame-pointer -g -finline-limit=1800 -std=gnu99  -Wall -Wmissing-prototypes -Wpointer-arith  -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -I/data/espine1/dev/tools/curl/7.21.7/dist/rhel5_x86_64/include -Werror -fpic -I/opt/greenplum/greenplum-db-4.3.8.0/include -w -I. -I/opt/greenplum/greenplum-db-4.3.8.0/include/postgresql/server -I/opt/greenplum/greenplum-db-4.3.8.0/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/data/pulse2-agent/agents/agent1/work/GPDB-4_3_x-rcbuilds-1repo/rhel5_x86_64/src/gpAux/ext/rhel5_x86_64/include -I/data/pulse2-agent/agents/agent1/work/GPDB-4_3_x-rcbuilds-1repo/rhel5_x86_64/src/gpAux/ext/rhel5_x86_64/include/libxml2  -c -o dblink.o dblink.c
gcc -m64 -O3 -funroll-loops -fargument-noalias-global -fno-omit-frame-pointer -g -finline-limit=1800 -std=gnu99  -Wall -Wmissing-prototypes -Wpointer-arith  -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -I/data/espine1/dev/tools/curl/7.21.7/dist/rhel5_x86_64/include -Werror -fpic -shared  dblink.o  -L/opt/greenplum/greenplum-db-4.3.8.0/lib -L/opt/greenplum/greenplum-db-4.3.8.0/lib -L/opt/greenplum/greenplum-db-4.3.8.0/lib -L/data/pulse2-agent/agents/agent1/work/GPDB-4_3_x-rcbuilds-1repo/rhel5_x86_64/src/gpAux/ext/rhel5_x86_64/lib -lpq -Wl,-rpath,'/opt/greenplum/greenplum-db-4.3.8.0/lib',--enable-new-dtags -o dblink.so
/bin/mkdir -p '/opt/greenplum/greenplum-db-4.3.8.0/lib/postgresql'
/bin/sh /opt/greenplum/greenplum-db-4.3.8.0/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 755  dblink.so '/opt/greenplum/greenplum-db-4.3.8.0/lib/postgresql/dblink.so'
/bin/sh /opt/greenplum/greenplum-db-4.3.8.0/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./uninstall_dblink.sql '/opt/greenplum/greenplum-db-4.3.8.0/share/postgresql/contrib'
/bin/sh /opt/greenplum/greenplum-db-4.3.8.0/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 644 dblink.sql '/opt/greenplum/greenplum-db-4.3.8.0/share/postgresql/contrib'
[***** dblink]$ 

4.使用gpscp命令 拷贝dblink.so文件到各个节点：

[********* ~]$ gpscp -f host_seg /opt/greenplum/greenplum-db-4.3.8.0/lib/postgresql/dblink.so =:/opt/greenplum/greenplum-db-4.3.8.0/lib/postgresql/dblink.so

5.到contrib目录下执行dblink.sql 文件

[******** contrib]$ psql -f dblink.sql >dblink.sql.log 2>&1 &

[******** contrib]$ more dblink.sql.log 
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
REVOKE
REVOKE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION........................................................

6.检查是否成功

[****** contrib]$ psql
psql (8.2.15)
Type "help" for help.
csgbi=# 
csgbi=# select * from  dblink_connect('?','dbname=csgbi');
 dblink_connect 
----------------
 OK
(1 row)

csgbi=# 

已成功安装dblink模块。