今回の記事は、WordpressをPostgreSQLで動かすためのプラグイン PG4WPに関するお話です。

動機

２年ほど前から、「ソロ旅ねっと」というウェブサービスを趣味で運営しています。

先日、このサービスの一部としてブログ機能を追加しました。

ブログ機能を追加するにあたり、考えたことは以下の2つです。

• ブログシステムを自前で組むことはやりたくない
• 別途ブログサービス的なものを利用するのではなく、自前で建ててみたい（完全にエゴの世界）
• 現状赤字運営なので、新しくサーバを借りることなく、今動かしているサーバと同居させたい

ウェブサービスのバックエンドはPHPで組んでいるので、まず候補に挙がったのはWordpressでした。
しかし、ソロ旅ねっとではRDBとしてPostgreSQLを採用しているのですが、Wordpressが採用しているRDBはMySQLです。

さらに、ソロ旅ねっとのDBサーバはAWSのt4a-microインスタンスで動かしており、このスペックのサーバにPostgreSQLとMySQLも同居させるのも非現実的です。

PHPとPostgreSQLで動作するようなブログシステムはないものかと探してみていたら、どうやら、pg4wpというプラグインを使えばWordpressをPostgreSQLで動かすことができるらしいということが分かりました。
そこで、今回はそれを試してみることにしました。

最低限これだけは守りたかったこと

• Wordpressはなるべく最新版を使う
• Wordpress本体のソースは触りたくない(wp-includeの中身とか)。
• themeのカスタマイズ、functons.php の追記とか action/filterの追加はおｋ。
• 記事投稿や閲覧は普通のWordpressと同じ操作性にしたい。
  • 管理画面が動かないから記事投稿はcli経由のみとかは嫌。
• 画像はs3に置きたい(参照はCloudFront経由)。
• ACF(Advanced Custom Field)も使えたらいいな（活用するかはわかんないけど）。

結果

Wordpress 5.6（インストール時点での最新版）を PostgreSQL12 な環境で動かすことに成功しました。

• ACFも正しく動作している様子
• 画像リソースをS3に保存する仕組みもWP Offload Media Liteというプラグインを利用して対応できた
• Wordpress本体ソースの書き換えこそしていませんが、pluginのソースは何か所か改変してます
• 趣味プロジェクトだからいいけど、業務（特に自分以外の誰かが記事投稿とかするようなサイト）では絶対やりたくないｗ

やったこと

事前に用意するもの

• PHPが動作するウェブサーバ環境。
  • 自分はPHPは7.4を使用しています。もう少し古くても大丈夫だとは思います。
  • PHPの拡張モジュールとして、「php-pdo」「php-pgsql」の２つは必須です。また、mysqlの拡張モジュールは不要です。
• PostgreSQLサーバが動作しているサーバ（ウェブサーバと同じ環境でもおｋ）
  • 自分はPostgreSQL12を使用しています。バージョンは多少前後しても大丈夫だとは思います。
• Wordpress最新版（記事執筆時点では 5.6)のアーカイブ。

作業内容

割と試行錯誤色々やってみてできたという感じで、作業の記録もとっていません。
なので、この通りにコマンド打っていけば出来るよ的な、作業手順ドキュメントのようなものはありません。

ハマった時にどういう考え方で解決していけばいいかということの参考程度に読んでいただければ幸いです。

WordpressとPG4WPプラグインの導入は、以下のサイトの情報を参考に進めました。

WordPressインストール時のデータベースをPostgreSQLに指定してハマる

OSやホスティングサービスの管理画面の使い勝手などにも左右されるかと思いますが、ここに書かれた手順を自分の環境に置き換えながら作業を進めれば、最低限動作する環境は構築できるかと思います。

２点ほど補足です。

まず１点目ですが、Wordpressのバージョンは、インストール時点での最新版である5.6を使用しています。

次に２点目です。
PG4WPプラグインに関しても、上記のブログで公開されているものは使用していません。

上記ブログで紹介されていたプラグインは、Wordpressの4系で動作確認がされているようでした。

このプラグインがWordpress5系でも動作するかを検証ついでに試してみてもよかったのですが、このプロジェクトからさらにforkされたプロジェクトがいくつかあり、それらをざっと眺めていたところ、その中の１つに「wp5.1」という名前のmasterやdevelopにはマージされていないブランチを持っているプロジェクトを見つけました。

今回は、そのプロジェクトのwp5.1ブランチ最新版を使っています。
abatanx/postgresql-for-wordpress

内部エラーの修正

