Greenplum 的dblink与tablefunc模块安装

1、dblink模块安装

登录主节点并切换到GP用户下

[gpadmin@mdw ~]#su - gpadmin

下载源码包并解压，进入dblink目录下

[gpadmin@mdw ~]$ wget https://ftp.postgresql.org/pub/source/v8.2.23/postgresql-8.2.23.tar.gz[gpadmin@mdw ~]$ tar xvf postgresql-8.2.23.tar.gz [gpadmin@mdw ~]$ cd postgresql-8.2.23/contrib/dblink/

修改Makefile文件，作用是去掉编译中的警告信息

[gpadmin@mdw dblink]$ vi Makefile PG_CPPFLAGS = -I$(libpq_srcdir) -w

编译dblink模块

[gpadmin@mdw dblink]$  make USE_PGXS=1 install

查看hosts中的节点信息

[gpadmin@mdw dblink]$ cat /etc/hosts127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain6172.16.104.21  mdw172.16.104.22  smdw172.16.104.23  sdw1172.16.104.24  sdw2172.16.104.25  sdw3

把模块复制到各个节点

[gpadmin@mdw dblink]$  gpscp -h smdw -h sdw1 -h sdw2 -h sdw3  dblink.so =:/usr/local/greenplum-db/lib/postgresql/dblink.so

初始化dblink模块

[gpadmin@mdw dblink]$ psql -f dblink.sql gpdatabaseCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONREVOKEREVOKECREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE TYPECREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTION

测试

[gpadmin@mdw dblink]$ psql -d gpdatabasepsql (8.2.15)Type "help" for help.gpdatabase=# select dblink_connect('myconn','dbname=gpdatabase'); dblink_connect ---------------- OK(1 row)

OK，安装成功。

2、安装tablefunc模块，步骤基本如上，

切换到tablefunc目录下,编译

[gpadmin@mdw ~]$ cd postgresql-8.2.23/contrib/tablefunc/[gpadmin@mdw tablefunc]$  make USE_PGXS=1 install

把模块传到各节点

[gpadmin@mdw dblink]$  gpscp -h smdw -h sdw1 -h sdw2 -h sdw3  tablefunc.so =:/usr/local/greenplum-db/lib/postgresql/tablefunc.so

初始化模块

[gpadmin@mdw tablefunc]$ psql -f tablefunc.sql gpdatabaseSETCREATE FUNCTIONCREATE FUNCTIONCREATE TYPECREATE TYPECREATE TYPECREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTION

测试

[gpadmin@mdw ~]$ psql -d gpdatabasepsql (8.2.15)Type "help" for help.gpdatabase=# select * from normal_rand(10, 5, 3);    normal_rand    ------------------ 6.49804853868727 5.94780258015849 3.76292085922418 2.26097995785225 1.06472447639215 1.83973228257959  3.4955971041885 3.95784964260771 7.32294513598636 9.51874065685311(10 rows)

OK，安装成功。