The ​PostgreSQL Apt Repository now hosts installs of PostGIS, pgRouting, pgpointcloud in addition to PostgreSQL.

The following describes how to install PostgreSQL 14, PostGIS 3.2, pgRouting 3.4 on an Ubuntu/Debian system. It is assumed to also work on Linux Mint, Lubuntu, and Xubuntu.

Run these in terminal:

Verify what version of Ubuntu you are running

sudo lsb_release -a 

Add Respository to sources.list

You'll need to replace the nickname below with what you are running.

# prereqs needed for accept certs
apt install ca-certificates gnupg

Adds pgdg keys and main repo

curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Testing Beta Packages

# If you want to try beta packages like not yet released PG15 and PostGIS 3.3 versions, you can do

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg-testing main 15" > /etc/apt/sources.list.d/pgdg-testing.list'

Allow dependencies to be installed

PostgreSQL relies on libpq and other things, sometimes your upstream packaging will get in the way. To ensure PostgreSQL apt repo takes, precendence, create a file as follows

# to allow dependencies to be installed
cat << EOF >> /etc/apt/preferences.d/pgdg.pref
Package: *
Pin: release o=apt.postgresql.org
Pin-Priority: 500
EOF

Installing

1. Then update your catalog and installed software

   sudo apt update
   sudo apt upgrade
   

2. Install PostgreSQL replacing 14 with the version you want

   sudo apt install postgresql-14 
   

Apt system allows for installing multiple versions of PostgreSQL, so you could also install an additional, such as

sudo apt install postgresql-15 

3. You can check which clusters you are running using the pg_lsclusters commandline tool that is also installed

pg_lsclusters

4. To see what extensions are available

apt search postgresql-14 | grep postgis

Install PostGIS, pgRouting

The following will install PostGIS and pgRouting (the -3 always refers to latest 3 version available. So at this time that would be 3.2.1)

sudo apt install postgresql-14-postgis-3

#to get the commandline tools shp2pgsql, raster2pgsql you need to do this
sudo apt install postgis

To get pgRouting

1. Check versions available

apt search postgresql-14 | grep pgrouting

# Install pgRouting 
sudo apt install postgresql-14-pgrouting

2. A companion package for pgrouting is the osm2pgrouting which allows you to load osm data in a routable format. To install do the following

apt install osm2pgrouting

Enable PostGIS and pgRouting in database

Never install PostGIS or pgrouting in the postgres database, create a user database You can also enable the PostGIS extension here (or with the GUI as described below):

sudo -u postgres psql

CREATE DATABASE gisdb;
ALTER DATABASE gisdb SET search_path=public,postgis,contrib;
\connect gisdb;

CREATE SCHEMA postgis;

CREATE EXTENSION postgis SCHEMA postgis;
SELECT postgis_full_version();

should give you output something like this:

                                                                                           postgis_full_version
 POSTGIS="3.2.1" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
(1 row)

Installing postgis_sfcgal, if you need advanced 3D support

CREATE EXTENSION postgis_sfcgal SCHEMA postgis;
SELECT postgis_full_version();

should give you:

                                                                                                   postgis_full_version

POSTGIS="3.2.1" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" SFCGAL="SFCGAL 1.4.1, CGAL 5.3.1, BOOST 1.74.0" PROJ="8.2.1" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

Installing postgis_raster, if you need raster support

CREATE EXTENSION postgis_raster SCHEMA postgis;
SELECT postgis_full_version();

Output will be something like below, you should see RASTER in there and GDAL

                      postgis_full_version                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.2.1" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" SFCGAL="SFCGAL 1.4.1, CGAL 5.3.1, BOOST 1.74.0" PROJ="8.2.1" GDAL="GDAL 3.4.1, released 2021/12/27" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" RASTER
(1 row)

Installing postgis_topology

If you need to edit vector data and maintain connectedness across boundaries, then postgis_topology can help with that.

CREATE EXTENSION postgis_topology;
SELECT postgis_full_version();

Note the added TOPOLOGY in this output.

                           postgis_full_version                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.3.0dev 0" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" SFCGAL="SFCGAL 1.4.1, CGAL 5.3.1, BOOST 1.74.0" PROJ="8.2.1" GDAL="GDAL 3.4.1, released 2021/12/27" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER

Install pgRouting

CREATE  EXTENSION pgrouting SCHEMA postgis;
SELECT * FROM pgr_version();

Depending on the version, newer versions only output a version number as below. Older versions output a whole table

 pgr_version
-------------
 3.3.0

To get a full detail table on newer versions of pgrouting, use pgr_full_version();

Exit the psql console:

\q

Installing ogr_fdw

ogr_fdw is a spatial foreign data wrapper that uses GDAL under the hood. To install binaries:

sudo apt install postgresql-14-ogr-fdw

Once binaries are installed, you can enable in your database and link to external files as follows

\connect gisdb;
CREATE EXTENSION ogr_fdw SCHEMA postgis;
CREATE SCHEMA IF NOT EXISTS staging ;

-- see a list of data sources supported
SELECT *
FROM unnest(ogr_fdw_drivers());

-- here I assume you have a file in root of /gis_data folder that postgres process has read rights to
-- any file that gdal can read will do e.g shape file, CSV, etc
CREATE SERVER svr_shp
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource '/gis_data',
    format 'ESRI Shapefile' );


-- this will link in all your shapefile tables in folder gis_data as foreign tables
IMPORT FOREIGN SCHEMA ogr_all 
FROM SERVER svr_shp INTO staging;

More examples of loading you can find at ​https://github.com/pramsey/pgsql-ogr-fdw