mysql gis postgre_PostgreSQL&PostGIS完全安装

检查PostGIS、PostgreSQL、GEOS、GDAL、PROJ等各软件的版本依赖关系

1. 创建postgres用户和组

# groupadd -g 101dba

# useradd-u 501 -g dba -G root -d /usr/local/pgsql postgres

2. 添加postgres用户环境变量

$ cat ~/.bash_profile

# .bash_profile

# Get the aliases and functionsif [ -f ~/.bashrc ]; then.~/.bashrcfi# User specific environment and startup programs

export PGHOME=/usr/local/pgsql

export PGDATA=/usr/local/pgsql/data

export PATH=$PGHOME/bin:$PATH:$HOME/bin

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH

export DATE=`date +"%Y%m%d%H%M"`

export HISTTIMEFORMAT="%Y-%m-%d %H:%M:%S>"

3. 调整系统参数

# tail -n 12 /etc/sysctl.conf

vm.overcommit_memory=1vm.overcommit_ratio=90fs.aio-max-nr=1048576fs.file-max= 7672460net.ipv4.ip_local_port_range=9000 65500net.core.rmem_default=262144net.core.rmem_max=4194304net.core.wmem_default=262144net.core.wmem_max=1048586kernel.sem= 50100 64128000 50100 1280kernel.shmall=5242880kernel.shmmax=12884901888#tail -n 4 /etc/security/limits.conf

postgres soft nproc8192postgres hard nproc16384postgres soft nofile4096postgres hard nofile65536

4. 安装依赖包

# yum install -y python-devel perl-ExtUtils-Embed python-devel gcc-c++ openssl-devel readline readline-devel bzip2 zlib zlib-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel openldap openldap-devel libgeos-dev libproj-dev libgdal-dev xsltproc docbook-xsl docbook-xml imagemagick libmagickcore-dev dblatex tcl tcl-devel unixODBC unixODBC-devel libpng12 libpng12-devel

5. 安装PostgreSQL

修改源码文件src/include/pg_config_manual.h中 "#define NAMEDATALEN"的值为256，这一步不是必须的，修改值是因为遇到过R.D的同事要建立的schema长度大于64个字符，但是pG默认是64字符，超过的长度会比截取掉。

$ wget https://ftp.postgresql.org/pub/source/v9.5.7/postgresql-9.5.7.tar.bz2

$ tar -jxf postgresql-9.5.7.tar.bz2

$ cd postgresql-9.5.7$ ./configure --prefix=/usr/local/pgsql --with-wal-segsize=32 --with-perl --with-python --with-gssapi --with-pam --with-ldap --with-openssl --with-tcl --with-libxml --with-libxslt

$make$make install$ cd contrib/$make && make install$mkdir /usr/local/pgsql/{data,arch,plugin}

$sudo echo "su - postgres -c 'pg_ctl start -D /usr/local/pgsql/data'" >> /etc/rc.local

$/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/$cat /usr/local/pgsql/data/postgresql.conf | grep -v "^$" | grep -v "^#"listen_addresses= '*'# what IP address(es) to listen on;

port= 5432# (change requires restart)

max_connections= 300# (change requires restart)

shared_buffers=2048MB # min 128kB

logging_collector=on # Enable capturing of stderr and csvlog

log_directory= 'pg_log'# directory where log files are written,

log_filename= 'postgresql-%Y-%m-%d_%H%M%S.log' # log filename pattern,

$cat /usr/local/pgsql/data/pg_hba.conf | grep -v "^$" | grep -v "^#"local all all trust

host all all127.0.0.1/32trust

host all all0.0.0.0/0md5

host all all ::1/128trust

$ pg_ctl start-W

$ psql

psql (9.5.7)

Type"help" forhelp.

postgres=# selectversion();

version----------------------------------------------------------------------------------------------------------PostgreSQL9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit

(1 row)

6.安装PostGIS

6.1 安装Proj4

$ wget http://download.osgeo.org/proj/proj-4.9.3.tar.gz

$ tar -xf proj-4.9.3.tar.gz

$ cd proj-4.9.3$ ./configure --prefix=/usr/local/pgsql/plugin/proj

$make$make install#echo "/usr/local/pgsql/plugin/proj/lib" > /etc/ld.so.conf.d/proj-4.9.3.conf

# ldconfig

6.2 安装GEOS

$ wget http://download.osgeo.org/geos/geos-3.6.1.tar.bz2

$ tar -jxf geos-3.6.1.tar.bz2

$ cd geos-3.6.1$ ./configure --prefix=/usr/local/pgsql/plugin/geos

$make$make install#echo "/usr/local/pgsql/plugin/geos/lib" > /etc/ld.so.conf.d/geos-3.6.1.conf

