‘PostgreSQL’ タグのついている投稿

正規化しないと駄目かな

PostgreSQL8.1を使ったあるシステムでのお話しです。

まずユーザデータのテーブルが有り、そのユーザはID（連番）で管理されています。
そしてユーザが登録するデータテーブルには、データごとにそのデータを共有するユーザのIDが複数登録されています。
これはユーザIDをCSV形式の文字列で格納をしているんですが、条件文でLIKEとORを連発するのでデータ量が多くなると検索が非常に重くなります。

正規化しないと駄目かなと考えたんですが、対象となるテーブルが複数有るので修正に必要な箇所が散在していて、そのうえデータ量が多くなるテーブルは１つか２つしかないので、構造を変えずにどうにかならないかと別の方法を探しました。

結果として一応、postgresql-contribに含まれるtsearch2が、分かち書き形式の全文検索が出来るので良い感じかなと言う事でやってみました。
日本語には対応していませんが、今回は不要なので構いません。
（GINインデックスは対応が8.2からだったのでパスしました）

postgresql-contribは既にインストールされているので、postgresユーザになって、
psql dbname -f /usr/share/pgsql/contrib/tsearch2.sql

次にpsql dbnameでログインして、GRANTを掛けます。
GRANT ALL ON pg_ts_cfg TO PUBLIC;
GRANT ALL ON pg_ts_cfgmap TO PUBLIC;
GRANT ALL ON pg_ts_dict TO PUBLIC;
GRANT ALL ON pg_ts_parser TO PUBLIC;

とりあえずテスト。
CREATE TABLE test_tb(testts tsvector);
CREATE INDEX test_tb_testts ON test_tb USING gist (testts);

何事もなく作成されたので、データを入れてみて、SELECT。
データ中に2と4の含まれるデータを検索します。
SELECT * FROM test_tb WHERE testts @@ to_tsquery('default', '2&4'); -- and検索
SELECT * FROM test_tb WHERE testts @@ to_tsquery('default', '2|4'); -- or検索

EXPLAINすると、INDEXを使用しているようです。
Bitmap Heap Scan on test_tb (cost=4.10..108.72 rows=27 width=32)
Filter: (testts @@ ”’2” & ”4”’::tsquery)
-> Bitmap Index Scan on test_tb_index (cost=0.00..4.10 rows=27 width=0)
Index Cond: (testts @@ ”’2” & ”4”’::tsquery)

これを応用して、対象となるhogeテーブルのcol列にtsvector型に対応させたインデックスを作成します。
一応カンマからスペースに変更して、格納するようにreplaceしています。
CREATE INDEX hoge_col ON hoge USING gist (to_tsvector('default', replace(col,',',' ')));
SELECT * FROM hoge WHERE to_tsvector('default', replace(col,',',' ')) @@ to_tsquery('default', '2|4');

これで一応インデックスは効いているようです。
今までのクエリはインデックスを使わないでそのまま使えるし、インデックススキャンが必要なところは都度対応する事が出来ます。
とはいえ、かなり強引かなと思える方法なので、今後見て行かないとこれでOKかは分かりませんが・・・。
それと、どちらも許容範囲では有りますが、メモリ上に一度乗るとインデックス使わない検索の方が早い事も有ります。

何か他に良い方法は無いものか。

Popularity: 4% [?]

タグ:
カテゴリー: PostgreSQL | コメントはまだありません »

複数テーブルをまたいで重複しないIDを自動的に振る（PostgreSQL）


CREATE TABLE test(id serial, hoge text, primary key(id));
CREATE TABLE test2(id int default nextval(pg_get_serial_sequence('test'::text,'id'::text)::regclass), hoge text);

INSERT INTO test(hoge) VALUES('hoge');
INSERT INTO test2(hoge) VALUES('hoge');
INSERT INTO test(hoge) VALUES('hoge');

PostgreSQL8.1.9で確認。

testのidには1,3が、test2のidには2が入ります。
立っているclassは親でも使えと言う感じです。
デフォルト値にはcurrvalも使えますが、setvalで初期値を忘れずに。

