Table of Contents
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 ¶
- Then update your catalog and installed software
sudo apt update sudo apt upgrade
- 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
- You can check which clusters you are running using the pg_lsclusters commandline tool that is also installed
pg_lsclusters
- 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 ¶
- Check versions available
apt search postgresql-14 | grep pgrouting
# Install pgRouting sudo apt install postgresql-14-pgrouting
- 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