PostgreSQL : してログ

12

自分用なので色々と中途半端な記事です。

データベースの作成
createdb DBNAME --encoding=UTF8 --lc-collate=ja_JP.UTF-8 --lc-ctype=ja_JP.UTF-8 --template=template0
データベースの複製

既存データベースをテンプレートにして新しいデータベースが作成できます。

createdb -T DB_ORG DB_REP

データベースが稼働中なら、下記のコマンドで複製できます。

pg_dump -Ft DB_ORG | pg_restore -c -d DB_PEP
日付のフォーマット
表現結果
桁揃えto_char(now(),'YYYY-MM-DD HH24:MI:SS')2019-04-21 09:31:22
前ゼロ抜きto_char(now(),'YYYY-FMMM-FMDD FMHH24:MI:SS')2019-4-6 9:32:02
ISO8601to_char(now(),'YYYY-MM-DD"T"HH24:MI:SS"Z"')2019-11-14T18:37:28Z
データベースのタイムゾーンを変更する
alter database DBNAME set timezone to 'Asia/Tokyo';
select pg_reload_conf();
あるソート順において対象データが何番目にあたるか調べる

例えば、sorted_table で update_time の降順で並び変えたとき、id=24 のデータが何番目に来ているかを調べるには下記のような SQL になります。

select *
from (select row_number() over (order by update_time desc) as rowno,id from source_table) as sorted_table
where id=24
その他
評価が null の場合に別の値を返す(nvl 代替)
coalesce(column,0)
降順の並べ替えで NULL 値を最後にする
order by update_time desc nulls last

文字列フィールドの改行コードを取り除くSQLです。trim 関数を使いますがデフォルトだと空白文字だけなので、改行コードを指定してあげます。

update accesslog set useragent=trim(both E'\r\n' from usetagent);

最新の PostgreSQL 9.4 を試してみようと、Windows Server 2003 にインストールしてみたところ、“Problem running post install step...the Database Cluster Initialisation failed.”というエラーで正しく完了しない問題が発生しました。 試しに、8.4 を含めここにあるいくつかのバージョンを試しましたが、いずれもダメでした。 でも不思議なことに、少し前にダウンロードしてあった 9.3.2 は何もしなくてもインストールできます。 最終的には、ここを見てインストールに成功しましたので、その手順を書き留めておきます。

PostgreSQL のインストール
  • インストールに失敗していたら、いったんアンインストールしておきます
  • インストールディレクトリを作成します(一回失敗していれば、C:\Program Files\PostgreSQL が残っているのでそれでも良い)
  • インストールディレクトリに Administrators 権限を持ったユーザーにフルコントロール権限を与えます(適当なユーザーが無ければ、postgres などユーザーを作成し Administrators グループに追加します)
  • インストーラを右クリックして、「別ユーザーとして実行」にて起動します
  • 権限を与えたユーザーを指定してインストーラを実行します
  • これで正常にインストールできるはずです
PostGIS のインストール
  • インストール先で示されるパスが、バックスラッシュで区切られているので、すべて¥マークに直します
  • これで正常にインストールされるはずです

テーブルの定義(データ型、フィールド長、カラムの定義順、デフォルト値、NOT NULLなど)を一括して取得する SQL 文です。 テーブル名は、relname = 'table_name' の部分で指定します。 前の記事の方法より、こちらのほうが簡単で他の情報も取得できます。

select
	*
from
	information_schema.columns
where
	table_catalog = current_database()
		and
	table_name = 'table_name'
order by
	ordinal_position
;

テーブルを構成する、各フィールドのデータ型を取得するSQL文は、下記のようなります。 テーブル名は、relname = 'table_name' の部分で指定します。 プログラム内でこれらを取得することで、入力値の桁数や、データ型のチェックに役立ちます。

select
	at.attname,
	format_type(at.atttypid, at.atttypmod)
from
	pg_attribute as at
		left join pg_type as tp on (at.atttypid = tp.oid)
where
	at.attnum > 0 and
	at.attrelid = (select relfilenode from pg_class where relname = 'table_name')
order by
	at.attnum
;

PostgreSQL の pg_dump や pg_restore といった CLI 管理ツールは、PowerShell ではうまく動かいないようです。 Windows のコマンド・プロンプトに代わるコマンド・ライン・インターフェイスの PowerShell ですが、コマンド・プロンプトの代わりにはならないということが分かりました。