普通に使う機会が無さそうなものばかりで。

Popularity: 7% [?]

タグ:
カテゴリー: 未分類 | コメントはまだありません »

PostgreSQL9.0が正式リリースされたようですね

http://www.postgresql.jp/

やはり最大の特徴はレプリケーションやホットスタンバイ等が統合されている点でしょうか。
これでマスタースレーブへの導入が進むとか、新しい使い方も出て来そうな気もしますが、試すには時間が無いかな。

Popularity: 8% [?]

タグ:
カテゴリー: 未分類 | コメントはまだありません »

PostgreSQLメモ

多分使わないメモ。

・カラム名column_nameのあるテーブルを探す。
 select t.relname,c.attname,format_type(c.atttypid, c.atttypmod) from pg_attribute as c
 inner join pg_stat_user_tables as t on(c.attrelid = t.relid)
 where attname = ‘column_name’;

・テーブルとインデックスのサイズを確認する（ブロックサイズ*ブロック数）。
 SELECT tablename,pg_relation_size(tablename::text) from pg_tables where tableowner != ‘postgres’;

 SELECT indexname,pg_relation_size(indexname::text) as indexsize from pg_indexes where schemaname != ‘pg_catalog’;

・今ロックされているテーブルを調べる。
 SELECT relname from pg_stat_all_tables where relid in(select relation from pg_locks) and schemaname != ‘pg_catalog’;

Popularity: 6% [?]

タグ:
カテゴリー: 未分類 | コメントはまだありません »

Munin で PostgreSQL を監視する

PostgreSQL 大好きっ子なので PostgreSQL を監視するプラグインを追加するんだぜー。

PostgreSQL 監視用のプラグインはパッケージを導入した際にいくつか入ってます。

1
2
3
4
5
6

$ ls -l /usr/share/munin/plugins/postgres*
-rwxr-xr-x 1 root root 5377 2009-10-18 19:53 /usr/share/munin/plugins/postgres_block_read_
-rwxr-xr-x 1 root root 7777 2009-10-18 19:53 /usr/share/munin/plugins/postgres_commits_
-rwxr-xr-x 1 root root 2159 2009-10-18 19:53 /usr/share/munin/plugins/postgres_locks
-rwxr-xr-x 1 root root 6118 2009-10-18 19:53 /usr/share/munin/plugins/postgres_queries_
-rwxr-xr-x 1 root root 7243 2009-10-18 19:53 /usr/share/munin/plugins/postgres_space_

もちろん /usr/share/munin/plugins/ には、PostgreSQL 用だけじゃなくていろんなプラグインが入ってます。いっぱい、いっぱい。

PostgreSQL のコネクション数の監視をするプラグインは残念ながらここにはないので
http://munin-monitoring.org/wiki/plugin-pg__connections
から取ってきました。

こいつも /usr/share/munin/plugins/ に置いて、実行できるようにしておきましょう。

1

$ sudo chmod +x /usr/share/munin/plugins/pg__connections

プラグインの設定方法はプラグインのファイルを開いて読むと書いてありますので、そのようにしていきます。

Munin のプラグインは /etc/munin/plugins/ ディレクトリにあるかどうかで管理されています。まずは、取ってきた pg__connections のシンボリックリンクを作成。

1

$ sudo ln -s /usr/share/munin/plugins/pg__connections /etc/munin/plugins/pg_5432_connections

プラグインのファイルの Usage には pg__connections ってな名前をつけるように書いてありますが、ポート番号にしてみました。このプラグインについてはこの文字列が動作に影響を及ぼすことはなさそうなので何でもいいと思います。

用意されているプラグインからも監視したい対象のプラグインをセットアップ。

1
2

$ sudo ln -s /usr/share/munin/plugins/postgres_block_read_ /etc/munin/plugins/postgres_block_read_5432
$ sudo ln -s /usr/share/munin/plugins/postgres_space_ /etc/munin/plugins/postgres_space_5432

この2つにしました。

