Ganos时空引擎插件如何升级 - 云数据库 RDS

Ganos时空引擎插件包括PostGIS插件和阿里云自研Ganos插件。本文介绍这些插件的升级方法。

Ganos插件升级

阿里云时空引擎Ganos插件在描述中进行标识。您可以通过如下方法快速找到已安装的Ganos插件并升级。

使用PostgreSQL命令行工具连接数据库。

使用\dx命令查看插件列表。

结果示例：

                                                                          List of installed extensions
                Name                | Version |   Schema   |                                                     Description
------------------------------------+---------+------------+---------------------------------------------------------------------------------------------------------------------
 address_standardizer               | 2.5.4   | public     | Ganos PostGIS+ address standardizer
 address_standardizer_data_us       | 2.5.4   | public     | Ganos PostGIS+ address standardizer data us
 ganos_address_standardizer         | 4.1     | public     | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 ganos_address_standardizer_data_us | 4.1     | public     | Address Standardizer US dataset example
 ganos_geometry                     | 4.1     | public     | Ganos geometry extension for PostgreSQL
 ganos_geometry_sfcgal              | 4.1     | public     | Ganos geometry SFCGAL functions extension for PostgreSQL
 ganos_geometry_topology            | 4.1     | topology   | Ganos geometry topology spatial types and functions extension for PostgreSQL
 ganos_networking                   | 4.1     | public     | Ganos networking extension for PostgreSQL
 ganos_spatialref                   | 4.1     | public     | Ganos spatial reference extension for PostgreSQL
 ganos_tiger_geocoder               | 4.1     | tiger      | Ganos tiger geocoder and reverse geocoder
 plpgsql                            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                            | 2.5.4   | public     | Ganos PostGIS+
 postgis_sfcgal                     | 2.5.4   | public     | Ganos PostGIS+
 postgis_tiger_geocoder             | 2.5.4   | public     | Ganos PostGIS+ tiger geocoder
 postgis_topology                   | 2.5.4   | public     | Ganos PostGIS+ topology

通过Description列的描述信息获取已安装的Ganos插件。
说明如果描述中包含Ganos，则表示是Ganos插件。

升级Ganos插件。

如果您的Ganos插件版本大于等于3.1，请使用如下语句对全部Ganos插件进行升级。
```
SELECT ganos_update();
```

如果您的Ganos插件版本小于3.1，请参考如下命令，手动创建函数对全部Ganos插件进行升级。

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;

PostGIS插件升级

PostGIS插件根据RDS PostgreSQL实例大版本不同，升级方法不同。

如果RDS PostgreSQL实例大版本为PostgreSQL 10、11、14或15，升级方法如下：
```
ALTER EXTENSION <插件名> UPDATE;
```

如果RDS PostgreSQL实例大版本为PostgreSQL 12、13，升级方法如下：

CREATE OR REPLACE FUNCTION postgis_update()
    RETURNS void AS
$$
DECLARE
    rec RECORD;
    sql text;
BEGIN
    FOR rec IN
        SELECT extname
        FROM pg_extension
        WHERE extname like 'postgis%'
            OR extname like 'address_standardizer%'
            OR extname like 'pgrouting%'
        LOOP
            sql = 'ALTER EXTENSION '
                || rec.extname
                || ' UPDATE ';
            RAISE NOTICE '%', sql;
            BEGIN
                EXECUTE sql;
            EXCEPTION
                WHEN OTHERS THEN
                RAISE NOTICE 'Failed to update extension : %', rec.extname;
            END;
        END LOOP;
END
$$ LANGUAGE 'plpgsql' volatile STRICT;


CREATE OR REPLACE FUNCTION update_postgis()
    RETURNS text AS
$$
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_update();
    END IF;

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

SELECT update_postgis();