检查PostGIS、PostgreSQL、GEOS、GDAL、PROJ等各软件的版本依赖关系
http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS
1. 创建postgres用户和组
# groupadd -g 101 dba # useradd -u 501 -g dba -G root -d /usr/local/pgsql postgres
2. 添加postgres用户环境变量
$ cat ~/.bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # 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=1 vm.overcommit_ratio=90 fs.aio-max-nr=1048576 fs.file-max= 7672460 net.ipv4.ip_local_port_range=9000 65500 net.core.rmem_default=262144 net.core.rmem_max=4194304 net.core.wmem_default=262144 net.core.wmem_max=1048586 kernel.sem= 50100 64128000 50100 1280 kernel.shmall=5242880 kernel.shmmax=12884901888 # tail -n 4 /etc/security/limits.conf postgres soft nproc 8192 postgres hard nproc 16384 postgres soft nofile 4096 postgres hard nofile 65536
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字符，超过的长度会比截取掉。
注：如果缺少部分系统包的话可以通过如下网址查找 https://centos.pkgs.org/
$ 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 file name pattern, $ cat /usr/local/pgsql/data/pg_hba.conf | grep -v "^$" | grep -v "^#" local all all trust host all all 127.0.0.1/32 trust host all all 0.0.0.0/0 md5 host all all ::1/128 trust $ pg_ctl start -W $ psql psql (9.5.7) Type "help" for help. postgres=# select version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 9.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 configured for 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.1 GDAL config: /usr/local/pgsql/plugin/gdal/bin/gdal-config GDAL version: 2.1.2 PostgreSQL config: /usr/local/pgsql/bin/pg_config PostgreSQL version: PostgreSQL 9.5.7 PROJ4 version: 49 Libxml2 config: /usr/bin/xml2-config Libxml2 version: 2.7.6 JSON-C support: no PCRE support: no PostGIS debug level: 0 Perl: /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=# CREATE EXTENSION postgis; CREATE EXTENSION postgres=# CREATE EXTENSION postgis_topology; CREATE EXTENSION postgres=# CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION postgres=# CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION postgres=# dx List of installed extensions Name | Version | Schema | Description ------------------------+---------+------------+--------------------------------------------------------------------- fuzzystrmatch | 1.0 | public | determine similarities and distance between strings plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language postgis | 2.2.5 | public | PostGIS geometry, geography, and raster spatial types and functions postgis_tiger_geocoder | 2.2.5 | tiger | PostGIS tiger geocoder and reverse geocoder postgis_topology | 2.2.5 | topology | PostGIS topology spatial types and functions (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 whitespace in 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 will wait for postgres to start. Note that # PGSTARTTIMEOUT should be less than 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 OOM kill on the postmaster OOMScoreAdjust=-1000 Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj Environment=PG_OOM_ADJUST_VALUE=0 ExecStart=/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 not kill postmaster # during crash recovery. TimeoutSec=0 [Install] WantedBy=multi-user.target