テーブルのdump

テーブルを指定してデータをダンプするコマンド

pg_dump -a -d -f 出力ファイル名 -F p -t テーブル名 DB名

PostgreSQL バックアップとリストア

覚書。
参考になりそうなページからコピペ。

ラージオブジェクトなしでデータベース【mydatabase】をファイル【myfile】にバックアップしたい
pg_dump -d mydatabase > myfile


ラージオブジェクトなしでバックアップされたファイル【myfile】からデータベース【mydatabase】にリストアしたい
psql -f myfile -d mydatabase


ラージオブジェクトありでデータベース【mydatabase】をファイル【myfile】にバックアップしたい
pg_dump -b -Fc -d mydatabase > myfile


ラージオブジェクトなしでバックアップされたファイル【myfile】からデータベース【mydatabase】にリストアしたい
pg_restore -d mydatabase myfile


ラージオブジェクトありでデータベース【mydatabase】をtar形式でファイル【myfile】にバックアップしたい
pg_dump -b -Ft -d mydatabase > myfile


ラージオブジェクトありでバックアップされたファイル【myfile】(tar形式)からリストアしたい
pg_restore -d -d mydatabase myfile


ラージオブジェクトありでデータベース【mydatabase】をバックアップし、gzip 圧縮してファイル【myfile.gz】に保存したい
pg_dump -b -Fc -d mydatabase | gzip > myfile.gz


ラージオブジェクトありでバックアップ＆gzip 圧縮されたファイルファイル【myfile.gz】からデータベース【mydatabase】にリストアしたい
gzip -dc myfile.gz | pg_restore -d mydatabase


別ホスト【anotherhost】のデータベース【mydatabase】をラージオブジェクトなしでファイル【myfile】にバックアップしたい
pg_dump -h anotherhost -U postgres -d mydatabase > myfile


別ホストのデータベース【mydatabase】をラージオブジェクトありでファイル【myfile.gz】にバックアップ&gzip圧縮したい
pg_dump -b -Fc -h hostname -U postgres -d mydatabase | gzip > myfile.gz


データベース【mydatabase】のテーブルスキーマのみ（入っているデータは含まない）をSQLとしてファイル【myfile】に保存したい
pg_dump -s -d mydatabase > myfile


データベース【mydatabase】のデータのみ（テーブルスキーマは含まない）のみをSQLとしてファイル【myfile】に保存したい
pg_dump -a -d mydatabase > myfile


全てのデータベースをラージオブジェクトなしでファイル【myfile】にバックアップしたい（ラージオブジェクトありはpg_dumpallではバックアップ不可）
pg_dumpall > myfile

PostgreSQLの日本語全文検索

8.3の場合、TSearchがデフォルトで入っているが、英語にしか対応していないので
日本語での全文検索を行うためには、ライブラリを入れてあげる必要がある。
今回は、textsearch_jaというのを使うことに。
textsearch_jaを使うにはmecabが必要なので、まずはこれをインストール。

mecab
http://mecab.sourceforge.net/

手順
１、mecabの本体と辞書をダウンロードする
　　　mecab-0.97.tar.gz
　　　mecab-ipadic-2.7.0-20070801.tar.gz
２、mecabインストール（rootユーザで）
　　　tar zxf mecab-0.97.tar.gz
　　　cd mecab-0.97
　　　./configure
　　　make
　　　make install
３、辞書のインストール（rootユーザで）
　　DBの文字コードがUTF-8なので、UTF-8で作成する。
　　　tar zxf mecab-ipadic-2.7.0-20070801.tar.gz
　　　cd mecab-ipadic-2.7.0-20070801
　　　./configure --with-charset=utf8
　　　make
　　　make install
４、LD_LIBRARY_PATHの設定
　　　export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
５、postgreSQLを再起動する


textsearch_ja
http://textsearch-ja.projects.postgresql.org/index-ja.html

手順
１、PostgreSQLのソースディレクトリにtextsearch_jaを展開する。
　　（/usr/local/src/postgresql-8.3.1/contrib）
２、以下を実行
　　make
　　sudo make install
　　pgsql -f textsearch_ja.sql DB名

注意点
・PostgreSQLをバイナリからインストールした場合は、先にビルドしておく必要がある
　（configureとmakeまで）
・LD_LIBRARY_PATHの設定忘れに注意。設定後はpostgresを再起動しないとパスが読み込まれない


さて、全文検索の環境は整ったのでインデックスを作成。今回はGINを使う。
以下のSQLを実行。

CREATE INDEX インデックス名 on 対象テーブル名 USING gin(to_tsvector('japanese', カラム名))

GINインデックスの検索はGiSTの3倍くらい高速なんだそうだ。
ただし、インデックスの構築や更新に時間がかかるし、サイズも大きい。
システムの構成とかハードウェアの余力なんかを考えて決めるべきなんだろう・・・
まあ、検索の速さ重視で。

さて、いよいよ検索実行。
普通のSELECT文のWHERE句をちょっと変えるだけ。

SELECT * from テーブル名 WHERE to_tsvector('japanese', カラム名) @@ to_tsquery('japanese', '検索条件');

検索条件には、「AND」「OR」「NOT」の3種類の記号が使える。
それぞれ「&」「|」「!」。
&&とか||とか書かないことに注意。
こんな感じ？

SELECT * from テーブル名 WHERE to_tsvector('japanese', カラム名) @@ to_tsquery('japanese', '渋谷 & イタリアン');

以上。

Postgres限定、大量のデータを手っ取り早くINSERTするには。

PSQLのCOPYコマンドを使う。

例）
psql -d DB名 -c copy テーブル名 from 'CSVファイルパス' with NULL as '' csv

オプションの詳細はPostgreSQLのマニュアル参照。
200万行くらいなら1時間かからないで終わる。
VACUUM FULLをしておくと更に早い、かも。
とりあえずINSERT遅すぎ。

postgresのデータのエクスポート

SQLを作るのが面倒くさいときに便利。

pg_dump -d -U postgres DB名 ＞ ファイル名.sql