はじめに

2019年～2020年にかけてサーバーリプレイスの移行作業(Windows Server 2008R2 + Oracle 11g → Windows Server 2016 + PostgreSQL 9.6)を行いました。
そして、2021/07からPostgreSQL 12のアップグレードの準備を始め、全国の各拠点に展開していきました。

時は経ち、次のサーバーリプレイスの移行作業(Windows Server 2022 + PostgreSQL 14)の準備を行うことになりました。

PostgreSQL 14の移行作業

前回はPostgreSQL 9.6からPostgreSQL 12でoid列廃止やフォルダ構成変更など変更点があったため面倒な部分がありましたが、PostgreSQL 12からPostgreSQL 14ではそのような面倒なことがありません。

PostgreSQL 13の主な新機能と変更点

B-Treeインデックスの性能向上
新しい実行プラン
パーティションテーブル機能の拡張
パラレルVACUUM
進捗レポートビューの拡張
pgbenchの拡張
pg_rewindの拡張
SQL機能の追加

PostgreSQL 1４の主な新機能と変更点

Btreeインデックスの肥大化防止
式に対する拡張統計
LZ4による列のTOAST圧縮
postgres_fdwの拡張
ロジカルレプリケーションの改善
マルチ範囲型
再帰CTE問い合わせにおけるSEARCH／CYCLE句のサポート
アイドルセッションのタイムアウト機能
新システムロールpg_read_all_data/pg_write_all_data
COPY進捗表示、WAL書き出しやロジカルレプリケーションの統計表示
pg_amcheckコマンド
ANALYZEコマンドのパフォーマンス改善により大幅に高速化
password_encryptionのデフォルトがmd5からscram-sha-256に変更

pg_upgrade

今回データの移行作業には、メジャーアップデートツール「pg_upgrade」を使用しました。
pg_upgradeで使用するフォルダ構成

種類	フォルダ
旧データベースクラスタ	F:/Program Files/PostgreSQL/12/data
新データベースクラスタ	F:/Program Files/PostgreSQL/14/data
旧プログラムフォルダ	D:/Program Files/PostgreSQL/12/bin
新プログラムフォルダ	D:/Program Files/PostgreSQL/14/bin
ログやバッチ生成フォルダ	D:\temp

1.PostgreSQL 12のサービス停止

旧バージョンのPostgreSQL 12のサービスを停止します。

net stop postgresql-x64-12

2.PostgreSQL 14インストール

新バージョンのPostgreSQL 14(postgresql-14.4-1-windows-x64.exe)をインストール、その際にポートを一時的に「5433」にしておきます。
アップデート完了後にポートを標準の「5432」にします。

3.PostgreSQL 14のサービス停止

新バージョンPostgreSQL 14のサービスを停止します。

net stop postgresql-x64-14

4.pg_hba.confの編集

旧バージョンのPostgreSQL 12と新バージョンのPostgreSQL 14の両方のpg_hba.confファイルを編集し、データベースの認証を「md5」や「scram-sha-256」などから「trust」に変更します。

pg_hba.conf

host    all    all    127.0.0.1/32      trust
host    all    all    0.0.0.0/0     trust

0.0.0.0/0の部分はあくまで例となりますので、正しいIPを登録するようにしてください。

5.pg_upgradeを実行

pg_upgradeツールを実行したカレントディレクトリにログや一括analyze用バッチファイルと削除用のbatファイルが生成されます。
pg_upgradeツールから書き込み可能なフォルダ(例 d:\temp)を作成して、Everyoneの権限を付与します。
管理者権限で実行する

例

if not exist "d:\temp" (
    mkdir d:\temp
)
icacls d:\temp /grant Everyone:"(OI)(CI)(F)"

今回はカレントディレクトリを「d:\temp」にして、pg_upgradeツールを実行します。
pg_upgradeツールは、postgresユーザで実行する必要があります。
オプションの英小文字が旧バージョン用、英大文字は新バージョン用になります。

例

