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

mahan

下载基础包
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/s ... tgresql-15.6.tar.gz
tar -xzf postgresql-15.6.tar.gz

wget 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/python3
   
make -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 install

  PostGIS is now configured for x86_64-pc-linux-gnu

-------------- Compiler Info -------------
  C compiler:           gcc -std=gnu99 -g -O2 -fno-math-errno -fno-signed-zeros -Wall
  C++ 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/include
  LDFLAGS:              -L/usr/lib/x86_64-linux-gnu -lm
  SQL preprocessor:     /usr/bin/cpp -traditional-cpp -w -P -Upixel -Ubool
  Archiver:             gcc-ar rs

-------------- Additional Info -------------
  Interrupt Tests:   ENABLED

-------------- Dependencies --------------
  GEOS config:          /usr/bin/geos-config
  GEOS version:         3.12.1
  GDAL config:          /usr/bin/gdal-config
  GDAL version:         3.8.4
  PostgreSQL config:    /usr/local/pgsql/bin/pg_config
  PostgreSQL version:   PostgreSQL 15.6
  PROJ4 version:        94
  Libxml2 config:       /usr/bin/xml2-config
  Libxml2 version:      2.9.14
  JSON-C support:       yes
  protobuf support:     yes
  protobuf-c version:   1004001
  PCRE support:         Version 2
  Perl:                 /usr/bin/perl

--------------- Extensions ---------------
  PostgreSQL EXTENSION support:       enabled
  PostGIS Raster:                     enabled
  PostGIS Topology:                   enabled
  SFCGAL support:                     disabled
  Address Standardizer support:       enabled

-------- Documentation Generation --------
  xsltproc:            
  xsl style sheets:     
  dblatex:              
  convert:              
  mathml2.dtd:          http://www.w3.org/Math/DTD/mathml2/mathml2.dtd

configure: 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=# \dx
                                        List of installed extensions
          Name          | Version |   Schema   |                        Description                        
------------------------+---------+------------+------------------------------------------------------------
fuzzystrmatch          | 1.1     | public     | determine similarities and distance between strings
pgrouting              | 3.6.0   | public     | pgRouting Extension
plpgsql                | 1.0     | pg_catalog | PL/pgSQL procedural language
postgis                | 3.4.2   | public     | PostGIS geometry and geography spatial types and functions
postgis_raster         | 3.4.2   | public     | PostGIS raster types and functions
postgis_tiger_geocoder | 3.4.2   | tiger      | PostGIS tiger geocoder and reverse geocoder
postgis_topology       | 3.4.2   | topology   | PostGIS topology spatial types and functions