AWS RDS PostgreSQL で PostGISを使って地図を色々処理しているシステムの運用を担当しています。
このシステムのPostgreSQLバージョンが 9.4 だったことで、Amazonからサポート終了のお知らせを頂いてアップグレードすることになりました。

関連企業向けの業務ツールで、夜間はメンテナンスのために停止して問題ないシステムですので、AWS Consoleからアップデート指示するだけで大丈夫だろう〜と思っていたら

Database instance is in a state that cannot be upgraded:
 PreUpgrade checks failed:
 The instance could not be upgraded because the PostGIS
 extension and its dependent extensions (address_standardizer,
 address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology)
 installation in one or more databases is not compatible with your desired
 upgrade path.
 This instance must be upgraded one major version at a time.
 Either drop these extensions or try upgrading the instance one major version
 at a time.
 Please refer to the public documentation for more details.

※超意訳 => PostGISが対応していないからアップデート出来ないよ〜

という内容のメッセージが表示されて、色々調べて対応したのですが思いの外、対応が大変だったので備忘録も兼ねてその手順をメモしておきます。

PostGISのアップグレード手順

全文英語ですが、公式ドキュメントはこちらになります。
https://postgis.net/docs/postgis_installation.html#upgrading
日本語マニュアルもありました。
https://postgis.net/docs/postgis-ja.html

アップグレードの手順としては大きく分けて
* ソフトアップグレード
* ハードアップグレード
の２つの手法が有り、 ソフトアップグレードの方はマイナーバージョンアップをするとき、ハードアップグレードはメジャーバージョンアップをするときに使うようです。

で、ソフトアップグレードをするだけならば、対象DBに接続した上で、

ALTER EXTENSION POSTGIS UPDATE TO '〓'
-- 〓はバージョン番号

このようなSQLを実行すれば作業完了です。
この手順は、Amazonの公式ドキュメントにも記載されています。
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.Overview

今回は、メジャーバージョンを上げる必要があり、ハードアップグレードが必要でした。

PostGISハードアップグレードの手順

ハードアップグレードの手順は、ざっくり示すと以下のようになります。

現行のDBのダンプを取得する。
新しいDBインスタンスを作成する。
新しいDBに、新しいバージョンのPostGisをインストール（有効化？）する。
新しいDBに、１で取得したダンプをリストアする。

ほら。一気にハードルが上がった感じに… orz
更には
https://qiita.com/mzmiyabi/items/fdb5130ed5acb7faf0ca
こちらのページに記載されているような形で、通常のdump / restoreも試してみたのですが、私の環境ではこれではうまくいきませんでした。
AWS RDSで有ることが関係している（rdsadminロールになることができないため）のか、今回私の環境ではRDS for PostgreSQLからAurora PostgreSQLへのアップデートという流れだったためそれが影響したのか、それとも、他の理由からなのかは不明です。

ということで、Amazonのドキュメントにもある

注記
Amazon RDS PostgreSQL DB インスタンスで PostGIS 拡張機能を実行している場合は、PostGIS ドキュメントの「PostGIS のアップグレード手順」に従って、アップグレードしてください。

という注意書きに従ってPostGISの公式ドキュメントを頼りに作業を進めていきます。
羅針盤となるページはこちら。

0.前提条件

今回のアップデートは、 9.4 => 10.7で確認しました。
理由としては、今後の運用のために、PostgreSQLからAurora PostgreSQLへの変更を検討していることから、現在Aurora PostgreSQLが現時点で対応している最新版の10.7にしたいと考えていたからです。
また、先程の羅針盤ページに記載されていますが、リストアをする際にPostGISから提供されている postgis_restore.pl というツールを利用します。
PostGISのソースを取得して、utils配下でビルドされるのですが、いくつか外部ライブラリをインストールしなければビルドできませんでした。
参考に、yumの履歴を見てみるとこのあたりをインストールしたようです。

sudo yum install gcc pg_config libxml2 libxml2-devel.x86_64 geos-config libgeos-config libgeos-dev geos geos-devel gcc gcc-c++ proj proj-devel gdal gdal-devel gdal-libs clang clang-devel clang-libs

