ubuuntu24.04 编译安装 PostgreSQL15.6+postgis 3.4.2 + pgrouting 3.6.0 +lz4

下载基础包

sudo apt update && sudo apt upgrade -y
sudo apt install -y build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libssl-dev libgdal-dev \
libgeos-dev libproj-dev libprotobuf-c-dev protobuf-c-compiler libjson-c-dev sqlite3 libsqlite3-dev cmake pkg-config liblz4-dev \
git                    \
libboost-all-dev       \
libbz2-dev             \
liblz4-dev             \
libzstd-dev            \
libcurl4-openssl-dev

下载源码包

wget https://ftp.postgresql.org/pub/source/v15.6/postgresql-15.6.tar.gz
tar -xzf postgresql-15.6.tar.gzwget https://download.osgeo.org/postgis/source/postgis-3.4.2.tar.gz
tar -xzf postgis-3.4.2.tar.gz

编译 PG

./configure \--prefix=/usr/local/pgsql     \--with-openssl               \--with-libxml                \--with-readline              \--with-python	\--with-lz4 \PYTHON=/usr/bin/python3make -j$(nproc) world && make install-world

编译 postgis

./configure \--with-pgconfig=/usr/local/pgsql/bin/pg_config \--with-projdir=/usr \CPPFLAGS="-I/usr/include" \LDFLAGS="-L/usr/lib/x86_64-linux-gnu"make -j $(nproc)  && make installPostGIS is now configured for x86_64-pc-linux-gnu-------------- Compiler Info ------------- C compiler:           gcc -std=gnu99 -g -O2 -fno-math-errno -fno-signed-zeros -WallC++ compiler (Wagyu): gcc -std=c++11 -x c++ C++ compiler (FlatGeobuf): gcc -std=c++11 -x c++ CPPFLAGS:              -I/usr/include -I/usr/include  -I/usr/include/libxml2  -I/usr/include/json-c   -DNDEBUG -I/usr/includeLDFLAGS:              -L/usr/lib/x86_64-linux-gnu -lmSQL preprocessor:     /usr/bin/cpp -traditional-cpp -w -P -Upixel -UboolArchiver:             gcc-ar rs-------------- Additional Info ------------- Interrupt Tests:   ENABLED-------------- Dependencies -------------- GEOS config:          /usr/bin/geos-configGEOS version:         3.12.1GDAL config:          /usr/bin/gdal-configGDAL version:         3.8.4PostgreSQL config:    /usr/local/pgsql/bin/pg_configPostgreSQL version:   PostgreSQL 15.6PROJ4 version:        94Libxml2 config:       /usr/bin/xml2-configLibxml2 version:      2.9.14JSON-C support:       yesprotobuf support:     yesprotobuf-c version:   1004001PCRE support:         Version 2Perl:                 /usr/bin/perl--------------- Extensions --------------- PostgreSQL EXTENSION support:       enabledPostGIS Raster:                     enabledPostGIS Topology:                   enabledSFCGAL support:                     disabledAddress Standardizer support:       enabled-------- Documentation Generation -------- xsltproc:             xsl style sheets:     dblatex:              convert:              mathml2.dtd:          http://www.w3.org/Math/DTD/mathml2/mathml2.dtdconfigure: WARNING: 
configure: WARNING:  | You are building using --with-projdir. This option isn't standard and    |
configure: WARNING:  | might be incompatible with future releases of PROJ.                      |
configure: WARNING:  | You can instead adjust the PKG_CONFIG_PATH environment variable if you   |
configure: WARNING:  | installed software in a non-standard prefix.                             |
configure: WARNING:  | Alternatively, you may set the environment variables PROJ_CFLAGS and     |
configure: WARNING:  | PROJ_LIBS to avoid the need to call pkg-config. 

编译安装 pgrouting

下载源码包

git clone https://github.com/pgRouting/pgrouting.git
或下载压缩包
https://github.com/pgRouting/pgrouting/releases

配置编译参数

export PATH=/usr/local/pgsql/bin:$PATH
export LD_LIBRARY_PATh=/usr/local/pgsql/lib:$LD_LIBRARY_PATh
tar xf pgrouting-3.6.0.tar.gz
cd pgrouting-3.6.0
mkdir build && cd build
cmake .. \-DCMAKE_BUILD_TYPE=Release \-DCMAKE_INSTALL_PREFIX=/usr/local/pgsql \-DPOSTGRESQL_EXECUTABLE=/usr/local/pgsql/bin/pg_config

编译安装

make -j$(nproc)
make install
ldconfig

初始化数据库

useradd -m postgres
chown -R postgres:postgres /usr/local/pgsql
su - postgres
cd /usr/local/pgsql
./bin/initdb -U postgres -E UTF8 -D ./data

建表并检查列是否使用了 lz4 压缩算法

drop table if exists test_lz4;
CREATE TABLE test_lz4 (txt text COMPRESSION lz4);
INSERT INTO test_lz4 SELECT repeat('abcd',10000);
\d+ test_lz4
select pg_column_compression(txt) from test_lz4;postgres=# select pg_column_compression(txt) from test_lz4;pg_column_compression 
-----------------------lz4
(1 row)drop table if exists test;
CREATE TABLE test (txt text);
INSERT INTO test SELECT repeat('abcd',10000);
\d+ test
select pg_column_compression(txt) from test;
postgres=# select pg_column_compression(txt) from test;pg_column_compression 
-----------------------lz4
(1 row)

检查 postgis 与 pgrouting 是否可以成功创建

create extension fuzzystrmatch;
create extension pgrouting;
create extension plpgsql;
create extension postgis;
create extension postgis_raster;
create extension postgis_tiger_geocoder;
create extension postgis_topology;postgres=# \dxList of installed extensionsName          | Version |   Schema   |                        Description                         
------------------------+---------+------------+------------------------------------------------------------fuzzystrmatch          | 1.1     | public     | determine similarities and distance between stringspgrouting              | 3.6.0   | public     | pgRouting Extensionplpgsql                | 1.0     | pg_catalog | PL/pgSQL procedural languagepostgis                | 3.4.2   | public     | PostGIS geometry and geography spatial types and functionspostgis_raster         | 3.4.2   | public     | PostGIS raster types and functionspostgis_tiger_geocoder | 3.4.2   | tiger      | PostGIS tiger geocoder and reverse geocoderpostgis_topology       | 3.4.2   | topology   | PostGIS topology spatial types and functions