TECHSCORE BLOG

クラウドCRMを提供するシナジーマーケティングのエンジニアブログです。

psqlをカスタマイズして面倒を減らそう

大規模なシステムでは、パフォーマンスやセキュリティ上の制約により、複数のデータベースをシャーディング(水平分割)するアーキテクチャを選択するケースは多いかと思います。
多くのPostgreSQLユーザーはpsqlコマンドを使って調査や保守を行いますが、シャーディングにより沢山のデータベースを管理するようになると、データベースの接続先を入力するのはとても面倒な作業となります。
エンジニアたるもの、「面倒だ」と思う心は大切にすべきです。
今回はシャーディングされたデータベースをpsqlで管理するための工夫をご紹介します。

寺岡 佑起(テラオカ ユウキ)
AWS Summit 2022で発表してきました。
3年分くらい緊張しました💦


psqlのソースにパッチを当ててカスタマイズ

psql には \c コマンドというものがありまして、\c db1 と打つと、psqlのコンソール内でdb1データベースへ接続を切り替えることができます。
Synergy!ではpsqlのソースコードにパッチを当てて、与えられた引数に応じて接続先を検索し、切り替える改造を施して利用してきました。
しかし、OSSのソースコードにパッチを当ててしまうと、バージョンアップに追従するのがとても大変です。
PostgreSQLクライアントプロトコルは変わらないため、古いバージョンのpsqlでもクエリの実行は可能です。
しかし、テーブル情報などのカタログテーブルは頻繁に変更が行われています。
データベースのバージョンアップ後に古いpsqlで接続を行うと、\d table_name でテーブル情報を確認しようとしたらエラーが起きる。といった不幸が多発するようになります。
psqlもサーバに合わせてバージョンアップしていきたいのですが、そのためにはパッチを当てた箇所の変更点を確認し適切に作成し直す必要があります。
言うまでもなく、こんな自己都合のパッチは本家に取り込んでもらえません。このアプローチを取る限りバージョンアップの度にパッチ作業が必要です。

標準機能だけで実現できないか?

継続的な面倒を避けるための苦労は買ってでもしろ。がモットーな私は、この面倒を受け入れることができませんでした。
そこで、なんとかパッチを当てずに、psqlの標準機能だけでやりたい事を実現する方法を検討しました。

まず、psqlのドキュメント日本語訳) を一通り読んだ上で、使えそうな機能をピックアップします。

psqlrc

psqlコマンド開始時に自動的に読み込まれるスクリプトを用意することができます。~/.bashrcみたいなものです。
システム構成ディレクトリにあるpsqlrcファイルや ~/.psqlrc が自動的に読み込まれる他、PSQLRC 環境変数で指定したスクリプトを実行させることも可能です。

メタコマンド

\ からはじまるpsqlのコマンドをメタコマンドと呼びます。psqlユーザー御用達の \d もテーブル情報を表示するメタコマンドです。

\set [ name [ value [ ... ] ] ]

psql変数を設定します。valueを複数指定した場合は連結された文字列が変数に設定されます。引数無しで呼び出した場合は設定された全変数が表示されます。
\set や後述の \gset,\prompt などで設定できるpsql変数は、psql内のほとんどの箇所で展開することができます。
SQL内で部分的に展開する、SQL文をまるごと格納して実行する、メタコマンドを格納して実行する、など用途は多岐にわたります。
展開の構文は :変数名 です。SQL上でクオート済みの値として展開する :'変数名' :"変数名" 構文も便利です。

\gset [ prefix ]

クエリバッファをサーバーに送信し、クエリの出力をpsql変数に設定することができます。発行するクエリが返却するレコードは必ず一行である必要があります。 \g から始まるメタコマンドは、SQL文の末尾の;の変わりに指定するもので、対象のクエリに対して特別な処理を行います。
今回は使いませんが、SQLで組み立てたSQLを発行する \gexec はイチ推しの機能です。

\c or \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host ] [ port ] | conninfo ]

先程少し触れたように、psqlコンソール内で接続先を切り替えるために使います。
接続情報は、空白区切り、URL型式のconninfoパラメータのいずれかで指定します。

\i or \include filename

ファイルに記述された一連のpsqlコマンドを発行します。頻繁に使う処理はスクリプト化して保存しておくと良いでしょう。
psql実行時のカレントディレクトリを意識せずに呼び出すには、ファイル名をフルパスで記述する必要があるためpsqlrcで変数化してしまうのをおすすめします。
以下のような変数を作っておくと :hoge だけで実行できます。

