centos7 geenplum5.x postgis开源版本编译

下载5.1
https://codeload.github.com/greenplum-db/gpdb/tar.gz/5.1.0

基础环境

tar xzvf gpdb-5.1.0.tar.gz
cd gpdb-5.1.0
参照README.md、README.amazon_linux准备依赖
yum install -y epel-release
yum install -y python-pip
sudo yum -y install git gcc readline-devel zlib-devel libcurl-devel bzip2-devel bison flex gcc-c++ python-devel openssl-devel libffi-devel libapr-devel libevent-devel
sudo yum -y install perl-ExtUtils-MakeMaker.noarch perl-ExtUtils-Embed.noarch
sudo yum -y install apr-util-devel libxml2-devel libxslt-devel
yum install -y unzip

sudo pip install --upgrade setuptools wheel paramiko pip lockfile psutil

https://github.com/greenplum-db/gp-xerces
下载下来解压
cd gp-xerces
mkdir build
cd build
…/configure
make install
cd …/…/

yum install -y cmake3 ninja-build
sudo ln -sf /usr/bin/cmake3 /usr/bin/cmake
https://github.com/greenplum-db/gporca

git clone https://github.com/greenplum-db/gporca.git --depth 1

git config --global http.postBuffer 524288000
git config --global http.lowSpeedLimit 0
git config --global http.lowSpeedTime 999999

git clone --branch v2.46.6 https://github.com/greenplum-db/gporca.git --depth 1

cd gporca
mkdir build
cd build

cmake -GNinja …
ninja-build install
cd …/…/

vi /etc/ld.so.conf
添加
/usr/local/lib
#运行
ldconfig

编译
./configure --with-openssl --with-libxml --with-libxslt --with-python --with-perl

报错，提示ORCA版本要2.46
checking Checking ORCA version… configure: error: Your ORCA version is expected to be 2.46.XXX

make
make install

编译通过
重新编译需要先make clean再make

部署
#修改配置
sudo bash -c 'cat >> /etc/sysctl.conf <<-EOF
kernel.shmmax = 500000000
kernel.shmmni = 4096
kernel.shmall = 4000000000
kernel.sem = 500 1024000 200 4096
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_forward = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.all.arp_filter = 1
net.ipv4.ip_local_port_range = 1025 65535
net.core.netdev_max_backlog = 10000
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152
vm.overcommit_memory = 2

EOF’

sudo bash -c 'cat >> /etc/security/limits.conf <<-EOF

• soft nofile 65536
• hard nofile 65536
• soft nproc 131072
• hard nproc 131072

EOF’

. /usr/local/gpdb/greenplum_path.sh

cd /usr/local/gpdb/
mkdir conf
cd conf

cp $GPHOME/docs/cli_help/gpconfigs/gpinitsystem_test .
mkdir -p /data/gpdb/primary
mkdir -p /data/gpdb/mirror
mkdir -p /data/gpdb/master

hostnamectl set-hostname gpc
vi /etc/hosts
添加
192.168.81.153 gpc

vi hostfile_gpinitsystem
添加
gpc

#gpssh-exkeys -h gpc

vi gpinitsystem_test
修改
declare -a DATA_DIRECTORY=(/data/gpdb/primary /data/gpdb/primary)
declare -a MIRROR_DATA_DIRECTORY=(/data/gpdb/mirror /data/gpdb/mirror)
MASTER_DIRECTORY=/data/gpdb/master
MASTER_HOSTNAME=gpc
MACHINE_LIST_FILE=/usr/local/gpdb/conf/hostfile_gpinitsystem

添加用户
groupadd -g 530 gpadmin
useradd -g 530 -u 530 -m -d /home/gpadmin -s /bin/bash gpadmin
chown -R gpadmin:gpadmin /home/gpadmin
echo “gpadmin” | passwd --stdin gpadmin
赋权
chown -R gpadmin:gpadmin /data/gpdb
chown -R gpadmin:gpadmin /usr/local/gpdb

开启ssh
vi /etc/ssh/sshd_config
RSAAuthentication yes #开启私钥验证
PubkeyAuthentication yes #开启公钥验证

su - gpadmin
cd ~
ssh-keygen -t rsa -P ‘’
cat /home/gpadmin/.ssh/id_rsa.pub >> /home/gpadmin/.ssh/authorized_keys
chmod 700 .ssh/authorized_keys

接重启ssh服务(使用root用户)
sudo systemctl restart sshd.service

登录
su - gpadmin

cd /usr/local/gpdb/conf/

gpssh -f /usr/local/gpdb/conf/hostfile_gpinitsystem -e -v "cat >> /home/gpadmin/.bashrc <

source /usr/local/gpdb/greenplum_path.sh
export MASTER_DATA_DIRECTORY=/data/gpdb/master/gpseg-1
export GPPORT=5432
export PGDATAbase=gp_sydb
EOF"

