brycejohnston/ubuntu24_postgres18_upgrade.md

## ubuntu24_postgres18_upgrade.md

      
    Raw
  

              ubuntu24_postgres18_upgrade.md
            
          
    Ubuntu 24 PostgreSQL 18 & PostGIS 3.6 (GEOS 3.14) Upgrade Guide

Target System: Ubuntu 24.04 (Noble)

Objective: Migrate DBs from PG 17 to PG 18 and install latest GIS libraries from source.

0. Backup / Dump All Databases (Before You Start)

sudo -u postgres pg_dumpall --globals-only > /var/backups/pg_globals.sql
sudo -u postgres pg_dumpall > /var/backups/pg_dumpall.sql

1. System Preparation & Repositories

Add the official PostgreSQL repository and install the necessary build tools.
Update and add PG Global Repo

sudo apt update && sudo apt install -y curl ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

sudo apt install -y postgresql-18 \
postgresql-server-dev-18 \
build-essential \
cmake libxml2-dev \
libproj-dev \
libgdal-dev \
libjson-c-dev \
pkg-config \
flex \
bison \
libpcre3-dev \
libprotobuf-c-dev \
protobuf-c-compiler

2. Install GEOS 3.14 (From Source)

Since the APT repositories often lag, we compile the latest GEOS to unlock PostGIS 3.6 features.
cd /tmp
wget https://download.osgeo.org/geos/geos-3.14.1.tar.bz2
tar xjf geos-3.14.1.tar.bz2 && cd geos-3.14.1

mkdir build && cd build
cmake -DCMAKE_BUILD_TYPE=Release -DCMAKE_INSTALL_PREFIX=/usr ..
make -j$(nproc)
sudo make install
sudo ldconfig

3. Install PostGIS 3.6 (From Source)

Compile PostGIS specifically linked against your new PostgreSQL 18 and GEOS 3.14 binaries.
cd /tmp
wget https://download.osgeo.org/postgis/source/postgis-3.6.2.tar.gz
tar xzf postgis-3.6.2.tar.gz && cd postgis-3.6.2

./configure --with-geosconfig=/usr/bin/geos-config --with-pgconfig=/usr/lib/postgresql/18/bin/pg_config

make -j$(nproc)
sudo make install

4. Migrate Data (PostgreSQL 17 → 18)

Use the Ubuntu cluster management tools to move your data.
Drop the empty default v18 cluster

sudo pg_dropcluster 18 main --stop
Upgrade the v17 cluster to v18

sudo pg_upgradecluster --force-packages 17 main
Verify clusters

pg_lsclusters
# Version 18 should be 'online' on port 5432

5. Finalize Extensions & Optimizer

Refresh the internal library links and database statistics.
Update PostGIS inside your databases

sudo -u postgres psql -d your_db_name -c "ALTER EXTENSION postgis UPDATE;"
Verify versions

sudo -u postgres psql -c "SELECT postgis_full_version();"
# Ensure output shows GEOS="3.14.x" and POSTGIS="3.6.x"
Recalculate Statistics

sudo -u postgres vacuumdb --all --analyze-in-stages --jobs=4

6. Cleanup & Security

Remove the old version and lock the PostGIS package to prevent APT from "hosing" your custom build.
# Prevent APT from overwriting your source build
sudo apt-mark hold postgresql-18-postgis-3

# Remove PG 17
sudo pg_dropcluster 17 main
sudo apt purge postgresql-17
sudo apt autoremove
Note: Because you compiled from source, if you ever need to update GEOS or PostGIS, repeat steps 2 or 3 and run ALTER EXTENSION postgis UPDATE; again.
Do pgtune alter conf as needed for your server requirements - https://pgtune.leopard.in.ua/

7. Ensure PG 18 server is properly enabled

sudo systemctl enable postgresql@18-main
sudo systemctl start postgresql@18-main
No results found