Changes between Initial Version and Version 1 of UsersWikiPostGIS3UbuntuPGSQLApt

Timestamp:

May 29, 2022, 1:03:06 PM (2 years ago)

Author:

robe

Comment:

—

UsersWikiPostGIS3UbuntuPGSQLApt

@@ +1 @@
+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