之前整理的是PostgreSQL9和PostGIS2的安装手册,最近在安装PostgreSQL13时发现由于版本过新,安装步骤略有不同,这里简单记录一下安装过程.
一 各软件包版本依赖关系检查
检查PostGIS、PostgreSQL、GEOS、GDAL、PROJ等各软件的版本依赖关系
http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS
二. 版本选择
- 系统版本
CentOS Linux release 7.7.1908 (Core)
- PostgreSQL13.1
https://ftp.postgresql.org/pub/source/v13.1/postgresql-13.1.tar.gz
- PostGIS
https://download.osgeo.org/postgis/source/postgis-3.1.0.tar.gz
- GEOS
http://download.osgeo.org/geos/geos-3.9.0.tar.bz2
- proj
https://download.osgeo.org/proj/proj-7.2.0.tar.gz
- gdal
https://github.com/OSGeo/gdal/releases/download/v3.2.0/gdal-3.2.0.tar.gz
- sqlite3(系统自带版本低于proj安装最低版本要求,所以我们自己重新编译新的sqlite3版本)
https://www.sqlite.org/snapshot/sqlite-snapshot-202101022356.tar.gz
三 系统配置
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 libtiff libtiff-devel curl-devel
三 PostgreSQL数据库安装
$ mkdir /usr/local/pgsql $ mkdir /usr/local/pgsql/{data,arch,plugin} $ chown -R postgres.dba /usr/local/pgsql $ tar -zxf postgresql-13.1.tar.gz $ cd postgresql-13.1 $ ./configure --prefix=/usr/local/pgsql --with-segsize=32 --with-tcl --with-perl --with-python --with-gssapi --with-pam --with-openssl --with-libxml --with-libxslt $ make world $ make install-world $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/ $ cat /usr/local/pgsql/data/postgresql.conf | grep -v "^$" | grep -v "^#" listen_addresses = '*' port = 5432 max_connections = 600 superuser_reserved_connections = 3 unix_socket_directories = '/tmp' shared_buffers = 16GB huge_pages = try temp_buffers = 256MB work_mem = 256MB maintenance_work_mem = 256MB logging_collector = on log_directory = 'log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_file_mode = 0600 log_rotation_size = 10MB $ 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
使用systemctl来管理PostgreSQL服务
# cat /usr/lib/systemd/system/pgsql.service [Unit] Description=PostgreSQL database server Documentation=https://www.postgresql.org/docs/ After=syslog.target After=network.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
# systemctl daemon-reload # systemctl start pgsql # systemctl enable pgsql # su - postgres $ psql psql (13.1) Type "help" for help. postgres=#
四 安装PostGIS扩展
1. 安装sqlite3
$ tar -zxf sqlite-snapshot-202101022356.tar.gz $ cd sqlite-snapshot-202101022356 $ ./configure --prefix=/usr/local/pgsql/plugin/sqlite $ make $ make install $ /usr/local/pgsql/plugin/sqlite/bin/sqlite3 --version 3.35.0 2021-01-02 23:56:37 203c049c662380411522d0c7c493201331bbb2792a7c5b12684f04f532a0695d
2. 安装proj
$ tar -zxf proj-7.2.0.tar.gz $ cd proj-7.2.0 $ ./configure --prefix=/usr/local/pgsql/plugin/proj SQLITE3_CFLAGS=-I/usr/local/pgsql/plugin/sqlite/include SQLITE3_LIBS="-L/usr/local/pgsql/plugin/sqlite/lib -lsqlite3" $ make $ make install $ echo "/usr/local/pgsql/plugin/proj/lib" > /etc/ld.so.conf.d/proj.conf $ ldconfig
3. 安装geos
$ tar -jxf geos-3.9.0.tar.bz2 $ cd geos-3.9.0 $ ./configure --prefix=/usr/local/pgsql/plugin/geos $ make $ make install $ echo "/usr/local/pgsql/plugin/geos/lib" > /etc/ld.so.conf.d/geos.conf $ ldconfig
4. 安装gdal
$ tar -zxf gdal-3.2.0.tar.gz $ cd gdal-3.2.0 $ ./configure --prefix=/usr/local/pgsql/plugin/gdal --with-proj=/usr/local/pgsql/plugin/proj $ make $ make install $ echo "/usr/local/pgsql/plugin/gdal/lib" > /etc/ld.so.conf.d/gdal.conf $ ldconfig
5. 安装PostGIS
$ tar -zxf postgis-3.1.0.tar.gz $ cd postgis-3.1.0 $ ./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 --without-protobuf $ make $ make install
6. 创建PostgGIS扩展
postgres=# c template1 template1=# CREATE EXTENSION postgis; template1=# CREATE EXTENSION postgis_raster; template1=# CREATE EXTENSION postgis_sfcgal; template1=# CREATE EXTENSION fuzzystrmatch; template1=# CREATE EXTENSION address_standardizer; template1=# CREATE EXTENSION address_standardizer_data_us; template1=# CREATE EXTENSION postgis_tiger_geocoder; template1=# CREATE EXTENSION postgis_topology; template1=# dx List of installed extensions Name | Version | Schema | Description ------------------------------+---------+------------+--------------------------------------------------------------------------------------------------------------------- address_standardizer | 3.1.0 | public | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step. address_standardizer_data_us | 3.1.0 | public | Address Standardizer US dataset example fuzzystrmatch | 1.1 | public | determine similarities and distance between strings hstore | 1.7 | public | data type for storing sets of (key, value) pairs plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language postgis | 3.1.0 | public | PostGIS geometry and geography spatial types and functions postgis_raster | 3.1.0 | public | PostGIS raster types and functions postgis_tiger_geocoder | 3.1.0 | tiger | PostGIS tiger geocoder and reverse geocoder postgis_topology | 3.1.0 | topology | PostGIS topology spatial types and functions (9 rows)