無理に使おうとした場合、下記のような警告やエラーが出て、作成されたバックアップファイルも壊れています。 Windows Server 2012 では、PowerShell が標準という扱いのようなのですが、似たような見た目に騙されると痛い目に遭います。 マイクロソフトに言いたいのは、もっと保守的にアップデートして欲しい、ということ。 特に、サーバー系 OS はね。

pg_dump を PowerShell で使った場合に出る警告
pg_dump: [カスタムアーカイバ] 警告: ftellで想定位置との不整合がありました -- ftellが使用されました
PowerShell + pg_dump で作ったバックアップファイルを pg_restore でリストアした場合のエラー
[アーカイバ]ファイルヘッダにマジック番号がありませんでした。

まさか、PDO のバグだったとは。

PostgreSQL に格納されたバイナリデータ(bytea)を PDO で取得しようとすると、文字列としてしか取得できないバグがあり、5.2.6 で報告されたのに、5.3.28 でも治っていないようです。 おかげで、急ぎのプロジェクトなのに、かなりの時間をロスしてしまいました。 通常、バイナリデータの取得は、以下のように書きますが、このバグにより「xffd8ffdb00430005030404040....」のような文字列が返ってきます。

$sth = $pgsql->prepare("select id,mime,img from t_image limit 1");
$sth->execute();
$sth->bindColumn('id', $id, PDO::PARAM_INT);
$sth->bindColumn('mime', $mime, PDO::PARAM_STR);
$sth->bindColumn('img', $img, PDO::PARAM_LOB);
if ($sth->fetch(PDO::FETCH_BOUND)) {
	header("Content-Type: {$mime}");
	fpassthru($img);
	exit;
}

PDO を使う限り解決策がないため、ネイティブ系の関数を使うか、文字列をバイナリ列に直すしか無いと思われます。 この文字列をバイナリ列に直すコードは、下記のようなものになります。

$sth = $pgsql->prepare("select id,mime,img from t_image limit 1");
$sth->execute();
$sth->bindColumn('id', $id, PDO::PARAM_INT);
$sth->bindColumn('mime', $mime, PDO::PARAM_STR);
$sth->bindColumn('img', $img, PDO::PARAM_LOB);
if ($sth->fetch(PDO::FETCH_BOUND)) {
	header("Content-Type: {$mime}");
	fgets($img,2);
	$data = fgets($img);
	echo pack('H*',$data);;
	exit;
}

文字列で返る以上、扱うデータ量は倍になりますし、pack という重めの関数を用いなければならないため、速度や効率面ではあまりよろしくありません。 ネイティブ系の関数で書ける場合は、そちらのほうがお勧めです。 なお、fgets($img,2); の部分は先頭に入る「x」の文字を捨てるためで、第2引数が「2」なのは、この関数が第2引数マイナス1バイトを読み込むという仕様のためです。 これに気づくのに、2時間ほど要してしまいました(^_^;)

PostgreSQL に接続中のユーザー名を取得する方法です。セッションユーザーは、データベースセッションを開始したユーザー、カレントユーザーは、現在の実行ユーザーとなります。スーパーユーザーでなければ、ユーザー変更できないので、通常は同じユーザーが入ります。

セッションユーザ名
select session_user;
現在の実行コンテキストのユーザ名
select current_user;

他にも、スキーマ名、IPアドレスなど様々な情報が取れますが、それらについてはこちらをご覧ください。ストアドプロシージャなどで活用すると、ログ取得などなど、色々と便利そうです。

以前のバージョンでは、テンプレートを元に新しい空間データベースを作成しましたが、PostgreSQL 9.1 の CREATE EXTENSION で機能拡張としてインストールする形になりました。このため、PostGIS 2.1 では従前のテンプレートが無く、代わりにデータベースを作成した後、下記のコマンドにより空間データベースを構築します。

create extension postgis;

他にも、経路探索を行う pgrouting、トポロジー型をサポートする postgis_topology などがあるようです。また、日本では使えませんが、ジオコーディング用の機能拡張も用意されています。これらも同様に、CREATE EXTENSION で必要に応じてインストールできます。

PostgreSQLのシステムテーブルから、存在するテーブルやビューの一覧を得ることができます。 下記、SQL文の結果で、スキーマ名(nspname)、テーブルやビューの名前など(relname)、オブジェクトの種類(relkind)が返ります。 relkind は、テーブル(r)、ビュー(v)になります。

select
  c.oid,
  n.nspname,
  c.relname,
  c.relkind
from
  pg_catalog.pg_class as c
    inner join pg_catalog.pg_namespace as n on (c.relnamespace=n.oid)
12