Update GanosBase and PostGIS extensions for PostgreSQL - ApsaraDB RDS

GanosBase includes two types of extensions: Alibaba Cloud-developed Ganos extensions and PostGIS extensions. When a newer version becomes available on your RDS instance, extensions are not updated automatically — you must update them manually. Run the queries in this topic to check which extensions need updating, then apply the appropriate update method for your PostgreSQL version.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for PostgreSQL instance with GanosBase installed
Access to the PostgreSQL CLI to connect to the instance

Update Ganos extensions

Check which extensions need updating

Connect to your RDS instance using the PostgreSQL CLI.

Run the following query to list installed Ganos extensions and check whether updates are available:

SELECT
  *,
  installed_version < default_version AS need_update
FROM
  pg_available_extensions
WHERE
  name LIKE 'ganos%';

The output looks similar to the following:

name                          | default_version | installed_version | comment                                                                              | need_update
------------------------------+-----------------+-------------------+--------------------------------------------------------------------------------------+-------------
 ganos_trajectory              | 5.5             | 5.4               | Ganos trajectory extension for PostgreSQL                                            | t
 ganos_pointcloud_geometry     | 5.5             | 5.4               | Ganos_pointcloud LIDAR data and ganos_geometry data for PostgreSQL                   | t
 ganos_raster                  | 5.5             | 5.4               | Ganos raster extension for PostgreSQL                                                | t
 ganos_networking              | 5.5             | 5.4               | Ganos networking extension for PostgreSQL                                            | t
 ganos_geometry_pyramid        | 5.5             | 5.4               | Ganos Geometry Pyramid extension for PostgreSQL                                      | t
 ganos_scene                   | 5.5             | 5.4               | Ganos scene extension for PostgreSQL                                                 | t
 ganos_geometry_topology       | 5.5             | 5.4               | Ganos geometry topology spatial types and functions extension for PostgreSQL         | t
 ganos_tiger_geocoder          | 5.5             | 5.4               | Ganos tiger geocoder and reverse geocoder                                            | t
 ganos_importer                | 5.5             | 5.4               | Ganos Spatial importer extension for PostgreSQL                                      | t
 ganos_vomesh                  | 5.5             | 5.4               | Ganos volumn mesh extension for PostgreSQL                                           | t
...

Column	Description
`name`	The name of the extension.
`default_version`	The latest version available on the RDS instance.
`installed_version`	The version currently installed on the RDS instance.
`comment`	A description of the extension.
`need_update`	Whether an update is available. `t` means an update is required.

If need_update is t for any extension, proceed with the update steps below.

Run the update

The update method depends on the version of your Ganos extensions.

Version 3.1 or later — Run the built-in update function:
```
SELECT ganos_update();
```

Earlier than version 3.1 — The ganos_update() function does not exist yet. Create it first, then call it:

CREATE OR REPLACE FUNCTION ganos_update()
    RETURNS text AS
$$
DECLARE
    rec RECORD;
    sql text;
BEGIN
    FOR rec IN
        SELECT extname
        FROM pg_extension
        WHERE extname like 'ganos_%'
        LOOP
            sql = 'ALTER EXTENSION '
                || rec.extname
                || ' UPDATE ';
            RAISE NOTICE '%', sql;
            EXECUTE sql;
        END LOOP;
    return 'All Ganos extensions have updated to latest version';
END
$$ LANGUAGE 'plpgsql' volatile STRICT;

Update PostGIS extensions

The following steps apply to a single database. If your RDS instance has multiple databases, repeat these steps on each database.

Check which extensions need updating

Connect to your RDS instance using the PostgreSQL CLI.

Run the following query to list installed PostGIS extensions and check whether updates are available:

SELECT
  *,
  installed_version < default_version AS need_update
FROM
  pg_available_extensions
WHERE
  name LIKE 'postgis%';

The output looks similar to the following:

name                    | default_version | installed_version | comment                            | need_update
------------------------+-----------------+-------------------+------------------------------------+-------------
 postgis                | 3.3.2           | 3.1.4             | Ganos PostGIS+                     | t
 postgis_tiger_geocoder | 3.3.2           | 3.1.4             | Ganos PostGIS+ tiger geocoder      | t
 postgis_raster         | 3.3.2           | 3.1.4             | PostGIS raster types and functions | t
...

If need_update is t, proceed with the update steps below.

Run the update

The update method depends on the major engine version of your RDS instance.

PostgreSQL 10, 13, 14, or 15

Update each extension individually using ALTER EXTENSION:

ALTER EXTENSION <Extension name> UPDATE;

Alternatively, update all PostGIS extensions at once using the PostGIS_Extensions_Upgrade() function:

SELECT PostGIS_Extensions_Upgrade();

For more information about PostGIS_Extensions_Upgrade(), see the PostGIS documentation.

PostgreSQL 11 or 12

Run the following script. It detects which extensions are installed and calls the appropriate update function automatically:

do
$$
DECLARE
    with_pgis boolean;
    with_ganos boolean;
BEGIN
    SELECT ((SELECT 1 FROM pg_extension WHERE extname='postgis') IS NOT null)
    INTO with_pgis;

    SELECT ((SELECT 1 FROM pg_extension WHERE extname='ganos_geometry') IS NOT null)
    INTO with_ganos;

    IF with_pgis AND with_ganos THEN
        PERFORM ganos_update();
    ELSIF with_ganos THEN
        PERFORM ganos_update();
    ELSIF with_pgis THEN
        PERFORM PostGIS_Extensions_Upgrade();
    END IF;

    return 'PostGIS has update to ' || postgis_lib_version();
END
$$ LANGUAGE 'plpgsql' ;