The [http://wiki.postgresql.org/wiki/Apt 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)" }}} === 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