pg_upgrade.exe ^
-U postgres ^
-d "F:/Program Files/PostgreSQL/12/data" ^
-D "F:/Program Files/PostgreSQL/14/data" ^
-b "D:/Program Files/PostgreSQL/12/bin" ^
-B "D:/Program Files/PostgreSQL/14/bin"

6.エラー

pg_upgradeツールでは移行前にチェック処理が動作します。チェックに引っかかるような何か問題が発生した場合、上述したフォルダ「d:\temp」にログが出力されます。

テーブルで型指定が明示的にされていないカラムがあった場合「tables_using_unknown.txt」ファイルが生成されます。
connection to database failed: fe_sendauth: no password supplied のエラーが表示されたときおそらく、パスワード認証で失敗しています。その場合、pg_hba.confファイルを編集し、データベースの認証を「trust」にしてください。

7.再実施

エラーがあった場合、エラー原因を解消して再実施します。
例として、pg_hba.confで認証を「trust」に変更していなかったなど。

8.確認

upgrateに成功したら、以下のように表示される。

環境にALTER EXTENSIONコマンドで更新すべき機能拡張があります。以下のファイル
    update_extensions.sql
を、psqlを使用してデータベースのスーパーユーザーとして実行することで、これらの機能拡張
が更新されます。


アップグレードが完了しました
----------------
オプティマイザーの統計情報は、pg_upgrade では転送されません。
新サーバーを起動した後、以下のコマンドを実行することを検討してください:
    D:/Program Files/PostgreSQL/14/bin/vacuumdb -U postgres --all --analyze-in-stages

このスクリプトを実行すると、旧クラスタのデータファイル delete_old_cluster.batが削除されます:

pg_hba.confを元に戻す

pg_hba.confファイルのデータベースの認証を「trust」にしたのを元に戻します。

PostgreSQL 12でデータベースの認証を「md5」を使用していた場合、PostgreSQL 14でもデータベースの認証を「md5」にしておかないと認証出来なくなります。

postgresql.confを編集

新バージョンのPostgreSQL 12のポートが「5433」でデフォルトポートではないため、postgresql.confファイルのport番号をデフォルトポートの「5432」変更します。

postgresql.conf

port = 5432

※postgresql.confの他の設定などもついでに変更しておくといいでしょう。

同様に旧バージョンのPostgreSQL 12のポートを「5433」に変更します。

postgresql.conf

port = 5433

新バージョン側を再起動

net start postgresql-x64-14

バージョンを確認する。

psql -V
psql (PostgreSQL) 14.4.1

データの確認

正しくデータが移行されていることを確認します。

統計情報の収集

統計データは移行されないため、pg_upgradeツールが「d:\temp」フォルダに生成した「update_extensions.sql」ファイルをpsqlを使用してデータベースのスーパーユーザーとして実行し、オプティマイザの統計情報を収集しておきます。

update_extensions.sql

\connect postgres
ALTER EXTENSION "pg_stat_statements" UPDATE;

psql -U postgres -f update_extensions.sql

※ポート番号をデフォルトポートに変更しておかないと、バッチを実行して統計情報を収集したはずなのに実は実行されてなくて遅いってことになります。

後片付け

不要であれば、旧バージョンのPostgreSQL 12のデータベースクラスタ(dataフォルダ)を削除します。
pg_upgradeツールが「d:\temp」フォルダに生成した「delete_old_cluster.bat」ファイルを実行すると簡単です。

delete_old_cluster.bat

最後に

データ移行だけであれば、PostgreSQL 12からPostgreSQL 14のアップグレードは、pg_upgradeツールを使用することでスムーズに出来ました。
今回のアップグレードで唯一嵌った点として、PostgreSQL 14から「password_encryptionのデフォルトがmd5からscram-sha-256に変更」になったため、pg_hba.confファイルで「trust」から元に戻す時に「scram-sha-256」にしたら認証してくれずデータベースに接続できなくて焦りました。「md5」に書き換えたらデータベースに接続できるようになりました。
認証方式を「scram-sha-256」に変更するかどうかは、アップグレードが終わってから考慮すればいいです。