PostgreSQLの拡張機能の有効化(オンプレ/AWS RDS)

OracleともMySQLともちがう、「豊富な拡張」

PostgreSQLの特徴的なところだと思います。
なんなら最初から入っていてくれよ、と愚痴りたくなる程度に、結構めんどくさかったので、備忘録的な意味合いで残しておこうかと。

いや、この拡張機能を利用者自らが開発して組み込める柔軟性こそがPostgreSQLの良さだ、って話なんでしょうけど。

以降のお話は、PostgreSQL 10.6/10.7ベースです。
例として「pg_hint_plan」を扱います。

生PostgreSQLの場合

CentOS 7 + PostgreSQL 10.7 + pg_hint_plan10-1.3.3　でやってます。

rpmまたはtar.gzベースのソースを入手し、インストールする。

以下の場所から、対象ファイルをダウンロードします。
https://ja.osdn.net/projects/pghintplan/releases/

rpmの場合、以下のコマンドを実行します。

yum -y localinstall pg_hint_plan10-1.3.3-1.el7.x86_64.rpm

あたりまえですが、依存するモジュールも一緒に持ってきてくれるので楽ちん。

ソースの場合、以下のコマンドを実行します。

$ tar xzvf pg_hint_plan10-1.3.3.tar.gz
$ cd pg_hint_plan-1.3.3
$ make
$ su
# make install

データベースに登録

PostgreSQLの使用するデータベースに接続し、以下を発行。

CREATE EXTENSION pg_hint_plan

データベースの下のスキーマとして「pg_hint_plan」スキーマが追加されます。
（CREATE EXTENSIONでスキーマが追加されるか否かは、拡張機能による）

セッション単位にロード

PostgreSQLの使用するデータベースに接続し、以下を発行。

LOAD 'pg_hint_plan';

成功すると、「LOAD」とだけ表示されます。

全セッションがデフォルトでロードした状態にする

postgresql.conf（Postgresqlをyumで入れると「/var/lib/pgsql/10/data/postgresql.conf」にある）を編集して、再起動する。

shared_preload_libraries = 'pg_hint_plan'               # (change requires restart)

デフォルトでは、「#」でコメントアウトされていて、設定も空っぽ「''」ですので、有効化して拡張機能名を追加します。複数の拡張機能を起動時にLOADする場合は、カンマ区切りですね。

再起動後にPostgreSQLにログインして、正しく読み込まれているかを確認します。

SHOW shared_preload_libraries;

ここまでできたら、実際に拡張機能を使ってみて、想定した挙動になるか試しましょう。

AWS RDS/PostgreSQL＆Aurora/PostgreSQLの場合

OS層にアクセスできず、拡張機能を後付できない代わりに、最初から必要なファイルは置いてあります。
各バージョンで使用できる拡張は以下に書いてあります。
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts

Auroraも原則同じものが使えますが、
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.Compare.html
にRDS版とAurora版の差が書いてあります。

追記・修正:2019/04/30
~~そして、残念なことに、ここに書いてない拡張でも「ロジカルレプリケーション」に関係するものは、Auroraでは使えません。~~
Amazon Aurora with PostgreSQL Compatibility で論理レプリケーションのサポートを開始
のとおり、ロジカルレプリケーション対応になりました。
しかも、拡張ではなくPostgreSQLネイティブのロジカルレプリケーションである「CREATE PUBLICATION」「CREATE SUBSCRIPTION」コマンドによるレプリケーションに対応しています。

以下の「ベストプラクティス」で取り上げられているにもかかわらず有効化の方法がハッキリ書いてない「pg_hint_plan」の有効化手順は以下のようになります。

なお、RDS、AuroraともにPostgreSQL 10.6でやってます。

データベースに登録

PostgreSQLの使用するデータベースに接続し、以下を発行。

CREATE EXTENSION pg_hint_plan

データベースの下のスキーマとして「pg_hint_plan」スキーマが追加されます。
（CREATE EXTENSIONでスキーマが追加されるか否かは、拡張機能による）

全セッションがデフォルトでロードした状態にする

生PostgreSQLのように、「LOAD」コマンドを打つとエラーになります。。。

パラメータグループを編集し、インスタンスを再起動します。

RDSの場合、パラメータグループに全部入っています。

shared_preload_libraries = 'pg_hint_plan'
pg_hint_plan.enable_hint = 1
pg_hint_plan.enable_hint_table = 1 # 好みに応じて
pg_hint_plan.debug_print # 好みに応じて
pg_hint_plan.message_level # 好みに応じて
pg_hint_plan.parse_messages # 好みに応じて

なお、「shared_preload_libraries」だけがStaticパラメータです。

Auroraの場合、上記6つのパラメータは、クラスターパラメータグループと（インスタンス）パラメータグループの両方に存在しているみたいです。

「shared_preload_libraries = 'pg_hint_plan'」は、どちらに設定しても有効で、
「pg_hint_plan.enable_hint = 1」は、（インスタンス）パラメータグループ側に設定しないと、有効にならないようです。

なんだこれ。（心の声