软件版本：
PostgreSQL-11.2 gdal-2.2.3 proj-4.8.0 geos-3.5.0 json-c-0.13.1 protobuf-2.6.1 protobuf-c-1.2.1 postgis-2.5.0
一、源码安装pg11.2
用户配置
创建安装用户 groupadd dba -g 1000 useradd postgres -g 1000 -u 1000
安装依赖
yum install -y openssl openssl-devel readline readline-devel zlib-devel gcc gcc-c++ libxml2 libxml2-devel flex
切换到postgres用户
su - postgres
获取并解压包
wget https://ftp.postgresql.org/pub/source/v11.2/postgresql-11.2.tar.gz tar -xf postgresql-11.2.tar.gz
进入目录
cd postgresql-11.2
编译环境检查
./configure --prefix=/home/postgres/soft --with-openssl --with-pgport=6000 (最后出现makefile)
编译安装
make world -j24 make install-world -j24
初始化数据库数据目录到/home/postgres/data
/home/postgres/soft/bin/initdb -D /home/postgres/data -E UTF8
启动数据库
/home/postgres/soft/bin/pg_ctl start -D /home/postgres/data -l /tmp/logfile
配置环境变量.bashrc
vi .bashrc
export PGPORT=6000
export PGUSER=postgres
export PGHOME=/home/postgres/soft
export PGDATA=/home/postgres/data
export PATH=${PGHOME}/bin:${PATH}
LD_LIBRARY_PATH=$PGHOME/lib:/usr/local/lib:/usr/local/lib64:/usr/lib64:$LD_LIBRARY_PATH
–读取环境变量
source .bashrc
数据库配置
su - postgres
mkdir -p /home/postgres/archive/wal
cluster_name='enmo_6001' listen_addresses='0.0.0.0' port=6000 wal_log_hints=on logging_collector=on logging_collector=on log_filename='pg_log_%u.log' log_file_mode=0600 log_truncate_on_rotation=on log_rotation_age=1d wal_keep_segments=100 archive_mode = on archive_command = 'cp %p /home/postgres/archive/wal/%f' wal_level = 'logical' max_wal_senders = 10 max_replication_slots = 10 hot_standby = on client_encoding='UTF8' lc_messages = 'en_US.UTF-8' lc_monetary = 'en_US.UTF-8' lc_numeric = 'en_US.UTF-8' lc_time = 'en_US.UTF-8
修改参数，其余参数根据环境进行调整。
重启数据库生效
pg_ctl restart -D $PGDATA -l /tmp/logfile
二、postgis安装
mkdir /home/postgres/postgis cd /home/postgres/postgis
1.安装gdal
wget http://download.osgeo.org/gdal/2.2.3/gdal-2.2.3.tar.gz tar xf gdal-2.2.3.tar.gz cd gdal-2.2.3 ./configure make -j 24 make install -j 24
2.安装proj
wget http://download.osgeo.org/proj/proj-4.8.0.tar.gz tar xf proj-4.8.0.tar.gz cd proj-4.8.0 ./configure export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig export LD_LIBRARY_PATH=/usr/local/lib make -j 24 make install -j 24
3.安装geos
wget http://download.osgeo.org/geos/geos-3.5.0.tar.bz2 tar jxvf geos-3.5.0.tar.bz2 cd geos-3.5.0 ./autogen.sh ./configure make -j 24 make install -j 24
4.安装json-c
wget https://s3.amazonaws.com/json-c_releases/releases/json-c-0.13.1.tar.gz tar -xf json-c-0.13.1.tar.gz ./autogen.sh ./configure make -j 24 make install -j 24
5.安装protobuf-c
wget https://github.com/google/protobuf/releases/download/v2.6.1/protobuf-2.6.1.tar.gz tar xvf protobuf-2.6.1.tar.gz cd protobuf-2.6.1 ./configure make -j 24 make check make install -j 24 wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.2.1/protobuf-c-1.2.1.tar.gz tar xvf protobuf-c-1.2.1.tar.gz cd protobuf-c-1.2.1 export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig // 指定protobuf.pc文件所在 ./configure make -j 24 make install -j 24
6.配置ldconfig
将数据库软件、gdal、proj、geos的lib目录添加到ldconfig。
cat /etc/ld.so.conf include ld.so.conf.d/*.conf /usr/local/gdal/lib/ /usr/local/proj/lib/ /usr/local/geos/lib/ /home/postgres/soft/lib/ /usr/local/lib #保存退出 ldconfig
7.安装postgis(./configure对应目录需校对)
wget http://download.osgeo.org/postgis/source/postgis-2.5.0.tar.gz tar -xf postgis-2.5.0.tar.gz cd postgis-2.5.0/ ./configure -prefix=/usr/local/postgis --with-geosconfig=/usr/local/bin/geos-config --with-projdir=/usr/local/proj --with-gdalconfig=/usr/local/bin/gdal-config --with-pgconfig=/home/postgres/soft/bin/pg_config make -j 24 make install -j 24
8.检查postgis组件是否安装
ls /home/postgres/soft/share/extension/postgis*
9.使用超级用户创建扩展
[postgres@ysl ~]$psql -Upostgres postgres create extension postgis; create extension postgis_topology; create extension fuzzystrmatch; create extension postgis_tiger_geocoder;
三、测试
postgres=# select postgis_full_version();
postgres=# SELECT ST_SetSRID(ST_Point(-87.71,43.741),4326),ST_GeomFromText('POINT(-87.71 43.741)',4326);
四、创建空间数据库模板
1.模板及相关的函数，类型，操作符创建
# 创建无空间特性数据库 [postgres@ysl ~]$ createdb template_postgis # 创建相关空间数据库相关的函数，类型，操作符等 [postgres@ysl ~]$ psql -f /home/postgres/soft/share/contrib/postgis-2.5/postgis.sql -d template_postgis [postgres@ysl ~]$ psql -f /home/postgres/soft/share/contrib/postgis-2.5/rtpostgis.sql -d template_postgis # 验证空间数据库版本 [postgres@ysl ~]$ psql template_postgis psql (11.2) Type "help" for help. template_postgis=# select postgis_full_version(); postgis_full_v ersion --------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- POSTGIS="2.5.0 r16836" PGSQL="110" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2 012" GDAL="GDAL 2.2.3, released 2017/11/20 GDAL_DATA not found" LIBXML="2.9.1" RASTER (1 row) template_postgis=# \d List of relations Schema | Name | Type | Owner --------+-------------------+-------+---------- public | geography_columns | view | postgres public | geometry_columns | view | postgres public | raster_columns | view | postgres public | raster_overviews | view | postgres public | spatial_ref_sys | table | postgres (5 rows)
2.根据空间数据库模板创建新的空间数据库
[postgres@ysl ~]$ createdb -T template_postgis new_database template_postgis=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ------------------+----------+----------+-------------+-------------+----------------------- new_database | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template_postgis | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (5 rows)
3.测试
测试点(0, 0)是否在指定的多边形内。
new_database=# select ST_Within(ST_GeomFromText('POINT(0 0)', 4326), ST_GeomFromText('POLYGON((1 1, 1 -1, -1 -1, -1 1, 1 1))', 4326)) ;
st_within
-----------
t
(1 row)
