続：PostgreSQLのデータを抜き取る

前回に引き続き、PostgreSQLの特定のDBについて、全テーブルのデータとスキーマを一気にぶっこ抜いてしんぜよう。全くもって誰得なのか分からんが・・・自分用のメモ代わりにはなる。

まあシェルスクリプトで扱う練習データとかで使えるよね？また他のシステムへ移行時のデータエクスポートにも使えそう？ユニケージのための布石・・・にも使えるのか？

環境は、前回同様Mac、OSX Yosemite。HomebrewでGNU sed(gsed)とPostgreSQLをインストール。

抜き出す方法

COPYコマンドを使って抜き出す。COPYコマンドで抜き出したテーブルのデータは下記の通り。

• カラム区切りがタブ区切り
• 改行は\r\nへ変換
• NULL値は\Nで表示

加工後のデータ

シェルスクリプトで扱いやすいように、下記のように変換をする。

• タブ区切りを空白に変換
• データの空白は@s@p@に変換
• 空文字は""に変換

取り出す先のファイル名は下記のように想定。

• TABLE名.copy: COPYコマンドで取り出したデータ
• TABLE名.txt: シェルスクリプトで扱いやすい形式に変換したデータ
• TABLE名.schema: テーブルのスキーマ情報

抜き出すためのシェル芸パーツ

psqlコマンドの接続先ホスト名と接続ユーザ名のオプションは適宜入れること。

TABLE名を指定して標準出力へ書き出す。

$ echo '\COPY TABLE名 TO stdout;' | psql DB名

TABLE名とファイル名を指定して書き出す(TABLE名.copy)。

$ echo '\COPY TABLE名 TO TABLE名.copy;' | psql DB名

COPYコマンドで書き出したファイルTABLE名.copyを、シェルスクリプトで扱いやすい形式に変換するフィルタ。1行目で空文字を""に、2行目で空白を@s@p@、3行目のgsedでタブ区切りを空白区切りへ変換。

$ < TABLE名.copy gsed 's/\t\t/\t""\t/g' |\
sed 's/ /@s@p@/g' |\
gsed 's/\t/ /g'

TABLE名を指定してスキーマを表示

$ echo '\d TABLE名' | psql --no-align --tuples-only DB名

スキーマをファイルに出力(TABLE名.schema)

$ ( echo '\o TABLE名.schema'; echo '\d TABLE名' ) |\
psql --no-align --tuples-only DB名

全テーブル名のリストを表示

$ echo 'SELECT relname AS table_name FROM pg_stat_user_tables;' |\
psql --no-align --tuples-only DB名

全テーブルデータ抜きだし

シェル芸パーツを組み合わせて、一気に抜きだし一気に変換。爽快。

各テーブルのスキーマとデータを書き出す(TABLE名.schema、TABLE名.copy)

$ echo 'SELECT relname AS table_name FROM pg_stat_user_tables;' |\
psql --no-align --tuples-only DB名 |\
awk '{print "\\o "$1".schema"; print "\\d "$1; print "\\o"; print "\\COPY "$1" TO "$1".copy"}' |\
psql --no-align --tuples-only DB名

書き出した各TABLE名.copyを、シェルスクリプトで扱いやすい形式にまとめて変換。横に長いままだが、改行すると変になりそうだったので。

$ for file in `ls *.copy`; do table=`basename -s .copy $file`; cat $table.copy | gsed 's/\t\t/\t""\t/g' | sed 's/ /@s@p@/g' | gsed 's/\t/ /g' > $table.txt; done

さあ、レッツ誰得！