\set hoge '\\i /path/to/script.psql'

\prompt [ text ] name

コンソールで入力した文字列を変数に格納します。
\i はマクロのように使えますが、引数を渡すことができません。
動的な挙動はスクリプト内で利用するpsql変数を予め設定しておくことで制御することができます。
しかし、制御のための変数名を覚えるのも、事前に \set で設定するのも非常に面倒です。
そんな面倒は、スクリプト内で \prompt を使いコンソール上で入力を促すようにすれば改善できます。

改めてpsqlについて調べたところ、駆使すればかなり複雑な処理が実現可能なことがわかりました。
目的を達成するため、知らなかった機能について調べるのはとても楽しい時間でした。エンジニアの醍醐味ですね。

実際にやってみる

実験のための環境を準備します。
複数のインスタンスを立てるのが面倒なので、DBインスタンスを共用しデータベースを切り替えるパターンでシャーディングを行います。
管理テーブルにはURL型式の接続文字列を持たせているため、インスタンスが分かれても対応できます。

# dockerでpostgresを起動
$ docker run --name instance1 -p 5432:5432 -e POSTGRES_PASSWORD=postgres --rm -d postgres

# postgresに接続し、データベースの準備
$ psql -h localhost -p 5432 -U postgres 
-- データベースを作成
CREATE DATABASE db1;
CREATE DATABASE db2;

-- データベースごとの接続先を管理するテーブルを作成
CREATE TABLE databases (id varchar primary key, conninfo varchar);
INSERT INTO databases(id, conninfo) VALUES
   ('db1', 'postgresql://postgres:postgres@localhost:5432/db1')
  ,('db2', 'postgresql://postgres:postgres@localhost:5432/db2');

\q

~/.psqlrc を作成し、以下の2変数を設定します。

-- 接続先管理テーブル参照時の接続先
\set control_conninfo 'postgresql://postgres:postgres@localhost:5432/postgres'
-- :c 変数の展開により change_user.psql を実行する
\set c '\\i /tmp/change_user.psql'

続いて、:c で読み込まれる /tmp/change_user.psql を作成します。

-- データベースIDを入力させ、current_dbid 変数に設定
\prompt 'input db id: ' current_dbid
-- 管理テーブル参照のために接続変更
\c :control_conninfo
-- 管理テーブルを読み込み、current_conninfo 変数に設定
SELECT conninfo FROM databases WHERE id = :'current_dbid' \gset 'current_' 
-- 接続先をcurrent_conninfoに切り替え
\c :current_conninfo

なんと2ファイル、(コメントを除いて)たった6行で実装できてしまいました。
それでは実際に試してみましょう。

$ psql -h localhost -p 5432 -U postgres 
psql (14.3 (Ubuntu 14.3-1.pgdg20.04+1)、サーバー 14.2 (Debian 14.2-1.pgdg110+1))
"help"でヘルプを表示します。

postgres=# :c
input db id: db1
psql (14.3 (Ubuntu 14.3-1.pgdg20.04+1)、サーバー 14.2 (Debian 14.2-1.pgdg110+1))
データベース"postgres"にユーザー"postgres"として接続しました。
psql (14.3 (Ubuntu 14.3-1.pgdg20.04+1)、サーバー 14.2 (Debian 14.2-1.pgdg110+1))
データベース"db1"にユーザー"postgres"として接続しました。
db1=# :c
input db id: db2
psql (14.3 (Ubuntu 14.3-1.pgdg20.04+1)、サーバー 14.2 (Debian 14.2-1.pgdg110+1))
データベース"postgres"にユーザー"postgres"として接続しました。
psql (14.3 (Ubuntu 14.3-1.pgdg20.04+1)、サーバー 14.2 (Debian 14.2-1.pgdg110+1))
データベース"db2"にユーザー"postgres"として接続しました。
db2=#

切り替える度に管理テーブルDBへの接続が行われるためログが二重に出ますが、指定したデータベースに接続が切り替わっているのがわかります。
実案件ではさらにカスタムしたものを使っており、管理テーブルDBへの接続ログを QUIET 変数の調整で黙らせる。などの工夫も入れています。

まとめ

定形処理をpsqlスクリプトに纏めて \set でショートカットを作っておく方法を覚えると、psqlのカスタマイズの幅がぐっと広がるのを感じました。
この記事が皆様の面倒の解消に役立てば幸いです。
以上、エンジニアよ、怠惰であれ。というお話でした。

シナジーマーケティング株式会社では一緒に働く仲間を募集しています。