AWS RDS PostgreSQL版本升级

起因

海外AWS中有一套系统采用的是PostgreSQL 9.5， 因为PG 9.5在2021年2月11号之后不再被PG社区所支持了, PG社区对版本的支持参见Versioning Policy。因此AWS发送了邮件，提示我们尽快升级到9.6。并且介于PG 9.6在2020年11月11号之后也会停止支持，AWS建议直接升级到PostgreSQL 12或以上版本。

基于直接从9.5升级到12，中间跨越版本太大，可能会有问题。因此决定先暂时升级到9.6。等后续完整测试12后，再升级到12。

升级步骤

AWS升级，可以在Web Console中点击几个按钮就可以升级。但升级时，Event提示升级失败，PreUpgrade checks Fails。报错如下:

1
2
3
4
5
The instance could not be upgraded from 9.5.22.R1 to 9.6.19.R1 because of following reasons. Please take appropriate action on databases that have usages incompatible with requested major engine version upgrade and try again.

Following usages in database 'xxxxx' need to be corrected before upgrade:
-- The instance could not be upgraded because there are one or more databases with an older version of PostGIS extension or its dependent extensions (address_standardizer, address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology) installed. Please upgrade all installations of PostGIS and drop its dependent extensions and try again.
Following usages in database 'nest_demo' need to be corrected before upgrade:

提示升级检查失败，原因是PostGIS的版本存在问题。

失败原因调查

既然出错了，就上网找原因和解决方案。
AWS官网和PostgreSQL官网一通找。找到了问题所在。
目前我们PostgreSQL数据库中的PostGIS版本是2.1.3，根据PostgreSQL和PostGIS版本支持的说明，PostgreSQL 9.6中，最少支持2.2以上版本。所以导致了升级9.6失败。
postgresql_postgis_version_compatibility.png

不过说来也怪，根据这表格的说明，9.5版本中应该也已经不支持2.1.3了，但实际上目前我们数据库中PG版本是9.5.22, 而PostGIS版本是2.1.3。不过总之，升级到9.6需要升级对应的PostGIS版本就对了。

升级步骤

  1. 查看当前数据库PostGIS版本

    1
    2
    3
    4
    5
    6
    7
    8
    xxxx=> \dx
                                            List of installed extensions
         Name      | Version |   Schema   |                             Description
    ---------------+---------+------------+---------------------------------------------------------------------
    ...
     postgis       | 2.1.3   | public     | PostGIS geometry, geography, and raster spatial types and functions
    ...
    xxxx=>

  2. 查看可升级的PostGIS版本

    1
    2
    3
    4
    5
    6
    7
    8
    9
    xxxx=> SELECT * FROM pg_available_extension_versions where name='postgis';
      name   | version | installed | superuser | relocatable | schema | requires |                               comment
    ---------+---------+-----------+-----------+-------------+--------+----------+---------------------------------------------------------------------
     postgis | 2.5.2   | f         | t         | f           |        |          | PostGIS geometry, geography, and raster spatial types and functions
     postgis | 2.1.8   | f         | t         | f           |        |          | PostGIS geometry, geography, and raster spatial types and functions
     postgis | 2.2.5   | f         | t         | f           |        |          | PostGIS geometry, geography, and raster spatial types and functions
    (3 rows)

    xxxx=>

  3. 升级PostGIS至版本2.2.5

    1
    2
    3
    4
    5
    6
    xxxx=> ALTER EXTENSION postgis UPDATE TO '2.2.5';
    WARNING:  'postgis.backend' is already set and cannot be changed until you reconnect
    CONTEXT:  SQL statement "SELECT                  postgis_lib_version()"
    PL/pgSQL function postgis_major_version_check() line 21 at SQL statement
    ALTER EXTENSION
    xxxx=>

  4. AWS Web Console进行升级操作
    在Web Console中选中需要升级的RDS Instance，选择升级为9.6.19，顺利升级成功。

  5. 升级后续处理
    升级后，对每个表进行ANALYZE刷新pg_statistic。

    1
    analyze verbose table_name_1;

Reference

留言

▲