Posts Tagged ‘PostgreSQL’

Laravel 5.5 でデータベースdumpをお手軽にとったりもどしたり

muraveです。LTSを使いたい人なのでLaravelのバージョンが5.1から5.5にジャンプアップしました。
その際、いままで使っていたデータベースバックアップのプラグインが使えなくなりました。

ということで

https://github.com/spatie/laravel-backup

を導入。良い感じです。もしかして?と、この方のリポジトリを探してみたらありました。

https://github.com/spatie/laravel-db-snapshots

This package provides Artisan commands to quickly dump and load databases in a Laravel application.

そうそう、この記事の対象データベースはPostgreSQLです。

PostgreSQLの場合にレストア時にエラーでデータベースを飛ばしたので、その対処方法のメモだったりします。MySQL、SQLiteにも対応らしいですが試してません。導入や使い方はドキュメント見てくださいね。

(more…)

Facebooktwitterlinkedintumblrmail

Docker公式イメージで開発用RDBをゲットだぜ(PostgreSQL編)

まえがき

Laravelでの開発時にデプロイ先はMySQLなのに手を抜いて手元の開発機ではSQLiteを使っていたら痛い目にあったりしました、muraveです。

開発環境にあまり影響を与えずにサクッと開発用のRDB(Relational Database)を建てられると素敵ですね。Docker公式イメージを活用すると出来そうです。

RDBというデッカイ単語を使っていますが、自分がよく使うPostgreSQL、MySQL、MariaDBなどについて調べようと思います。MySQLとMariaDB自体はほぼ同じ扱い方ができるRDBですが、公式イメージでの扱いはどうなんでしょうね。

記事にまとめながら試していこうと思います。Docker for Macを使用しており、今回はPostgreSQLです。

(more…)

Facebooktwitterlinkedintumblrmail

お気楽スロークエリ(JOIN & GROUP編)

運用中の業務系システムがあるんですが、お客様から「遅い」とご指摘を頂いた機能を調べたところ台帳からデータを取得し、集計しているクエリが非常に遅い事が判明しました。

調査している環境のデータベースは PostgreSQL 9.3 です。こんな時は EXPLAIN ANALYZE。

(more…)

Facebooktwitterlinkedintumblrmail

OpenCOBOLとファイル操作(弊社拡張)

OpenCOBOLからのファイル操作ですが内部での定義によって幾つか種類があります。

  1. 固定長レコードのシーケンシャルファイル
  2. 固定長レコードのISAM形式ファイル
  3. 可変長レコードのシーケンシャルファイル

これらはCOBOLソース内でのSELECT句での定義でファイル名を直接または環境変数を経由して間接的に指定することが可能です。OpenCOBOLの素の状態ですと

          SELECT ADBF0320 ASSIGN TO "FILE0001"
                                ORGANIZATION  SEQUENTIAL
                                ACCESS  MODE  SEQUENTIAL.

と記述されている場合は、FILE0001または環境変数 DD_FILE0001または dd_FILE0001に設定されているファイル名のファイルのOPENが可能です。弊社ではこの環境変数渡しの機能を活用してperlからOpenCOBOL側へJCL中で使用しているファイル名を渡しています。
さて、JCL中ではSYSINと呼ばれる形式でファイルを作らずにその場で渡したいデータを記述することがあります。

\INPUT ACCEPT1,TYPE=DATASSF,LIST=YES
4241122 登録データ1
4241122 登録データ1追加分
\ENDINPUT;

ADAM2200:
\STEP PROG2000 FILE=USL.CAT1  DUMP=DATA SUBLM=NORMAL;
\ASSIGN FILE0010 USR.F001    SHARE=ALL HOLDMODE=NO;
\ASSIGN FILE0110 USR.F011-T  FILESTAT=TEMP   PUBLIC NORMAL=PASS;
\ALLOCATE FILE0110  USR.F011-T SIZE=05;
\DEFINE FILE0110  RECSIZE=57 BLOCKSZ=10260 INCRSZ=01
                     RELSP RECFORM=FB;
\ASSIGN SIN      ACCEPT1        FILESTAT=SYSIN;
\ENDSTEP;

上記ではJCL中で定義されたASSIGN1というSYSINの内容をSINというファイル識別名に割り当てています。これをperlに置き換える(この部分自動的に処理しています)と

INPUT "ACCEPT1,TYPE=DATASSF,LIST=YES",<<_EOT;
4241122 登録データ1
4241123 登録データ1追加分 
_EOT
ENDINPUT;

