• PostgreSQL13+PostGIS31安装手册


    之前整理的是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)
  • 相关阅读:
    Appium学习笔记||十一、Appium按键操作
    Appium学习笔记||九、操作不可见元素
    Appium学习笔记||八、滑动页面
    Appium学习笔记||七、用UIAutomator API定位
    Appium学习笔记||六、根据Xpath寻找元素
    Appium学习笔记||五、Tap方法
    enumerate 函数
    python 复制 浅copy 深copy 的区别
    听歌都能听出来代码...
    for 循环时候 pop现象
  • 原文地址:https://www.cnblogs.com/ilifeilong/p/14253883.html
Copyright © 2020-2023  润新知