一見うまく動いているように見えるのですが、内部ではSQLの実行エラーがそこそこに発生しています。

ここでは、どのようなエラーが発生しているかの調査方法、エラーの解消方法を書いていきます。

PG4WPプラグインは、DB接続時にWordpress内部で発行されているデータベースへの接続パラメータを書き換え、MySQLと接続する代わりにPDO経由でPostgreSQLと接続していると思われます（未確認）。
さらに、Wordpress内部のSQL呼び出しをフックし、データベースに渡すSQL文をPostgreSQLでもエラーとならずに動作するような形に変換することで、WordpressのデータベースをPostgreSQLに完全に置き換えています。

ただしこの変換ももちろん完ぺきではなく、PostgreSQLで動作するSQLの変換に失敗すると、クエリーメソッドが失敗し、結果として内部エラーが発生します。

なお、Wordpressのデバッグ機能を無効にしている限りは、画面にエラーなどは表示されません。

その代わり、SQLの実行エラーが発生した場合には、変換前のSQLと変換後のSQL、DBサーバから受け取ったエラーメッセージがログファイルに記録されるようになっています。

ログファイルは wp-content/pg4wp/logs/　ディレクトリの中に「pg4wp_errors.log」という名前で保存されています。特にローテーションなどの仕組みは入っていないようなので、エラーを放置したまま運用を続けた場合、ログファイルが肥大化し、ディスク容量の圧迫や性能低下などを引き起こすかもしれません。怖い怖い。

つまり、やることは、「エラーログを見て、エラーとなったSQLがどうすればPostgreSQLで正しく動作するかを考え、その通りに動作するような変換ルールを追加する」ということになります。

SQLの変換は、pg4wp/driver_pgsql_install.php というファイルで行っています。
このファイル、中身は膨大な量の str_replace、preg_replaceの羅列です。

コードの内容は難しくなく、渡されたMySQL向けのSQLをPostgreSQLで動作するように文字列置換で修正し、修正結果を返却する巨大関数が定義されているだけです。SQL構文の違いやキーワードの差異だけではなく、型の違いの影響などもこのファイルで吸収されています。

Wordpressのfilterの仕組みが組み込まれていれば、プラグインソースには一切触れずに処理の追加ができたのですが、残念ながらそのようなコードにはなっていないため、ソースを直接書き換えてしまいます。

以下のようなルールを追加しています。

• USE INDEX(...) の除去
  • USE INDEX句の除去は、プラグインで一部対応しているのですが、特定名称のindexの参照を除去しているだけなので正規表現による文字置換を利用して完全に除去します
• straight_join を inner join に置き換え。
  • 複数レコードがヒットするような条件の場合なども単純な置き換えでいいのかなど、ちゃんと考えてはいない。
• ソース内のpg_attrdef.adsrcをpg_attrdef.adbinに置き換え。
  • PostgreSQL12でadsrc列が削除されたため、それ以降のバージョンを利用する場合書き換えが必要。

ここまでの修正で、Wordpressの基本機能は内部エラーもなく正しく動作するようになったはずです。

Wordpressの画像をS3で管理し、CloudFront経由で参照できるようにする

Wordpressには画像やドキュメントファイルなどを一元管理するためのメディアという仕組みが備わっており、これらのファイルは wp-content/uploads ディレクトリに格納されます。

これらのファイルの格納先をS3に変更することも、プラグインも「WP Offload Media Lite」というプラグインを利用すれば可能です。

普通のWordpress環境にこのプラグインを導入し、メディアファイルの格納先をS3に変更する方法はこの記事では書きません。手順などはググれば出てきます。

このプラグインを PG4WPな環境のWordpressで動かすのは一筋縄ではいきませんでした。

最終的には、WP Offload Media Liteプラグインの初回ロード時に自動で作成されるカスタムテーブル（プラグインが独自に作成するWordpress本体管理外のテーブル）を、手動で（データベースに直接create table SQLを発行することで）作成することで動作させることに成功しました。

この節では以降、特に記載のない限り「WP Offload Media Liteプラグイン」のことを単にプラグインといいます。

以下が、プラグインの初回ロード時に発行されるカスタムテーブルの生成SQLです。