# ldconfig

6.3 安装GDAL

$ wget http://download.osgeo.org/gdal/2.1.2/gdal-2.1.2.tar.gz

$ tar -xf gdal-2.1.2.tar.gz

$ cd gdal-2.1.2$ ./configure --prefix=/usr/local/pgsql/plugin/gdal

$make$make install#echo "/usr/local/pgsql/plugin/gdal/lib" > /etc/ld.so.conf.d/gdal-2.1.2.conf

# ldconfig

6.4 安装PostGIS

$ wget http://download.osgeo.org/postgis/source/postgis-2.2.5.tar.gz

$ tar -xf postgis-2.2.5.tar.gz

$ cd postgis-2.2.5$ ./configure --prefix=/usr/local/pgsql/plugin/postgis --with-pgconfig=/usr/local/pgsql/bin/pg_config --with-geosconfig=/usr/local/pgsql/plugin/geos/bin/geos-config --with-gdalconfig=/usr/local/pgsql/plugin/gdal/bin/gdal-config --with-projdir=/usr/local/pgsql/plugin/proj

..............................................................................................

PostGIS is now configuredfor x86_64-pc-linux-gnu-------------- Compiler Info -------------C compiler:gcc -g -O2

SQL preprocessor:/usr/bin/cpp -traditional-cpp -w -P-------------- Dependencies --------------GEOS config:/usr/local/pgsql/plugin/geos/bin/geos-config

GEOS version:3.6.1GDAL config:/usr/local/pgsql/plugin/gdal/bin/gdal-config

GDAL version:2.1.2PostgreSQL config:/usr/local/pgsql/bin/pg_config

PostgreSQL version: PostgreSQL9.5.7PROJ4 version:49Libxml2 config:/usr/bin/xml2-config

Libxml2 version:2.7.6JSON-C support: no

PCRE support: no

PostGIS debug level:0Perl:/usr/bin/perl

--------------- Extensions ---------------PostGIS Raster: enabled

PostGIS Topology: enabled

SFCGAL support: disabled

Address Standardizer support: disabled-------- Documentation Generation --------xsltproc:/usr/bin/xsltproc

xsl style sheets:/usr/share/sgml/docbook/xsl-stylesheets

dblatex:

convert:

mathml2.dtd: http://www.w3.org/Math/DTD/mathml2/mathml2.dtd

$ make$make install

7. 检查PostGiS是否安装成功

postgres=# CREATE EXTENSION hstore;

postgres=# CREATEEXTENSION postgis;CREATEEXTENSION

postgres=# CREATEEXTENSION postgis_topology;CREATEEXTENSION

postgres=# CREATEEXTENSION fuzzystrmatch;CREATEEXTENSION

postgres=# CREATEEXTENSION postgis_tiger_geocoder;CREATEEXTENSION

postgres=# \dx

Listofinstalled extensions

Name| Version | Schema |Description------------------------+---------+------------+---------------------------------------------------------------------

fuzzystrmatch | 1.0 | public | determine similarities and distance betweenstrings

plpgsql| 1.0 | pg_catalog | PL/pgSQL procedural language

postgis| 2.2.5 | public | PostGIS geometry, geography, and raster spatial types andfunctions

postgis_tiger_geocoder| 2.2.5 | tiger | PostGIS tiger geocoder and reversegeocoder

postgis_topology| 2.2.5 | topology | PostGIS topology spatial types andfunctions

(5 rows)

8.将PostgreSQL配置为系统服务

[Unit]

Description=PostgreSQL database server

Documentation=https://www.postgresql.org/docs/

After=syslog.target

After=network.target

Wants=network-online.target

[Service]

Type=forking

User=postgres

Group=dba

Restart=always

LimitNOFILE=65536# Note: avoid inserting whitespacein these Environment=lines, or you may

# break postgresql-setup.

# Location of database directory

Environment=PGDATA=/usr/local/pgsql/data/# Maximum number of seconds pg_ctl willwait forpostgres to start. Note that

# PGSTARTTIMEOUT should belessthan TimeoutSec value.

Environment=PGSTARTTIMEOUT=200# Where to send early-startup messages from the server (before the logging

# options of postgresql.conf take effect)

# This is normally controlled by the global default set by systemd

# StandardOutput=syslog

# Disable OOMkillon the postmaster

OOMScoreAdjust=-1000Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj

Environment=PG_OOM_ADJUST_VALUE=0ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA} -s -w -t ${PGSTARTTIMEOUT}

ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast

ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s

KillMode=mixed

KillSignal=SIGINT

# Do not set any timeout value, so that systemd will notkillpostmaster

# during crash recovery.

TimeoutSec=0[Install]

WantedBy=multi-user.target