PostGIS: A Spatial Database Extension for PostgreSQL

PostGIS stands as a spatial database extension tailored for PostgreSQL. This extension enhances PostgreSQL by introducing support for geographic objects, empowering the database to store, query, and analyze spatial data, encompassing points, lines, and polygons.

Key Features of PostGIS

• Spatial Data Types: PostGIS introduces spatial data types (geometry and geography) for representing diverse shapes and geographical features.
• Spatial Indexing: It equips PostgreSQL with spatial indexing for efficient queries, enabling faster retrieval of spatial data.
• Geospatial Functions: PostGIS includes a robust collection of geospatial functions for spatial analysis, manipulation, and processing of spatial data.
• Support for Various Data Formats: It supports various data formats for seamless interchange, allowing users to import and export spatial data in different formats.
• Integration with GIS Software: PostGIS integrates well with GIS (Geographic Information System) software, enabling users to leverage the capabilities of both systems for spatial analysis and visualization.
• Geographic Indexing: PostGIS extends support for geographic indexing, enabling efficient indexing and retrieval of geographic data based on location.

Need for Updating PostGIS:

While using the PostGIS extension, it is important to stay updated with the latest releases of the extension for compatibility and improvements. One necessity to update the PostGIS extension arises while upgrading the PostgreSQL DB to a new major version.

Why Updating the PostGIS Extension When Upgrading PostgreSQL Is Crucial:

• Compatibility: Updating PostGIS ensures compatibility between PostgreSQL and the geospatial extension. Newer versions of PostgreSQL may introduce changes to the database structure or new features that require corresponding updates in PostGIS to align seamlessly.
• Bug Fixes and Optimizations: Each release of PostGIS includes updates that rectify known issues, enhance performance, and optimize the functionality of spatial queries. Updating ensures that you benefit from these improvements.
• Security: Newer versions of both PostgreSQL and PostGIS often include security patches and updates to address vulnerabilities present in previous releases. Updating helps protect your system from security threats.

Upgrading the major version of the PostgreSQL database (using the pg_uprade) may fail if older versions of the PostGIS and supporting extensions are installed, as the new database version might not support the existing PostGIS version.

Following is a sample log from an AWS RDS PostgreSQL server of a failing upgrade due to an older version of PostGIS:

 
-- The instance could not be updated because the PostGIS extension and its dependent extensions (address_standardizer, address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology, postgis_raster) installation in one or more databases is not compatible with your desired update path. Please update postgis and its dependent extensions to version supported in requested version.
	

In such cases, it is needed to first update the PostGIS extension to the latest version, after which we can update the DB major version.

PostGIS manages minor and major updates through the EXTENSION mechanism. If spatial capabilities are added to the database using CREATE EXTENSION postgis, users can update the database using this same functionality.

PostGIS version releases:

PostGIS community issues minor releases approximately once annually, typically just before PostgreSQL releases its latest version, usually around early October. Additionally, PostGIS releases patch versions as needed, responding to the volume of fixes or the gravity of ongoing issues.

The PostGIS project aims to support each minor version of extensions released for up to 2-4 years after initial release also at least until the lowest PostgreSQL version supported by PostGIS reaches its EOL.

The following table shows the list of PostGIS versions for the supported PostgreSQL major versions.

‍

Therefore, users of the PostGIS extension need to consider upgrading the PostGIS extension when planning for a PostgreSQL major version update for a database.

Updating PostGIS:

To update the PostGIS and the supporting extension in a database server, we can use the ALTER EXTENSION ‘extension_name’  UPDATE utility that comes with PostgreSQL, assuming that the packages required for the installation are pre-existing.

 
ALTER EXTENSION postgis UPDATE ;
	

Eg.

 
demo=> ALTER EXTENSION postgis UPDATE ;
WARNING:  unpackaging raster
WARNING:  PostGIS Raster functionality has been unpackaged
HINT:  type `SELECT postgis_extensions_update();` to finish the update. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;
ALTER EXTENSION
	

This updates the PostGIS primary extension to the latest version available.

In case we want to update to a particular version we can specify it in the command like:

 
ALTER EXTENSION postgis UPDATE TO ‘3.1.5’ ;
	

Then it is required to execute the postgis_extensions_update function to update the supported extensions.

 
SELECT postgis_extensions_update();
	

Eg.

 
demo=> SELECT postgis_full_version();
postgis_full_version                                                                                                                 
--------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.1.5 c60e4e3" [EXTENSION] PGSQL="110" GEOS="3.7.3-CAPI-1.11.3 b50468f" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.3.1, released 2018/06/22" LIBXML="2.9.1" LIBJS
ON="0.12.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" RASTER
(1 row)
	

With this, the PostGIS extension will be seamlessly updated, ensuring a smooth upgrade of the PostgreSQL database without any blockers.

Upgrading the PostGIS extension for PostgreSQL is absolutely crucial to guarantee optimal compatibility with the latest features, enhancements, and bug fixes. Additionally, it is paramount for addressing any potential security vulnerabilities that may arise.

It becomes an absolute necessity to update PostGIS and its dependent extensions while upgrading the PostgreSQL DB to a major version to ensure seamless compatibility and optimal performance.

Ready to harness the power of spatial data with PostgreSQL? Explore our comprehensive database management solutions tailored to meet your organization's needs. Contact us today to learn more about our services and how we can help you harness the power of PostgreSQL for your business.

{{cta}}