なんちゃってエンジニアのちょっとした楽する為の工夫：PostgreSQL Advent Calendar #19

DB PostgreSQL

PostgreSQL Advent Calendar 2011の19日目を担当します@dai_yamashitaです。

pgpoolでmd5認証が通すことができない（ほんと誰か教えてください。。m(_ _)m) くらいの
（PostgreSQLでmd5認証ぐらいはできるんですよ。。。）なんちゃってエンジニアですが、
そんなエンジニアが日々の運用を楽にするちょっとした工夫を今回ご紹介してみます。

今回のAdventカレンダーでもSELECT * FROMWeb-APIやmulticornを使ってPythonでfdwを実装する
などでも紹介されていますが、FDWがPostgreSQL9.1ではサポートされました。
それに今までの導入とは異なり、EXTENSIONを使用して、DBを拡張（って表現で正しいのかな？？）することになっています。

すでに、複数のDBを所有していて、全DBにそのEXTENSION適用しないといけないとかなると、イチイチコマンド全DBに適用してられねぇ〜ってなるので、
今回は、それを題材にちょっと工夫をしてみます。

ちなみに、自分の環境では、PostgreSQLは、ソースコンパイルで必ず導入してます。
管理は、Paco等を使ってたりしますが、最適化の期待と、共存等の考慮を含めると一番楽だと思ってのことです。

prefixについては、

/usr/local/pgsql/PGVERSION/
環境が複数必要な場合は、
/usr/local/pgsql/PGVERSION_PORT/

というような構成にしています。

では、前提を上記とした上で、ある程度の汎用性を持たせる為に、EXTENSIONについては指定できるような形を目指します。
作りに関しては、安易に全DBを取ってきて、それにCREATE EXTENSIONしてしまおう！！っていうなんちゃってレベルを晒して、以下のようなシェルスクリプトを作って見ました。
今回の環境は、PostgreSQL 9.1.2で行なっているので、環境にあわせて、適宜変更して下さい。

#!/bin/bash

# author: dai_yamashita

# pg_alldb_create_extension.sh

CMDNAME=`basename $0`

while getopts f:? opt
do
  case $opt in
    "f" ) EXTENSION="$OPTARG";;
    * ) echo "Usage: $CMDNAME [-f EXTENSION]" 1>&2
          exit 1 ;;
  esac
done

if [ "$EXTENSION" = "" ] ; then
    echo "  Error: Please INPUT EXTENSION..."
    exit 1
fi

# PGVERSION is the full package version, e.g., 9.0.0
# Note: the specfile inserts the correct value during package build
PGVERSION=9.1.2
PGPORT=5432
# Set defaults for configuration variables
PGENGINE=/usr/local/pgsql/${PGVERSION}/bin

# Who to run the postmaster as, usually "postgres".  (NOT "root")
PGUSER=postgres

for dbname in `su - $PGUSER -c "${PGENGINE}/psql -p ${PGPORT} -d template1 -q -t << EOF
select datname from pg_database where not datname in ('template0') order by datname;
EOF"
`
do
 echo "database ${dbname} file run ..."
 echo su - ${PGUSER} -c "${PGENGINE}/psql -U postgres -p ${PGPORT} -d ${dbname} -f ${FILE}"
 su - ${PGUSER} -c "${PGENGINE}/psql -U postgres -p ${PGPORT} ${dbname} -c 'CREATE EXTENSION ${EXTENSION}'"
done;

echo "all run !!"

PostgreSQLでは、クエリでDBの情報を取ってこれるので、ホント便利ですね！！

でも、作りとしては、イマイチな部分というか、突貫なので、lockファイル作ってtrap処理とかしてなかったり、
もっといい方法あると思いますので、ご指摘待ってます。m(_ _)m

では、直接の題材として、pgcyrptoの導入を全DBにしてみたいとます。
導入したソースは、/usr/local/src/にあるものとします。

cd /usr/local/src/postgresql-9.1.2/contrib/pgcrpto
make
make install

すると、

/usr/local/pgsql/9.1.2/share/extension/

配下に、pgcrypto関係のものができているハズです。

pgxnclientとか使ったりする場合は、

export PG_CONFIG=/usr/local/pgsql/9.1.2/bin/pg_config
export PATH=$PATH:/usr/local/pgsql/9.1.2/bin

を実施して、環境変数を一時的に通してしまったりするとうまく行きやすいと思います。
(Makeファイル書き換えても良いと思いますが、案外この方法でなんとかなります）

適宜

export -p
とか
env

して、状況を確認して下さい。

それでは、先程作成した、スクリプトを実施してみます。

sh pg_alldb_create_extension.sh -f pgcrypto

すでにあるDB全てにコマンドが実行されているのが確認できるかと思います。

あと、ちょこっといじると、クエリの実行ができたり、バックアップもpg_dumpallですべてを取って終わりではなく、
カスタム形式で保存できたりと、今後も活用できるかと思います。

明日はLET__IT__RIDEさんです。宜しくお願いします。

引き続き PostgreSQL Advent Calendar 2011をお楽しみください。