もしかしたら全部は必要ないのかもしれませんし、これでも足りていないかもしれませんが、1つずつ不足が指摘されたものと入れていく形でやっていたので正直、把握しきれていません…。
すみませんが、このあたりはご自分で調べていただければと思います。
では、実際の作業手順です。

1. ダンプの取得

公式ドキュメントのコマンドどおりで取得できます。

pg_dump -h [OLD_DBEndPoint].ap-northeast-1.rds.amazonaws.com -p
 5432 -U [UserName] -Fc -b -v -f "./[BackupFileName].backup" [DBName]

今回、AWSのRDSインスタンスをアップグレードすることになるため、全く別のDBインスタンスを作成することになります。
ただ、このPostGISのアップグレードの処理は論理DBごとに実施する必要があるようで、弊社の環境では１つのインスタンスに４つの論理DBを作成しており、そのうち２つでPostGISをインストールしているのですが、このようなケースでは、２つの論理DB上でこの処理をやる必要がありました。

2. ダンプの取得2

pg_dump -h [OLD_DBEndPoint].ap-northeast-1.rds.amazonaws.com -p
 5432 -U [UserName] -s "./[BackupFileName].schema.backup" [DBName]

2つ目のダンプを取得します。
こちらは -s オプションを付けたとおり、 --schema-only です。
わざわざ別に取る理由としては、当初、１のダンプからのリストアだけを行ったのですが、構築後のDBを確認したところ、キーの制約やインデックスが何もコピーされていない事象が起きたからです。
この問題を解決すべく、データ構造のスキーマのみ、別にダンプを取りました。

3. シークエンス値の確認

psql [OLD_DBEndPoint] -U [ROLE_NAME] [DB_NAME]
SELECT * FROM information_schema.sequences; --対象スキーマ内のシークエンスの一覧を取得
select * from [SEQUENCE_NAME]; --古いDBのシークエンス値を確認

2.の理由と同じで、１のデータをリストアしただけでは、シークエンスの値が更新されず、主キーが1から採番されてしまい、もともと存在していたデータと重複する不具合が起きてしまいました。
このため、各シークエンスの値はリストア後に手動で設定を行うべく、ここで値を控えておきます。

4. 新DBの構築

AWS Console上でインスタンスを作成する作業はすでに完了しており、postgresql上のROLEについてもすでに設定済みの前提となります。
対象のインスタンスに接続した後、以下のSQLコマンドを実行。

psql [NEW_DBEndPoint] -U [ROLE_NAME] [DB_NAME]
create database [DB_NAME];
\c [DB_NAME];
alter schema public owner to [ROLE_NAME]; --もしかしたら不要かも
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
alter database [DB_NAME] owner to [ROLE_NAME]; --必要に応じて

5.データ構造のリストア

3.で取得したschema-only dumpのリストア

psql -h [NEW_DBEndPoint].ap-northeast-1.rds.amazonaws.com -U [ROLE_NAME] [DB_NAME] < ./[BackupFileName].schema.backup

6. データのリストア

PostGISの公式で提供されているツールを利用してリストアします。

perl /{postgis_path}/utils/postgis_restore.pl "./[BackupFileName].backup" | psql -h [NEW_DBEndPoint].ap-northeast-1.rds.amazonaws.com -U [ROLE_NAME] [DB_NAME] 2> ~/[ERROR_FILE].txt

7. シークエンス値の設定

以下のSQLを実行することでシークエンスの値を特定の値に設定することが可能です。

psql [NEW_DBEndPoint] -U [ROLE_NAME] [DB_NAME]
select setval('[SEQUENCE_NAME]', 〓);

〓には、3.で取得したシークエンスの値を入力します。

以上、これだけの手順を経て、やっと元のDBの再現が完了しました。
正直、ダンプやリストアの方法をもう少しシンプルにできるだろう？という気はしなくもないのですが、もともとインフラは専門ではないため、作業日数の都合もあり効果的なやり方やうまく行かない解決方法を突き詰めるところまではできませんでした。
AWS環境でやるときでも「これでできるよ」というやり方がありましたら教えていただけると幸いです。

PostGISが動いているPostgreSQLをAurora PostgreSQLにアップグレードした手順