Determine which version of PostGIS each database is running

One of the features of PostGIS (pain to some however you look at it), is that PostGIS library file is versioned by minor version. The library will have for example a postgis-2.0 or postgis-1.5.dll / .so to denote the version. Each version of PostGIS can be compiled to run on usually about 3 or 4 versions of PostgreSQL.

Since PostGIS is not part of PostgreSQL proper and has to be installed separately, it is possible to run a different version of PostGIS in each database of a cluster. While this is a great feature for PostGIS developers and also great for users who want to keep their old legacy PostGIS apps, while testing or creating new apps with the PostGIS 2.0 or experiment with 2.1 development series, it does pose some obvious challenges.

For example you can't simply just upgrade your cluster to a new version of PostgreSQL. You need to make sure the new cluster has the various versions of PostGIS compiled and available. One step to that end is figuring out exactly what version of PostGIS each database in your cluster is running. Here is a quick psql script I wrote up to help with that.

The script

This psql script will generate another script which will generate a pipe delimeted database list of postgis databases and what version of postgis they are running.

The contents of check_postgis_version.sql look as follows

\t
\a
\o postgis_report_query.psql
\qecho '\\o postgis_report_query.psv'
SELECT '\connect ' || quote_ident(datname) || E'\n' || 'SELECT ' || quote_literal(datname) || ' As db, PostGIS_Lib_Version();'
 FROM pg_database
WHERE datname NOT IN ('postgres', 'template0', 'template1');
\qecho '\\o'
\o
\i postgis_report_query.psql

to run the script you'd do something like this and you'll need to run under a super user account

psql -U postgres -f check_postgis_version.sql -d postgres

The output will be a file called postgis_report_query.psv which is a | delimeted text file will look something like this:

testpostgis210|2.1.0SVN
testpostgis2|2.0.3

Note, if you want more detail as to the versions of other dependency libraries, you can replace postgis_lib_version() with postgis_full_version(). Check the PostGIS Management Function reference for additonal details