source ~/.bashrc

#初始化数据库
gpinitsystem -c gpinitsystem_test

初始化失败需要重新来
rm -rf /data/gpdb/*
mkdir -p /data/gpdb/primary
mkdir -p /data/gpdb/mirror
mkdir -p /data/gpdb/master

提示有/tmp/.s.PGSQL.19000.lock类似名称的锁文件，要将文件删除
rm -rf /tmp/.s.PGSQL.*

gpinitsystem -c gpinitsystem_test
直到出现成功提示
20191223:16:48:30:002485 gpinitsystem:gpc:gpadmin-[WARN]******************************************************
20191223:16:48:30:002485 gpinitsystem:gpc:gpadmin-[INFO]:-Greenplum Database instance successfully created
20191223:16:48:30:002485 gpinitsystem:gpc:gpadmin-[INFO]:-------------------------------------------------------

此时就可以登录数据库
psql -d postgres
#查询数据库
l
#设置gpadmin远程密码
alter user gpadmin encrypted password ‘gpadmin’;

修改master的pg_hba.conf
pg_hba.conf是控制数据库访问的文件

vi $MASTER_DATA_DIRECTORY/pg_hba.conf
host all gpadmin 0.0.0.0/0 md5
执行gpstop -u使配置生效

关闭防火墙
systemctl stop firewalld
systemctl disable firewalld

使用客户端通过5432端口连接postgresql数据库即可

postgis各种依赖
依赖安装
yum install -y gdal-devel
下载编译libgeos

yum install -y wget
wget http://download.osgeo.org/geos/geos-3.8.0.tar.bz2
yum install -y bzip2
tar jxf geos-3.8.0.tar.bz2
#编译
./configure -prefix=/usr/local/geos && make && make install

下载编译proj4

wget http://download.osgeo.org/proj/proj-4.9.3.tar.gz #http://download.osgeo.org/proj/proj-6.2.1.tar.gz 注意项目需要的是4.x，其他版本不可用
tar zxf proj-4.9.3.tar.gz
cd proj-4.9.3

yum install -y libsqlite3x-devel
./configure -prefix=/usr/local/proj4 && make && make install

将编译好的依赖包拷贝到运行目录中
方便数据库的整体拷贝
cp -rd /usr/local/proj4/lib/* /usr/local/gpdb/lib
cp -rd /usr/local/geos/lib/* /usr/local/gpdb/lib
cp -rd /usr/local/lib/* /usr/local/gpdb/lib
cp -rd /usr/lib64/libevent* /usr/local/gpdb/lib
cp -rd /usr/lib64/libapr* /usr/local/gpdb/lib

postgis编译
git clone https://github.com/greenplum-db/postgis.git --depth 1

vi /etc/ld.so.conf
添加
/usr/local/gpdb/lib
#运行
ldconfig
编译
./configure --with-pgconfig=/usr/local/gpdb/bin/pg_config --with-geosconfig=/usr/local/geos/bin/geos-config --with-projdir=/usr/local/proj4

源码编译过程中有一处报错
vi postgis/Makefile
修改内容
$(SQL_OBJS): %.in: %.in.c
$(SQLPP) -I…/libpgcommon -I/usr/local/gpdb/include $< | grep -v ‘^#’ > $@

make&& make install

编译完成后
/usr/local/gpdb/share/postgresql/contrib/postgis-2.0/
会有这个目录

下载非开源gp官网的postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg，一层层zip、tar解压打开，找到postgis.rpm中的./temp/share/postgresql/contrib/postgis-2.1/postgis_manager.sh
参照着写一个
cd /usr/local/gpdb/share/postgresql/contrib/postgis-2.0
vi postgis_manager.sh
#!/bin/bash -l

if [ “$2” = “install” ]
then
psql -d $1 -f $GPHOME/share/postgresql/contrib/postgis-2.0/postgis.sql;
psql -d $1 -f $GPHOME/share/postgresql/contrib/postgis-2.0/rtpostgis.sql;

psql -d $1 -f $GPHOME/share/postgresql/contrib/postgis-2.0/postgis_comments.sql;

psql -d $1 -f $GPHOME/share/postgresql/contrib/postgis-2.0/raster_comments.sql;
psql -d $1 -f $GPHOME/share/postgresql/contrib/postgis-2.0/spatial_ref_sys.sql;

else
echo “Invalid option. Please try install, upgrade or uninstall”
fi

启用某数据库的postgis
chmod 777 -R postgis_manager.sh
$GPHOME/share/postgresql/contrib/postgis-2.0/postgis_manager.sh mydatabase install

安装成功后，库中会出现spatial_ref_sys表

整个包压缩，准备拿到集群上安装尝试
tar cvzf gpdb-bin-5.1.tar.gz /usr/local/gpdb/