$sql = "
CREATE TABLE {$table_name} (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    provider VARCHAR(18) NOT NULL,
    region VARCHAR(255) NOT NULL,
    bucket VARCHAR(255) NOT NULL,
    path VARCHAR(1024) NOT NULL,
    original_path VARCHAR(1024) NOT NULL,
    is_private BOOLEAN NOT NULL DEFAULT 0,
    source_type VARCHAR(18) NOT NULL,
    source_id BIGINT(20) UNSIGNED NOT NULL,
    source_path VARCHAR(1024) NOT NULL,
    original_source_path VARCHAR(1024) NOT NULL,
    extra_info LONGTEXT,
    originator TINYINT UNSIGNED NOT NULL DEFAULT 0,
    is_verified BOOLEAN NOT NULL DEFAULT 1,
    PRIMARY KEY  (id),
    UNIQUE KEY uidx_path (path(190), id),
    UNIQUE KEY uidx_original_path (original_path(190), id),
    UNIQUE KEY uidx_source_path (source_path(190), id),
    UNIQUE KEY uidx_original_source_path (original_source_path(190), id),
    UNIQUE KEY uidx_source (source_type, source_id),
    UNIQUE KEY uidx_provider_bucket (provider, bucket(190), id),
    UNIQUE KEY uidx_is_verified_originator (is_verified, originator, id)
);";
dbDelta( $sql );

このSQLは、PostgreSQLでは以下のような理由により動作しません。

• 主キーの宣言方法が違う
• PostgreSQL には UNSIGNED は存在しない
• TINYINT 型は存在しない。これと同等の SMALLINT 型を使う必要がある
• BOOLEAN型はMySQLでは整数値（0 or 1)だが、PostgreSQLでは true / false という値を持てる
• unique制約の宣言方法が違う
  • 上のCREATE TABLE文には、おそらくunique制約以外の利用用途で書かれたUNIQUE KEY 文も混ざっている気がするけど

これを、（意図をくみ取ったうえで）PostgreSQLで動作するSQLに置き換えると以下のようになるかと思います。

CREATE TABLE wp_as3cf_items (
id BIGSERIAL,
provider VARCHAR(18) NOT NULL,
region VARCHAR(255) NOT NULL,
bucket VARCHAR(255) NOT NULL,
path VARCHAR(1024) NOT NULL,
original_path VARCHAR(1024) NOT NULL,
is_private SMALLINT NOT NULL DEFAULT 0,
source_type VARCHAR(18) NOT NULL,
source_id BIGINT NOT NULL,
source_path VARCHAR(1024) NOT NULL,
original_source_path VARCHAR(1024) NOT NULL,
extra_info TEXT,
originator INTEGER NOT NULL DEFAULT 0,
is_verified SMALLINT NOT NULL DEFAULT 1
);
create index on wp_as3cf_items (path);
create index on wp_as3cf_items (original_path);
create index on wp_as3cf_items (source_path);
create index on wp_as3cf_items (original_source_path);
create unique index on wp_as3cf_items (source_type, source_id);
create index on wp_as3cf_items (provider, bucket);
create index on wp_as3cf_items (is_verified, originator);

やったことは以下の通りです

• 主キーカラムの型をbigserialに置き換え
• UNSIGNEDを除去する。この時、対象カラムの型サイズを１段階上げる
• BOOLEAN型は SMALLINT型に置き換え
  • BOOLEAN型のままにしておくと、例えば、MySQL前提で作られている select * from wp_as3cf_items where is_verified = 0`みたいなSQLがエラーとなってしまうため
• 末尾のunique key制約を、元のSQL文のUNIQUE KEY uidx_path (path(190), id) は、そもそもidが絶対uniqueなので、これはおそらくpathカラムにindexを張りたいんだろうなーというような意図をくみ取りつつ、create (unique)? index 宣言に置き換えていく

これで動作するかと思いきや、なぜか管理画面のプラグインの設定画面でエラーが表示されます。
しょうがないので例のログを参照しつつ調べてみます。

このプラグイン、下記のようなコードでセットアップが適切に完了しているかのセルフチェックをしていました。

if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) ) === $table_name ) {
    $db_init_status[ $blog_id ]['status'] = true;
}

SHOW TABLESは PostgreSQLでは使えません。

同等のことはpg_tablesを参照することで実現できるのですが、、、

面倒臭くなってきていたので

if ( true ) {
    $db_init_status[ $blog_id ]['status'] = true;
}

とりあえずif条件外しました。。。。

これで、ようやく、やってみたかったことが実現できました。

一人旅が捗るWebサービスを思いついたので、開発から公開まで全部一人でやってみた話

一人で泊まれる宿検索｜ソロ旅ねっと（外部リンク）
ソロ旅にっき（もうちょっと記事を増やしたらちゃんと公開予定）（外部リンク）