PostgreSQL 16搭配PostGIS 3.5是地理空间数据处理的黄金组合。前者作为顶尖开源关系型数据库在16版本中性能、并发能力再升级后者则是权威的地理空间扩展3.5版本新增多项空间函数与格式支持。本文将带你从零开始一步步完成二者的编译安装解锁高效处理地理空间数据的强大能力。说明该文章为 centos/rhel 系操作系统教程Ubuntu系安装请参考另一篇postgresql编译安装postgresql中文文档http://www.postgres.cn/docs/16/index.html安装编译环境添加epel源# yum -y install epel-release安装依赖包和gcc等编译工具# yum -y install gcc gcc-c wget llvm llvm-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python-devel bzip2 gmp-devel mpfr-devel uuid uuid-devel pcre pcre-devel或8.0版本# yum -y install gcc gcc-c wget llvm llvm-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python3-devel bzip2 gmp-devel mpfr-devel uuid uuid-devel pcre pcre-develAlma Linux8.0# yum -y install gcc gcc-c wget llvm llvm-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python3-devel bzip2 gmp-devel mpfr-devel libuuid libuuid-devel pcre pcre-develAlma Linux9.0添加 CRB (CodeReady Builder) 仓库是一个为开发者提供的额外软件库 # dnf config-manager --set-enabled crb ​ # yum -y install gcc gcc-c wget llvm llvm-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python3-devel bzip2 gmp-devel mpfr-devel uuid uuid-devel krb5-devel perl-Opcode pcre pcre-devel ​ 或 ​ # yum -y install gcc gcc-c wget llvm llvm-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python3-devel bzip2 gmp-devel mpfr-devel libuuid libuuid-devel krb5-devel perl-Opcode pcre pcre-develRocky Linux8.0添加 CRB (CodeReady Builder) 仓库是一个为开发者提供的额外软件库 # dnf config-manager --set-enabled crb ​ # yum -y install gcc gcc-c wget llvm llvm-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python3-devel bzip2 gmp-devel mpfr-devel libuuid libuuid-devel pcre pcre-develRocky Linux9.0添加 CRB (CodeReady Builder) 仓库是一个为开发者提供的额外软件库 # dnf config-manager --set-enabled crb ​ # yum -y install gcc gcc-c wget llvm llvm-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python3-devel bzip2 gmp-devel mpfr-devel libuuid libuuid-devel krb5-devel perl-Opcode pcre pcre-devel银河麒麟高级服务器操作系统V10# yum -y install gcc gcc-c wget llvm llvm-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python3-devel bzip2 gmp-devel mpfr-devel libuuid libuuid-devel pcre pcre-devel perl-devel注部分系统还需安装 pcre pcre-devel 如openeuler、Anolis编译安装postgresql创建用户# useradd postgres # passwd postgres创建安装目录# mkdir /data/pgsql16 -p下载源码包下载地址https://ftp.postgresql.org/pub/source/此处使用16.10解压源码包# tar -zxvf postgresql-16.10.tar.gz初始化# cd postgresql-16.10/ # ./configure --prefix/data/pgsql16 --enable-nls --with-python --with-tcl --with-gssapi --with-icu --with-openssl --with-pam --with-ldap --with-systemd --with-libxml --with-libxslt --with-perl --enable-thread-safety --enable-debug --with-ossp-uuid如果安装的是libuuid libuuid-devel则为# ./configure --prefix/data/pgsql16 --enable-nls --with-python --with-tcl --with-gssapi --with-icu --with-openssl --with-pam --with-ldap --with-systemd --with-libxml --with-libxslt --with-perl --enable-thread-safety --enable-debug --with-uuide2fs编译 安装# make # make install注如想编译安装所有可编译的执行以下操作# make # make world ​ # make install # make install-docs # make install-world安装contrib目录下的一些工具是第三方组织的一些工具代码# cd contrib # make # make install编辑环境变量# vim /home/postgres/.bash_profile 添加下列内容 PGHOME/data/pgsql16 PGDATA/data/pgdata PATH$PGHOME/bin:$PATH export LD_LIBRARY_PATH$PGHOME/lib export PGDATA PATH PGHOME创建数据目录# mkdir /data/pgdata -p修改所属人所属组# chown postgres:postgres -R /data/{pgsql16,pgdata}切换到postgres用户# su - postgres初始化postgresql$ /data/pgsql16/bin/initdb -D /data/pgdata/创建日志存放目录$ mkdir /data/pgdata/logs启动postgresql$ /data/pgsql16/bin/pg_ctl -D /data/pgdata/ -l /data/pgdata/logs/logfile start常用命令如下pg_ctl 命令-- 启动、停止、重启 PostgreSQL该命令详细信息如下 pg_ctl start [-w] [-s] [-D datadir] [-l filename] [-o options] [-p path] pg_ctl stop [-W] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] pg_ctl restart [-w] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] [-o options] pg_ctl reload [-s] [-D datadir] pg_ctl status [-D datadir] pg_ctl kill [signal_name] [process_id] pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-w] [-o options] pg_ctl unregister [-N servicename]登录postgresql# su - postgres 切换到postgres用户 $ psql //登录postgresql postgres# ALTER ROLE postgres WITH PASSWORD password; //修改postgres用户密码修改配置# vim /data/pgdata/postgresql.conf ...... listen_addresses * //监听地址*号代表监听本机所有ip ...... 端口默认为5432 连接数默认为100 ​ password_encryption scram-sha-256 //密码加密策略 ​ 设置日志管理及自动轮滚 logging_collector on log_directory logs log_filename postgresql-%a.log log_truncate_on_rotation on log_rotation_age 1d log_rotation_size 0修改认证方式# vim /data/pgdata/pg_hba.conf ...... # local is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust //本机127.0.0.1允许免密登录 host all all 0.0.0.0/0 scram-sha-256 # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust切换至postgres用户重载postgresql配置# su - postgres $ /data/pgsql16/bin/pg_ctl reload配置为systemd管理先停止postgresql服务# su - postgres 切换到postgres用户 $ /data/pgsql16/bin/pg_ctl stop 停止postgresql服务切换回root用户编辑.service文件# vim /usr/lib/systemd/system/postgresql.service [Unit] DescriptionPostgreSQL database server Documentationman:postgres(1) Afternetwork-online.target Wantsnetwork-online.target ​ [Service] Typenotify Userpostgres Grouppostgres ExecStart/data/pgsql16/bin/postgres -D /data/pgdata ExecReload/bin/kill -HUP $MAINPID KillModemixed KillSignalSIGINT Restarton-failure TimeoutSec300 LimitNOFILE65535 LimitNPROC8192 ​ [Install] WantedBymulti-user.target启动postgresql# systemctl start postgresql.service编译安装PostGIS安装的PostGIS为3.5.4依赖的库有geosprojgdallibxmljson-cprotobuf如果要支持三维需安装sfcgal如果要做路网分析需安装pgrouting可单独安装。依赖版本关系参考https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS创建依赖安装目录# mkdir /data/pgplugin安装geos需要cmake编译需先安装下cmake# wget https://github.com/Kitware/CMake/releases/download/v3.31.10/cmake-3.31.10.tar.gz # tar -zxvf cmake-3.31.10.tar.gz # cd cmake-3.31.10 ​ 安装 # ./configure --prefix/data/pgplugin/cmake # make # make install ​ 编辑环境变量 # vim /etc/profile 修改内容如下 export CMAKE_HOME/data/pgplugin/cmake export PATH$CMAKE_HOME/bin:$PATH ​ # source /etc/profile# wget http://download.osgeo.org/geos/geos-3.14.1.tar.bz2 # tar -jxvf geos-3.14.1.tar.bz2 # cd geos-3.14.1 ​ 安装 # mkdir build cd build # cmake -DCMAKE_INSTALL_PREFIX/data/pgplugin/geos .. # make # make install ​ # echo /data/pgplugin/geos/lib64 /etc/ld.so.conf.d/pgplugin.conf # ldconfig安装proj本次安装proj9.7.1依赖较高版本的sqlite先安装sqllite # wget https://www.sqlite.org/2026/sqlite-autoconf-3510200.tar.gz # tar -zxvf sqlite-autoconf-3510200.tar.gz # cd sqlite-autoconf-3510200 # ./configure --prefix/data/pgplugin/sqlite --enable-rtree --enable-shared --enable-static # make # make install ​ 替换系统旧的sqllite # mv /usr/bin/sqlite3 /usr/bin/sqlite3_old # ln -s /data/pgplugin/sqlite/bin/sqlite3 /usr/bin/sqlite3 # sqlite3 --version 3.51.2 2026-01-09 17:27:48 b270f8339eb13b504d0b2ba154ebca966b7dde08e40c3ed7d559749818cb2075 (64-bit) ​ # echo /data/pgplugin/sqlite/lib /etc/ld.so.conf.d/pgplugin.conf # ldconfig ​ 将pkg_config暴露出去避免proj找不到sqlite的问题 # export PKG_CONFIG_PATH/data/pgplugin/sqlite/lib/pkgconfig:$PKG_CONFIG_PATH然后再安装proj9.7.1# yum -y install libtiff libtiff-devel # yum -y install libcurl libcurl-devel ​ # wget http://download.osgeo.org/proj/proj-9.7.1.tar.gz # tar -zxvf proj-9.7.1.tar.gz # cd proj-9.7.1 ​ 安装 # mkdir build cd build # cmake -DCMAKE_INSTALL_PREFIX/data/pgplugin/proj .. # make # make install ​ # echo /data/pgplugin/proj/lib64 /etc/ld.so.conf.d/pgplugin.conf # ldconfig低版本proj如proj8.2.1则为# ./configure --prefix/data/pgplugin/proj # make # make install ​ # echo /data/pgplugin/proj/lib /etc/ld.so.conf.d/pgplugin.conf # ldconfig注银河麒麟V10服务器版需使用低版本 proj 8.2.1安装json-c# wget https://github.com/json-c/json-c/archive/json-c-json-c-0.14-20200419.tar.gz # tar -zxvf json-c-json-c-0.14-20200419.tar.gz # cd json-c-json-c-0.14-20200419/ ​ 安装 # mkdir build cd build # cmake -DCMAKE_INSTALL_PREFIX/data/pgplugin/json-c .. # make # make install ​ # echo /data/pgplugin/json-c/lib64 /etc/ld.so.conf.d/pgplugin.conf # ldconfig安装libxml下载地址http://xmlsoft.org/sources/https://github.com/GNOME/libxml2/ 最新# yum -y install autoconf automake libtool libsysfs # tar -zxvf libxml2-2.9.14.tar.gz # cd libxml2-2.9.14/ ​ 安装 # ./autogen.sh # ./configure --prefix/data/pgplugin/libxml2 # make # make install ​ # echo /data/pgplugin/libxml2/lib /etc/ld.so.conf.d/pgplugin.conf # ldconfig安装protobuf# wget https://github.com/protocolbuffers/protobuf/releases/download/v3.11.4/protobuf-all-3.11.4.tar.gz ​ # tar -zxvf protobuf-all-3.11.4.tar.gz # cd protobuf-3.11.4/ # ./autogen.sh 如果有configure则不需要这步 ​ 安装 # ./configure --prefix/data/pgplugin/protobuf # make # make install ​ 编辑环境变量 # vim /etc/profile 添加以下内容 export PKG_CONFIG_PATH/data/pgplugin/protobuf/lib/pkgconfig export PROTOBUF_HOME/data/pgplugin/protobuf export PATH$PROTOBUF_HOME/bin:$PATH ​ # source /etc/profile ​ 验证 # protoc --version libprotoc 3.11.4 ​ # echo /data/pgplugin/protobuf/lib /etc/ld.so.conf.d/pgplugin.conf # ldconfig安装protobuf-c# wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.3.3/protobuf-c-1.3.3.tar.gz # tar -zxvf protobuf-c-1.3.3.tar.gz # cd protobuf-c-1.3.3 ​ 安装 如果之前没设置PKG_CONFIG_PATH变量需提前设置 export PKG_CONFIG_PATH/data/pgplugin/protobuf/lib/pkgconfig ​ # ./configure --prefix/data/pgplugin/protobuf-c # make # make install ​ 编辑环境变量 # vim /etc/profile 添加 export PATH/data/pgplugin/protobuf-c/bin:$PATH ​ # source /etc/profile ​ # echo /data/pgplugin/protobuf-c/lib /etc/ld.so.conf.d/pgplugin.conf # ldconfig备注如报错libprotobuf.so.21: cannot open shared object file: No such file or directory执行以下操作再编译# echo $LD_LIBRARY_PATH # export LD_LIBRARY_PATH/data/pgplugin/protobuf/lib:\$LD_LIBRARY_PATHLD_LIBRARY_PATH是Linux环境变量名该环境变量主要用于指定查找共享库动态链接库时除了默认路径之外的其他路径。安装sfcgal三维可选sfcgal依赖boostcgal需要提前安装和编译编译默认目录避免编译sfcgal时各种找不到库的问题# yum -y install boost-devel ​ # wget https://github.com/CGAL/cgal/archive/releases/cgal-5.6.3.tar.gz # tar -zxvf cgal-5.6.3.tar.gz # cd cgal-5.6.3 # mkdir build cd build ​ 安装cgal # cmake -DCMAKE_INSTALL_PREFIX/data/pgplugin/cgal -DCMAKE_BUILD_TYPERelease .. # make # make install编译安装sfcgal下载地址https://gitlab.com/sfcgal/SFCGAL# tar -zxvf SFCGAL-v1.5.2.tar.gz # cd SFCGAL-v1.5.2 ​ 安装 # mkdir build cd build # cmake -DCMAKE_INSTALL_PREFIX/data/pgplugin/sfcgal -DCGAL_DIR/data/pgplugin/cgal/lib64/cmake/CGAL -Wno-dev .. # make # make install ​ # echo /data/pgplugin/sfcgal/lib64 /etc/ld.so.conf.d/pgplugin.conf # ldconfig注银河麒麟V10服务器版需使用低版本 sfcgal 1.4.1安装gdal# yum -y install libqhull_r # 非必要可不安装 # wget https://download.osgeo.org/gdal/3.12.1/gdal-3.12.1.tar.gz # tar -zxvf gdal-3.12.1.tar.gz # cd gdal-3.12.1/ ​ # export PKG_CONFIG_PATH/data/pgsql16/lib/pkgconfig # echo /data/pgsql16/lib /etc/ld.so.conf.d/pgplugin.conf # ldconfig ​ 安装 # mkdir build cd build # cmake -DCMAKE_INSTALL_PREFIX/data/pgplugin/gdal -DCMAKE_BUILD_TYPERelease -DGDAL_USE_POSTGRESQLON -DGDAL_USE_PROJON -DGDAL_USE_SFCGALON -DCMAKE_PREFIX_PATH/data/pgplugin/proj;/data/pgplugin/sfcgal;/data/pgsql16 -Wno-dev .. ​ # make # make install ​ # echo /data/pgplugin/gdal/lib64 /etc/ld.so.conf.d/pgplugin.conf # ldconfig注银河麒麟V10服务器版需使用低版本 gdal 3.7.3安装PostGIS源码下载地址http://download.osgeo.org/postgis/source/# tar -zxvf postgis-3.5.4.tar.gz # cd postgis-3.5.4/ ​ 安装 # ./configure --prefix/data/pgplugin/postgis35 --with-gdalconfig/data/pgplugin/gdal/bin/gdal-config --with-pgconfig/data/pgsql16/bin/pg_config --with-geosconfig/data/pgplugin/geos/bin/geos-config --with-projdir/data/pgplugin/proj CPPFLAGS-I/data/pgplugin/proj/include LDFLAGS-L/data/pgplugin/proj/lib64 PKG_CONFIG_PATH/data/pgplugin/proj/lib64/pkgconfig --with-xml2config/data/pgplugin/libxml2/bin/xml2-config --with-jsondir/data/pgplugin/json-c --with-protobufdir/data/pgplugin/protobuf-c --with-sfcgal/data/pgplugin/sfcgal/bin/sfcgal-config ​ # make # make install安装pgrouting下载地址https://github.com/pgRouting/pgrouting/releases# vim /etc/profile //添加postgresql环境变量信息 ...... export PGHOME/data/pgsql16 export PGDATA/data/pgdata export PATH$PGHOME/bin:$PATH export LD_LIBRARY_PATH$PGHOME/lib ​ # source /etc/profile //生效 ​ ​ # wget https://github.com/pgRouting/pgrouting/releases/download/v3.8.0/pgrouting-3.8.0.tar.gz ​ # tar -zxvf pgrouting-3.8.0.tar.gz # cd pgrouting-3.8.0 # mkdir build cd build # cmake -DCMAKE_INSTALL_PREFIX/data/pgplugin/pgrouting -DCMAKE_PREFIX_PATH/data/pgplugin/cgal .. # make # make install注高版本pgrouting与boost有版本依赖关系centos7.9提供的boost版本1.53无法满足需要更高版本安装ogr-fdwgithub地址https://github.com/pramsey/pgsql-ogr-fdw/releasespostgresql的不同版本需要对应不同版本ogr-fdw否则会报错“ogr_fdw.h:40:28: fatal error: nodes/relation.h: No such file or directory”# wget https://github.com/pramsey/pgsql-ogr-fdw/archive/refs/tags/pgsql-ogr-fdw-1.1.7.tar.gz ​ # vim /etc/profile //添加gdal环境变量信息 ...... export GDALHOME/data/pgplugin/gdal export PATH$GDALHOME/bin:$PATH ​ # source /etc/profile //生效 ​ # tar -zxvf pgsql-ogr-fdw-1.1.7.tar.gz # cd pgsql-ogr-fdw-1.1.7 # make # make install修改权限# chown postgres:postgres -R /data/{pgsql16,pgdata}重启postgresql# systemctl stop postgresql.service # systemctl start postgresql.service测试登录测试# su - postgres $ psql -d postgres -p 5432创建扩展语句测试# CREATE DATABASE mytest; //创建数据库 # \c mytest; //切换到mytest库 ​ # CREATE EXTENSION postgis; CREATE EXTENSION # CREATE EXTENSION pgrouting; CREATE EXTENSION # CREATE EXTENSION postgis_topology; CREATE EXTENSION # CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION # CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION # CREATE EXTENSION address_standardizer; CREATE EXTENSION # CREATE EXTENSION postgis_raster; CREATE EXTENSION # CREATE EXTENSION postgis_sfcgal; CREATE EXTENSION ​ ​ 测试ST_AsMVT函数 # WITH mvtgeom AS ( SELECT ST_TileEnvelope(12,513,412) AS geom ) SELECT ST_AsMVT(mvtgeom.*) FROM mvtgeom; st_asmvt ---------------------------------------------------------------------------------------------- \x1a2b0a0764656661756c74121b1803221709d5a5a90e8adda00f1a00f09801f098010000ef98010f2880207802 (1 row) ​查看数据库中现有插件 \dx查看postgresql版本# SELECT version();查看postgis版本# SELECT PostGIS_full_version();