Greenplum 5.24.2部署dblink
2020-06-02 本文已影响0人
白面葫芦娃92
参考链接：
http://blog.sina.com.cn/s/blog_6b1dfc8701012lqg.html
[gpadmin@hadoop001 ~]$ pg_config --pkglibdir
/opt/greenplum/greenplum-db-5.24.2/lib/postgresql
[gpadmin@hadoop001 ~]$ cd /opt/greenplum/greenplum-db-5.24.2/lib/postgresql
[gpadmin@hadoop001 postgresql]$ ll
total 40980
-rwxr-xr-x 1 gpadmin gpadmin 28864 Jan 31 02:18 ascii_and_mic.so
-rwxr-xr-x 1 gpadmin gpadmin 69064 Jan 31 02:19 citext.so
-rwxr-xr-x 1 gpadmin gpadmin 62536 Jan 31 02:18 cyrillic_and_mic.so
-rwxr-xr-x 1 gpadmin gpadmin 236136 Jan 31 02:19 dblink.so
-rwxr-xr-x 1 gpadmin gpadmin 1263040 Jan 31 02:18 dict_snowball.so
-rwxr-xr-x 1 gpadmin gpadmin 30536 Jan 31 02:18 euc_cn_and_mic.so
-rwxr-xr-x 1 gpadmin gpadmin 40760 Jan 31 02:18 euc_jis_2004_and_shift_jis_2004.so
-rwxr-xr-x 1 gpadmin gpadmin 50608 Jan 31 02:18 euc_jp_and_sjis.so
-rwxr-xr-x 1 gpadmin gpadmin 30720 Jan 31 02:18 euc_kr_and_mic.so
-rwxr-xr-x 1 gpadmin gpadmin 50736 Jan 31 02:18 euc_tw_and_big5.so
-rwxr-xr-x 1 gpadmin gpadmin 104760 Jan 31 02:19 fixedwidth.so
-rwxr-xr-x 1 gpadmin gpadmin 219040 Jan 31 02:19 fuzzystrmatch.so
-rwxr-xr-x 1 gpadmin gpadmin 104576 Jan 31 02:19 gp_ao_co_diagnostics.so
-rwxr-xr-x 1 gpadmin gpadmin 50992 Jan 31 02:19 gp_cancel_query.so
-rwxr-xr-x 1 gpadmin gpadmin 7449576 Jan 31 02:19 gpcloud.so
-rwxr-xr-x 1 gpadmin gpadmin 84944 Jan 31 02:19 gp_distribution_policy.so
-rwxr-xr-x 1 gpadmin gpadmin 71576 Jan 31 02:19 gpextprotocol.so
-rwxr-xr-x 1 gpadmin gpadmin 592104 Nov 26 2019 gpfmt_avro.so
-rwxr-xr-x 1 gpadmin gpadmin 87472 Nov 26 2019 gpfmt_binary.so
-rwxr-xr-x 1 gpadmin gpadmin 455488 Nov 26 2019 gpfmt_confluent_avro.so
-rwxr-xr-x 1 gpadmin gpadmin 90088 Nov 26 2019 gpfmt_delimiter.so
-rwxr-xr-x 1 gpadmin gpadmin 87624 Nov 26 2019 gpfmt_json.so
-rwxr-xr-x 1 gpadmin gpadmin 669928 Nov 26 2019 gpfmt_kafka.so
-rwxr-xr-x 1 gpadmin gpadmin 17217448 Nov 26 2019 gpfmt_protobuf.so
-rwxr-xr-x 1 gpadmin gpadmin 69720 Jan 31 02:19 gpformatter.so
-rwxr-xr-x 1 gpadmin gpadmin 164080 Jan 31 02:20 gphdfs.so
-rwxr-xr-x 1 gpadmin gpadmin 94104 Jan 31 02:19 gp_inject_fault.so
-rwxr-xr-x 1 gpadmin gpadmin 95728 Jan 31 02:19 gp_instrument_shmem.so
lrwxrwxrwx 1 gpadmin gpadmin 10 Jan 31 02:19 gps3ext.so -> gpcloud.so
-rwxr-xr-x 1 gpadmin gpadmin 93904 Jan 31 02:19 gp_session_state_memory_stats.so
-rwxr-xr-x 1 gpadmin gpadmin 89936 Jan 31 02:19 gp_svec.so
-rwxr-xr-x 1 gpadmin gpadmin 2848488 Dec 11 02:57 gp_wlm.so
-rwxr-xr-x 1 gpadmin gpadmin 99216 Jan 31 02:19 gp_workfile_mgr.so
-rwxr-xr-x 1 gpadmin gpadmin 239256 Jan 31 02:19 hstore.so
-rwxr-xr-x 1 gpadmin gpadmin 88768 Jan 31 02:19 indexscan.so
-rwxr-xr-x 1 gpadmin gpadmin 39120 Jan 31 02:18 latin2_and_win1250.so
-rwxr-xr-x 1 gpadmin gpadmin 36800 Jan 31 02:18 latin_and_mic.so
-rwxr-xr-x 1 gpadmin gpadmin 524792 Jan 31 02:30 metrics_collector.so
-rwxr-xr-x 1 gpadmin gpadmin 1123880 Jan 31 02:20 orafunc.so
-rwxr-xr-x 1 gpadmin gpadmin 722960 Jan 31 02:19 pgcrypto.so
-rwxr-xr-x 1 gpadmin gpadmin 115672 Jan 31 02:19 pg_upgrade_support.so
drwxr-xr-x 4 gpadmin gpadmin 4096 Jan 31 02:19 pgxs
-rwxr-xr-x 1 gpadmin gpadmin 524952 Jan 31 02:19 plperl.so
-rwxr-xr-x 1 gpadmin gpadmin 830016 Jan 31 02:19 plpgsql.so
-rwxr-xr-x 1 gpadmin gpadmin 332952 Jan 31 02:19 plpython.so
-rwxr-xr-x 1 gpadmin gpadmin 568008 Jan 31 02:19 pxf.so
-rwxr-xr-x 1 gpadmin gpadmin 28880 Jan 31 02:18 utf8_and_ascii.so
-rwxr-xr-x 1 gpadmin gpadmin 248736 Jan 31 02:18 utf8_and_big5.so
-rwxr-xr-x 1 gpadmin gpadmin 35224 Jan 31 02:18 utf8_and_cyrillic.so
-rwxr-xr-x 1 gpadmin gpadmin 148480 Jan 31 02:18 utf8_and_euc_cn.so
-rwxr-xr-x 1 gpadmin gpadmin 213480 Jan 31 02:18 utf8_and_euc_jis_2004.so
-rwxr-xr-x 1 gpadmin gpadmin 240320 Jan 31 02:18 utf8_and_euc_jp.so
-rwxr-xr-x 1 gpadmin gpadmin 160976 Jan 31 02:18 utf8_and_euc_kr.so
-rwxr-xr-x 1 gpadmin gpadmin 359632 Jan 31 02:18 utf8_and_euc_tw.so
-rwxr-xr-x 1 gpadmin gpadmin 1043112 Jan 31 02:18 utf8_and_gb18030.so
-rwxr-xr-x 1 gpadmin gpadmin 377960 Jan 31 02:18 utf8_and_gbk.so
-rwxr-xr-x 1 gpadmin gpadmin 30888 Jan 31 02:18 utf8_and_iso8859_1.so
-rwxr-xr-x 1 gpadmin gpadmin 62832 Jan 31 02:18 utf8_and_iso8859.so
-rwxr-xr-x 1 gpadmin gpadmin 302128 Jan 31 02:18 utf8_and_johab.so
-rwxr-xr-x 1 gpadmin gpadmin 213016 Jan 31 02:18 utf8_and_shift_jis_2004.so
-rwxr-xr-x 1 gpadmin gpadmin 150800 Jan 31 02:18 utf8_and_sjis.so
-rwxr-xr-x 1 gpadmin gpadmin 305104 Jan 31 02:18 utf8_and_uhc.so
-rwxr-xr-x 1 gpadmin gpadmin 57888 Jan 31 02:18 utf8_and_win.so
[gpadmin@hadoop001 ~]$ cd /home/gpadmin/conf
[gpadmin@hadoop001 conf]$ ll
total 12
-rw-rw-r-- 1 gpadmin gpadmin 2592 Mar 14 15:32 gpinitsystem_config
-rw-rw-r-- 1 gpadmin gpadmin 30 Mar 13 22:58 hostlist
-rw-rw-r-- 1 gpadmin gpadmin 20 Mar 13 23:21 seg_hosts
[gpadmin@hadoop001 conf]$ gpssh -f hostlist 'll /opt/greenplum/greenplum-db-5.24.2/lib/postgresql/dblink.so'
[hadoop001] -rwxr-xr-x 1 gpadmin gpadmin 236136 Jan 31 02:19 /opt/greenplum/greenplum-db-5.24.2/lib/postgresql/dblink.so
[hadoop003] -rwxr-xr-x 1 gpadmin gpadmin 236136 Jan 31 02:19 /opt/greenplum/greenplum-db-5.24.2/lib/postgresql/dblink.so
[hadoop002] -rwxr-xr-x 1 gpadmin gpadmin 236136 Jan 31 02:19 /opt/greenplum/greenplum-db-5.24.2/lib/postgresql/dblink.so
[gpadmin@hadoop001 conf]$ psql -d bssignal
psql (8.3.23)
Type "help" for help.
bssignal=# \q
https://www.postgresql.org/ftp/source/v8.3.23/
[gpadmin@hadoop001 conf]$ cd
[gpadmin@hadoop001 ~]$ ll
total 16
drwxrwxr-x 2 gpadmin gpadmin 4096 Mar 14 15:32 conf
drwxrwxr-x 2 gpadmin gpadmin 4096 Jun 2 11:17 gpAdminLogs
drwxrwxr-x 7 gpadmin gpadmin 4096 Mar 13 23:29 gpdata
drwxrwxr-x 2 gpadmin gpadmin 4096 May 27 23:06 testdata
[gpadmin@hadoop001 ~]$ rz
rz waiting to receive.
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring postgresql-8.3.23.tar.gz...
100% 17953 KB 17953 KB/sec 00:00:01 0 Errors
[gpadmin@hadoop001 ~]$ cd /home/gpadmin/postgresql-8.3.23/contrib/dblink
[gpadmin@hadoop001 dblink]$ ll
total 84
-rw-r--r-- 1 gpadmin gpadmin 55399 Feb 5 2013 dblink.c
-rw-r--r-- 1 gpadmin gpadmin 2435 Feb 5 2013 dblink.h
-rw-r--r-- 1 gpadmin gpadmin 5610 Feb 5 2013 dblink.sql.in
drwxrwxr-x 2 gpadmin gpadmin 4096 Feb 5 2013 expected
-rw-r--r-- 1 gpadmin gpadmin 489 Feb 5 2013 Makefile
drwxrwxr-x 2 gpadmin gpadmin 4096 Feb 5 2013 sql
-rw-r--r-- 1 gpadmin gpadmin 1905 Feb 5 2013 uninstall_dblink.sql
[gpadmin@hadoop001 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
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/dblink
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
[gpadmin@hadoop001 dblink]$ make USE_PGXS=1 install
sed 's,MODULE_PATHNAME,$libdir/dblink,g' dblink.sql.in >dblink.sql
gcc -m64 -m64 -O3 -fargument-noalias-global -fno-omit-frame-pointer -g -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fno-aggressive-loop-optimizations -Wno-unused-but-set-variable -Wno-address -I/usr/local/curl/include -fpic -I/opt/greenplum/greenplum-db-5.24.2/include -w -I. -I. -I/opt/greenplum/greenplum-db-5.24.2/include/postgresql/server -I/opt/greenplum/greenplum-db-5.24.2/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/tmp/build/f8c7ee08/gpdb_src/gpAux/ext/rhel7_x86_64/include -I/tmp/build/f8c7ee08/gpdb_src/gpAux/ext/rhel7_x86_64/include/libxml2 -I/tmp/build/f8c7ee08/gpdb_src/gpAux/addon/src/include -c -o dblink.o dblink.c
gcc -m64 -m64 -O3 -fargument-noalias-global -fno-omit-frame-pointer -g -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fno-aggressive-loop-optimizations -Wno-unused-but-set-variable -Wno-address -I/usr/local/curl/include -fpic -shared dblink.o -L/opt/greenplum/greenplum-db-5.24.2/lib -L/opt/greenplum/greenplum-db-5.24.2/lib -L/opt/greenplum/greenplum-db-5.24.2/lib -L/tmp/build/f8c7ee08/gpdb_src/gpAux/ext/rhel7_x86_64/lib -lpq -Wl,-rpath,'/opt/greenplum/greenplum-db-5.24.2/lib',--enable-new-dtags -o dblink.so
/usr/bin/mkdir -p '/opt/greenplum/greenplum-db-5.24.2/lib/postgresql'
/bin/sh /opt/greenplum/greenplum-db-5.24.2/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 755 dblink.so '/opt/greenplum/greenplum-db-5.24.2/lib/postgresql/dblink.so'
/bin/sh /opt/greenplum/greenplum-db-5.24.2/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./uninstall_dblink.sql dblink.sql '/opt/greenplum/greenplum-db-5.24.2/share/postgresql/contrib/'
[gpadmin@hadoop001 dblink]$ ll
total 684
-rw-r--r-- 1 gpadmin gpadmin 55399 Feb 5 2013 dblink.c
-rw-r--r-- 1 gpadmin gpadmin 2435 Feb 5 2013 dblink.h
-rw-rw-r-- 1 gpadmin gpadmin 409128 Jun 2 11:36 dblink.o
-rwxrwxr-x 1 gpadmin gpadmin 192816 Jun 2 11:36 dblink.so
-rw-rw-r-- 1 gpadmin gpadmin 5572 Jun 2 11:36 dblink.sql
-rw-r--r-- 1 gpadmin gpadmin 5610 Feb 5 2013 dblink.sql.in
drwxrwxr-x 2 gpadmin gpadmin 4096 Feb 5 2013 expected
-rw-r--r-- 1 gpadmin gpadmin 492 Jun 2 11:36 Makefile
drwxrwxr-x 2 gpadmin gpadmin 4096 Feb 5 2013 sql
-rw-r--r-- 1 gpadmin gpadmin 1905 Feb 5 2013 uninstall_dblink.sql
##注意这个目录是从上边编译过程的提示里抄过来的已加粗褐色
##另外还要注意下边这个/home/gpadmin/conf/hostlist文件里要包含你所有的主用备用控制节点和数据节点
[gpadmin@hadoop001 dblink]$ gpscp -f /home/gpadmin/conf/hostlist dblink.so =:/opt/greenplum/greenplum-db-5.24.2/lib/postgresql/dblink.so
[gpadmin@hadoop001 dblink]$ psql -f dblink.sql bssignal
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
REVOKE
REVOKE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
[gpadmin@hadoop001 dblink]$ psql -d dblink_test;
psql (8.3.23)
Type "help" for help.
dblink_test=# SELECT dblink_connect('bssignal_dblink', 'dbname=bssignal');
dblink_connect
----------------
OK
(1 row)
dblink_test=# SELECT * FROM dblink('bssignal_dblink', 'SELECT device_name,device_code,attach_unit_code FROM public.m_signal_device')AS t1(device_name varchar(50),device_code varchar(20),attach_unit_code varchar(13)) LIMIT 5;
device_name | device_code | attach_unit_code
-------------+-----------------+------------------
受端适配器 | 40542S120904606 | 40542S1209046
受端适配器 | 40542S120907706 | 40542S1209077
受端适配器 | 40542S120909906 | 40542S1209099
受端适配器 | 40542S120910012 | 40542S1209100
送端适配器 | 40542S120903104 | 40542S1209031
(5 rows)