シンボリックリンクの最後にデータベース名を付けるように書いてありましたがこれらについてもポート番号の5432にしてみました。これらのプラグインでは付けた文字列がグラフのタイトルで使用されるのでちゃんと考えて付けましょう。

ちなみに postgres_block_read_5432 だとグラフのタイトルが「:: Postgres data reads from 5432」となります。postgres_space_5432 の方は「:: Postgres database 5432」となって、データベースの容量に関するグラフのタイトルとしてはイマイチな感じになっちゃいました。

PostgreSQLのプラグインについては設定ファイルに記述がないので設定を追記する必要があります。

1

$ sudo vi /etc/munin/plugin-conf.d/munin-node

私の実験環境での設定ですが、次のように追記しました。

1
2
3
4
5
6
7
8
9
10
11
12
13

[postgres_*]
env.PGHOST 127.0.0.1
env.PGPORT 5432
env.PGDATABASE postgres
env.PGUSER postgres
env.PGPASSWORD trust
 
[pg_5432_connections]
env.dbhost 127.0.0.1
env.dbport 5432
env.dbname postgres
env.dbuser postgres
env.dbpass trust

[postgres_*] は postgres_ が頭に付いているプラグインの設定、すなわち、用意されているプラグインの設定です。
[pg_5432_connections] はパラメータ名なども異なりますし別途書く必要があります。

実験環境の PostgreSQL の 接続設定は 127.0.0.1 からは trust にしています。よって、データベースの接続にパスワードは不要なのですが、何か書いていないとエラーなったので仮に trust としています。

PostgreSQL 側でやらなければならない設定として「postgresql.conf の stats_start_collector = true として統計情報収集器を有効にせよ」的なことも書いてありますが、PostgreSQL 8.3 以降、この設定は削除され、UDP ソケットの作成に成功したら必ず統計情報収集器が起動するようになったようです（http://www.sraoss.co.jp/technology/postgresql/8.3/ 参照）。

あとは Munin-Node を再起動すればOK、と言いたいところですが、実はこれでは動かない場合があります。 libdbd-pg-perl が入っていない場合です。プラグインが PostgreSQL にアクセスするのに使っているのです。
Ubuntu に Munin をインストールする時には入りませんでしたので、libdbd-pg-perl が入っていない場合には別途インストールする必要があります。

1

$ sudo aptitude install libdbd-pg-perl

こんどこそ、準備OK。Munin-Node を再起動します。

1
2
3
4
5

$ sudo /etc/init.d/munin-node restart
 * Stopping Munin-Node
   ...done.
 * Starting Munin-Node
   ...done.

再起動完了。

1

$ tail -n 20 /var/log/munin/munin-node.log

ってな感じで、 Munin-Node のログを見てエラーが出てないか確認すると良いでしょう。データがないせいで正常動作をしていてもエラーが出ていたりする場合もあります。内容で判断するしかないですね。

しばらく待つと、こんな感じに PostgreSQL のリソースのグラフが追加されたページが生成されます。

Popularity: 38% [?]

タグ:, ,
カテゴリー: Server | コメントはまだありません »

OS X に導入した PostgreSQL の自動起動を無効にする

PostgreSQL が自動起動するようになっていたので止めときます。開発・検証用ですので。

OS X では daemon の起動は launchctl コマンドで設定できるようです。拡張子が plist な設定ファイルでコントロールしているそうなので探します。

1
2
3

$ locate plist | grep postgres
/Library/LaunchDaemons/com.edb.launchd.postgresql-8.4.plist
/Library/PostgreSQL/8.4/uninstall-postgresql.app/Contents/Info.plist

com.edb.launchd.postgresql-8.4.plist というファイルのようです。

1

$ sudo launchctl unload -w /Library/LaunchDaemons/com.edb.launchd.postgresql-8.4.plist

再起動して確認。

1
2

$ ps ax | grep postgres
  419 s000  S+     0:00.00 grep postgres

おけおけ。

逆に自動起動させたいときは

1

