Check and Upgrade a PostGIS Database with postgis

You will use the installed postgis helper to inspect a PostgreSQL database, read the PostGIS version it reports, and decide whether an upgrade is appropriate. This guide uses Ubuntu package version 3.4.2+dfsg-1ubuntu3, installed as /usr/bin/postgis. Allow about fifteen minutes for a read-only check. An upgrade needs a tested database backup, a maintenance plan and permission to run SQL as the PostgreSQL account.

The helper is an administration wrapper, not the PostGIS SQL extension itself. The local manual documents the command shape as postgis command [arguments] and gives postgis status template_postgis as its example. The installed program also lists enable, upgrade and install-extension-upgrades.

1. Confirm the installed command

Start with two read-only checks. You do not need sudo for either:

$ command -v postgis; /usr/bin/postgis
$ dpkg-query -W -f='${Package} ${Version}\n' postgis; postgis 3.4.2+dfsg-1ubuntu3
$ postgis help; Usage: /usr/bin/postgis <command> [<args>]; Commands: help, enable <database>, upgrade <database>, status <database>, install-extension-upgrades [--pg_sharedir <dir>] [--extension <name>] [<from>...]

The real help output is longer and can wrap differently in your terminal. The useful checkpoint is that the binary path and package version are the ones you expected. The helper does not accept --help or --version as global options on this installation; use postgis help and the package query instead.

2. Check a database without changing it

Run status with the database name that contains, or should contain, PostGIS. Replace GIS_DATABASE with a real database name:

$ postgis status GIS_DATABASE
db GIS_DATABASE has postgis 3.4.2 as extension in schema public

The exact line depends on the database. A healthy extension-based installation normally reports the PostGIS version, the schema and the phrase as extension. If the extension is absent, the helper reports db GIS_DATABASE does not have postgis enabled. That is a status result, not a request to enable anything.

Check the shell status immediately if you are scripting the result:

$ postgis status GIS_DATABASE
$ printf 'postgis exit status: %s\n' "$?"
postgis exit status: 0

On this installed script, a database connection failure is printed as a diagnostic and the command can still exit successfully after continuing through its database list. Do not use the exit status alone as proof that inspection succeeded. Treat lines containing cannot be inspected or does not have postgis enabled as conditions your script must handle.

For example, the documented name template_postgis may not exist on a newer installation:

$ postgis status template_postgis
db template_postgis cannot be inspected: database "template_postgis" does not exist

Use psql -l or your normal inventory method to find the intended database, then rerun the status check. Do not create or modify a template merely to make the example name work.

3. Treat enable as an unsupported shortcut here

The command help says that enable enables PostGIS, but this package's installed Perl script reports Enable is not implemented yet. Do not use it as a production enablement procedure:

$ postgis enable GIS_DATABASE
Enable called with args: GIS_DATABASE
Enable is not implemented yet

For an extension-based database, enablement belongs in a controlled PostgreSQL session. The official PostGIS documentation uses CREATE EXTENSION postgis; for that operation. Run it only after confirming the database, the intended schema and the operator privileges. A typical supervised command is:

$ psql --dbname=GIS_DATABASE --command='CREATE EXTENSION postgis;'

This changes database state and may require a database owner or superuser. Take a backup and confirm that the extension is not already installed before running it. If it succeeds, verify with postgis status GIS_DATABASE. There is no universal undo that preserves every dependent object; removing an extension can destroy extension-owned objects, so recovery means restoring the database or following your organisation's reviewed rollback procedure.

4. Plan an upgrade before running upgrade

The helper's upgrade command is not a dry run. It opens each named database with psql, updates PostGIS extension metadata to a temporary marker, calls postgis_extensions_upgrade() and commits the transaction. That is a database change and can affect postgis, raster, topology, SFCGAL and related installed extensions.

Before using it, confirm all of the following:

• The target database name is exact and the PostgreSQL client can connect using the intended account.
• The new PostGIS files are installed and match the PostgreSQL server you are upgrading.
• A recent, restorable backup exists and the maintenance window covers the work.
• You have checked the official upgrade guidance for your old and new PostGIS versions.

When that review is complete, the command shape is:

$ postgis upgrade GIS_DATABASE
upgrading db GIS_DATABASE

The visible line only confirms that the helper started processing the database. It is not an upgrade report. Verify afterwards:

$ postgis status GIS_DATABASE
db GIS_DATABASE has postgis NEW_VERSION as extension in schema public
$ psql --dbname=GIS_DATABASE --tuples-only --no-align --command='SELECT postgis_full_version();'

Keep the original backup until the application has passed its spatial queries and migrations. If the operation fails, stop and retain the database logs and backup; do not repeatedly rerun an upgrade whose transaction or extension state is unclear.

5. Handle extension upgrade support files carefully

install-extension-upgrades prepares SQL upgrade support files in PostgreSQL's shared extension directory. It can create symbolic links, so it is a host-level change rather than a database status check. Its optional controls are --pg_sharedir DIR, --extension NAME and one or more old versions or share directories.

Inspect the directory and package ownership first. Run the preparation with the least privilege that can write that directory, commonly an administrator account:

$ pg_config --sharedir
/usr/share/postgresql/16
$ ls -ld /usr/share/postgresql/16/extension
$ sudo postgis install-extension-upgrades --pg_sharedir /usr/share/postgresql/16 3.3.4

Use the actual shared directory and an old version supported by your upgrade plan. Do not pass an arbitrary path or version copied from an unrelated host. Record the links created by your package-management process. Undoing this command means removing only the links it created, after checking that no upgrade still needs them; do not delete the whole extension directory.