はじめに
2019年～2020年にかけてサーバーリプレイスの移行作業(Windows Server 2008R2 + Oracle 11g → Windows Server 2016 + PostgreSQL 9.6)を行いました。
そして、PostgreSQL 9.6のサポート終了期限(EOL＝End Of Life)が2021/11と近づいてきたことにより、某N社のサポート最新版(2021/07現在)であるPostgreSQL 12にメジャーバージョンアップすることになりました。
| バージョン | 初期リリース日 | サポート終了期限 |
|---|---|---|
| 14 | 2021年09月30日 | 2026年11月12日 |
| 13 | 2020年09月24日 | 2025年11月13日 |
| 12 | 2019年10月03日 | 2024年11月14日 |
| 11 | 2018年10月18日 | 2023年11月09日 |
| 10 | 2017年10月05日 | 2022年11月10日 |
| 9.6 | 2016年09月29日 | 2021年11月11日 |
【2022/07/01追記】
PostgreSQL 12の移行作業
注意点
PostgreSQL 9.6からPostgreSQL 12にメジャーバージョンアップする上で間に10と11があります。
機能追加やパフォーマンス向上については気にする必要はないのですが、廃止された機能やフォルダ名変更やコマンドの変更など、移行作業をする上で気にする必要があります。
PostgreSQL 10
PostgreSQL 10では、いくつかのディレクトリ名や関数名などが変更されています。
メンテナンス系のシェルスクリプトや監視ツールで下記ディレクトリ名や関数名などをハードコードされている場合は、PostgreSQL10以降の名称に修正する必要があります。
* ログファイル出力先のディレクトリ名がpg_logからlogへ変更
* WALに関連するディレクトリ／関数／コマンドなどでxlogがwal、locationがlsnに変更
* コミットログの出力先ディレクトリ名がpg_clogからpg_xactへ変更
| PostgreSQL 9.6以前の名称 | PostgreSQL 10以降の名称 |
|---|---|
| pg_log | log |
| pg_xlog | pg_wal |
| pg_clog | pg_xact |
| pg_current_xlog_location | pg_current_wal_lsn |
| pg_xlogdump | pg_waldump |
| pg_receivexlog | pg_receivewal |
PostgreSQL 11
特になし
PostgreSQL 12
- WITH OIDが使えなくなった。WITH OIDSがあるテーブルがあるとpg_upgradeが実行できない
- recovery.confはなくなりpostgresql.confに統合
- リカバリ時はrecovery.signal、スタンバイ時はstandby.signal を置くようにする
pg_upgrade
今回データの移行作業には、メジャーアップデートツール「pg_upgrade」を使用しました。
pg_upgradeで使用するフォルダ構成
| 種類 | フォルダ |
|---|---|
| 旧データベースクラスタ | F:/Program Files/PostgreSQL/9.6/data |
| 新データベースクラスタ | F:/Program Files/PostgreSQL/12/data |
| 旧プログラムフォルダ | D:/Program Files/PostgreSQL/9.6/bin |
| 新プログラムフォルダ | D:/Program Files/PostgreSQL/12/bin |
| ログやバッチ生成フォルダ | D:\temp |
1.PostgreSQL 9.6のサービス停止
旧バージョンのPostgreSQL 9.6のサービスを停止します。
net stop postgresql-x64-12
2.PostgreSQL 12インストール
新バージョンのPostgreSQL 12(postgresql-12.7-2-windows-x64.exe)をインストール、その際にポートを一時的に「5433」にしておきます。
アップデート完了後にポートを標準の「5432」にします。
3.PostgreSQL 12のサービス停止
新バージョンPostgreSQL 12のサービスを停止します。
net stop postgresql-x64-12
4.pg_hba.confの編集
旧バージョンのPostgreSQL 9.6と新バージョンのPostgreSQL 12の両方のpg_hba.confファイルを編集し、データベースの認証を「md5」などから「trust」に変更します。
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/9.6/data" ^
-D "F:/Program Files/PostgreSQL/12/data" ^
-b "D:/Program Files/PostgreSQL/9.6/bin" ^
-B "D:/Program Files/PostgreSQL/12/bin"
6.エラー
pg_upgradeツールでは移行前にチェック処理が動作します。チェックに引っかかるような何か問題が発生した場合、上述したフォルダ「d:\temp」にログが出力されます。
- テーブルで型指定が明示的にされていないカラムがあった場合「tables_using_unknown.txt」ファイルが生成されます。
- OIDが含まれているテーブルがあった場合「tables_with_oids.txt」ファイルが生成されます。
- connection to database failed: fe_sendauth: no password supplied のエラーが表示されたときおそらく、パスワード認証で失敗しています。その場合、pg_hba.confファイルを編集し、データベースの認証を「trust」にしてください。
7.再実施
エラーがあった場合、エラー原因を解消して再実施します。
例えば、旧バージョンのPostgreSQL 9.6にOIDが含まれているテーブルがあった場合、WITHOUT OIDSしてOIDを除去します。
ALTER TABLE mst00001 SET WITHOUT OIDS;
8.確認
upgrateに成功したら、以下のように表示される。
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
analyze_new_cluster.bat
Running this script will delete the old cluster's data files:
delete_old_cluster.bat
pg_hba.confを元に戻す
pg_hba.confファイルのデータベースの認証を「trust」にしたのを元に戻します。
postgresql.confを編集
新バージョンのPostgreSQL 12のポートが「5433」でデフォルトポートではないため、postgresql.confファイルのport番号をデフォルトポートの「5432」変更します。
port = 5432
※postgresql.confの他の設定などもついでに変更しておくといいでしょう。
同様に旧バージョンのPostgreSQL 9.6のポートを「5433」に変更します。
port = 5433
新バージョン側を再起動
net start postgresql-x64-12
バージョンを確認する。
psql -V
psql (PostgreSQL) 12.7
データの確認
正しくデータが移行されていることを確認します。
統計情報の収集
統計データは移行されないため、pg_upgradeツールが「d:\temp」フォルダに生成した「analyze_new_cluster.bat」ファイルを実行し、オプティマイザの統計情報を収集しておきます。
analyze_new_cluster.bat
※ポート番号をデフォルトポートに変更しておかないと、バッチを実行して統計情報を収集したはずなのに実は実行されてなくて遅いってことになります。
後片付け
不要であれば、旧バージョンのPostgreSQL 9.6のデータベースクラスタ(dataフォルダ)を削除します。
pg_upgradeツールが「d:\temp」フォルダに生成した「delete_old_cluster.bat」ファイルを実行すると簡単です。
delete_old_cluster.bat
最後に
データ移行だけであれば、PostgreSQL 9.6からPostgreSQL 12のアップグレードは、pg_upgradeツールを使用することでスムーズに出来ました。
PostgreSQL 12からOIDが使用出来なくなったため、OIDを使用したアプリケーションの改修作業が必要になりました。