$ sudo launchctl load -w /Library/LaunchDaemons/com.edb.launchd.postgresql-8.4.plist

ですかね。

Popularity: 11% [?]

タグ:,
カテゴリー: Server | コメントはまだありません »

NATなFusion上のWindowsからホストのPostgreSQLに接続

MacBookにPostgreSQLを『Mac OS X で PostgreSQL を使ってみよう』(http://lets.postgresql.jp/documents/tutorial/macosx/)を参考にしつつ導入したのですがpsqlで日本語が通らず困ってたりする今日この頃です。ヘルプミー。

とりあえず、VMware Fusiion3上のWindows7にもPostgreSQLを入れたのですが、ServerだけMac側を使おうかなと。サーバーUnix系OS、クライアントWindowsな実環境に近くなりますし。

ってなわけで、Fusion上のWin7がどうなっているやら調べます。ipconfigで見てみましょう。ネットワーク アダプタはNATです。

1
2
3
4
5

IPv4 アドレス . . . . . . . . . . : 192.168.249.131
 
サブネット マスク . . . . . . . . : 255.255.255.0
 
デフォルト ゲートウェイ . . . . . : 192.168.249.2

ということで、ホストのアドレスは192.168.249.1のようですね。

次、OS X に導入したPostgreSQL8.4がどうなってるのか探ります(ちょい前に導入したもので設定を忘れてまして)。探っていくと、/Library/PostgreSQL/ってなところにインストールされているようです。

設定ファイルを探します。デフォルトだとデータと同じ場所に設定ファイルがありますね。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

$ sudo ls -l /Library/PostgreSQL/8.4/data/
total 80
-rw-------   1 postgres  daemon      4  1 15 08:41 PG_VERSION
drwx------   6 postgres  daemon    204  1 16 21:07 base
drwx------  42 postgres  daemon   1428  3 18 17:33 global
drwx------   3 postgres  daemon    102  1 15 08:41 pg_clog
-rw-------   1 postgres  daemon   3405  1 15 08:41 pg_hba.conf
-rw-------   1 postgres  daemon   1631  1 15 08:41 pg_ident.conf
drwxr-xr-x  69 postgres  daemon   2346  3 23 00:00 pg_log
drwx------   4 postgres  daemon    136  1 15 08:41 pg_multixact
drwx------   3 postgres  daemon    102  3 23 11:24 pg_stat_tmp
drwx------   3 postgres  daemon    102  1 15 08:41 pg_subtrans
drwx------   2 postgres  daemon     68  1 15 08:41 pg_tblspc
drwx------   2 postgres  daemon     68  1 15 08:41 pg_twophase
drwx------   4 postgres  daemon    136  1 15 08:41 pg_xlog
-rw-r--r--   1 postgres  wheel   16779  1 15 08:42 postgresql.conf
-rw-------   1 postgres  daemon     70  3 18 17:33 postmaster.opts
-rw-------   1 postgres  daemon     52  3 18 17:33 postmaster.pid

ありました。設定見たり、いじったりしましょう。

まずは postgresql.conf。postgresさんの持ち物らしいので、なりきってGO。

1

$ sudo -u postgres vim /Library/PostgreSQL/8.4/data/postgresql.conf

で、眺めると、全てのアドレスでlistenはしてるようです。

1

listen_addresses = '*'          # what IP address(es) to listen on;

修正は必要ありませんでした。

次、pg_hba.confを同じくpostgresさんになりきってエディターで開きます。

1
2
3
4
5
6
7
8

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

当然、192.168.249.131からの接続は許可されてません。今回は192.168.249.*からtrust（認証なし）にすることにします。

1
2
3
4
5
6
7
8
9
10

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5
# Win7 on Fusion3 connections
host    all         all         192.168.249.0/24      trust

trustな行が追記分です。保存して、PostgreSQLをリスタート。

Fusion 上の Win7 で pgAdmin III を起動して接続してみます。

よしよし。

Popularity: 12% [?]

タグ:, ,
カテゴリー: Server | コメントはまだありません »