ADAM2200:
STEP "PROG2000 FILE=USL.CAT1  DUMP=DATA SUBLM=NORMAL";
ASSIGN "FILE0010 USR.F001    SHARE=ALL HOLDMODE=NO";
ASSIGN "FILE0110 USR.F011-T  FILESTAT=TEMP   PUBLIC NORMAL=PASS";
ALLOCATE "FILE0110  USR.F011-T SIZE=05";
DEFINE "FILE0110  RECSIZE=57 BLOCKSZ=10260 INCRSZ=01",
                     "RELSP RECFORM=FB";
ASSIGN "SIN      ACCEPT1        FILESTAT=SYSIN";

上記のように変換しています。さて、SYSINの内容ですがまず1レコードが何byteであるという情報がありません。そして1行毎に行の長さが異なっています。今回移植の対象となった対象機のCOBOLではこのような場合には「改行区切りで1レコード」とするようになっていました。つまり可変長レコードです。ところがOpenCOBOLで可変長レコードをファイルとして扱うためには:
レコード先頭1バイトまたは2バイトにレコード長+1レコード分のデータ
レコード先頭1バイトまたは2バイトにレコード長+1レコード分のデータ
・・・
という形式でデータを作成する必要があります(つまり1byte目がレコード長として正しくないと、メモリ上に過大な長さのデータが読み込まれて、あっという間にSegfault します)。OpenCOBOLの外側からファイルの形式について何らかの方法で指示を出す必要が在りましたので、弊社ではファイル名の先頭に「sysin://」という識別子を(URI的に)付けてファイル名を渡すようにしています。これをOpenCOBOL内のファイルハンドラに渡る前に処理し、改行区切りの可変長レコードとして処理しています。同じく、印刷用の中間データなど1行の長さが可変長となる場合について「sysout://」という識別子を付けて指定することができるようにしています。他、標準の固定長レコードのドライバと動作をちょっと変えたドライバを使いたい場合を考え「misam://」や「mseq://」さらにLinux他では/dev/nullに該当するものとして「nullfs://」という識別子を指定可能としています。
標準の固定長レコードのドライバと動作をちょっと変えたいというのは例えばレコード挿入、削除時の細かい振る舞い、二次キー指定時の動作、二次キーを持っているISAMファイルを主キーしか定義していないCOBOLソースから書き込みモードで開いた場合の動作(OpeCOBOLの標準の動作では、書き込みモードでISAMファイルを開くと、一旦削除されますので、最悪二次キーについての定義が欠落します)等々です。
また、「perlfs://CLASSNAME/param」という形式でファイル名を渡す事によりファイルハンドラとしてperlにて記述したものを呼び出すようにもしています。DBとCOBOL内の固定長レコードの編集用コードについてperlで記述できるため、大変柔軟にDBとの連携を図れるようになりました(つまり、DBD::PgやDBD:MySQL、Oracleなどとの連携も可能です。MySQLについては既に運用されていますし、KeyValue系のDBへの接続もそれほどの変更なしに実装できます)。
上記に加え、固定長レコードやキー定義などの情報を別ディレクトリ内の管理ファイルに登録しておくことで、ファイルオープン時に正しい形式のファイルを使用しているかどうかCOBOLプログラム内の定義と照らし合わせて動的にチェックできるようになり、また現在どのようなファイルがオープン状態であるか?を全てモニタできるようにしています。
これらの改造はOpenCOBOLがオープンソースとして配布されていたことで可能になりました。成果は随時コミュニティ等にフィードバックしていきたいと考えております。

Facebooktwitterlinkedintumblrmail

正規化しないと駄目かな

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

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

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

結果として一応、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かは分かりませんが・・・。
それと、どちらも許容範囲では有りますが、メモリ上に一度乗るとインデックス使わない検索の方が早い事も有ります。

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

Facebooktwitterlinkedintumblrmail

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

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

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

$ 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 というファイルのようです。

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

再起動して確認。

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

おけおけ。

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

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

ですかね。

Facebooktwitterlinkedintumblrmail

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です。

IPv4 アドレス . . . . . . . . . . : 192.168.249.131

サブネット マスク . . . . . . . . : 255.255.255.0

デフォルト ゲートウェイ . . . . . : 192.168.249.2

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

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

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

$ 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。

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

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

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

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

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

# 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(認証なし)にすることにします。

# 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 を起動して接続してみます。

よしよし。

Facebooktwitterlinkedintumblrmail

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

http://www.postgresql.jp/

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

Facebooktwitterlinkedintumblrmail

複数テーブルをまたいで重複しない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で初期値を忘れずに。

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

Facebooktwitterlinkedintumblrmail

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';

Facebooktwitterlinkedintumblrmail