[[TOC]]

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)"

}}}


=== 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