PostgreSQL13+PostGIS31安装手册 [数据库教程]

Z时代
2024-01-10
分类：综合

database

之前整理的是PostgreSQL9和PostGIS2的安装手册,最近在安装PostgreSQL13时发现由于版本过新,安装步骤略有不同,这里简单记录一下安装过程.

一各软件包版本依赖关系检查

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

http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS

二. 版本选择

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=900065500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586
kernel.sem= 5010064128000501001280
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
$ makeinstall-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 waitfor 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
$ makeinstall

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
$ makeinstall
$ 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
$ makeinstall
$ 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
$ makeinstall
$ 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
$ makeinstall

6. 创建PostgGIS扩展

postgres=# c template1
template1=# CREATE EXTENSION hstore;
CREATE EXTENSION
template1=# CREATE EXTENSION postgis;
CREATE EXTENSION
template1=# CREATE EXTENSION postgis_topology;
CREATE EXTENSION
template1=# CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
template1=# CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
template1=# dx
                                        List of installed extensions
          Name          | Version |   Schema   |                        Description                         
------------------------+---------+------------+------------------------------------------------------------
 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_tiger_geocoder | 3.1.0   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 3.1.0   | topology   | PostGIS topology spatial types and functions
(6 rows)