PostgreSQLのダンプとリストア

2019.10.23 (水)

PostgreSQLのダンプとリストアは比較的mysqlライクなので、そんなに戸惑うことはないのですが、多少違うところ含めての備忘録です。

pg_dumpコマンド

pg_dumpは以下の二種類の方法でdumpができます。

  • スクリプト形式(デフォルト) リストアに必要なSQLをそのまま吐き出してくれるダンプ方法です。psqlコマンドでそのままリストアできます。いわゆる普通のダンプ方法なのでデバックはしやすいです。
  • アーカイブ形式 バイナリの形で出力されます。リストアはpsqlコマンドでなくpg_restoreコマンドで行います。アーカイブ形式のは、指定したtableのみを選択してリストアできるというのと、データベース(テーブル)自体が巨大なデータの場合は圧縮して出力できるという利点あり。ダンプの形式はcustom形式(圧縮なし)、tar形式(tarで圧縮)があります。

ダンプ pg_dump(スクリプト形式)

postgresにスイッチして使うのが礼儀みたいな感じなんですかね。

$ su - postgres
$ pg_dump DATABASE_NAME > DUMP_FILE_NAME.sql

リストア psql(スクリプト形式)

$ psql DATABASE_NAME < DUMP_FILE_NAME.sql

または

$ psql -U USER_NAME DATABASE_NAME < DUMP_FILE_NAME.sql

リストアはコピー形式で行われるので、リストアを繰り返すとレコードがどんどん追加されるという仕様。(mysqlは上書きしてまるっと入れ替えてくれる)
なので、なんらかの形でリストアが失敗した場合は、テーブルを削除しないと駄目です。

確認

テーブルレコード数で確認

select relname, n_live_tup from pg_stat_user_tables where schemaname='public' order by relname;

クライアント側から確認

$ psql -U USER_NAME -h HOST_IP DB_NAME

おまけ

CSVのダウンロードしたいときなどはこちら

psql -U USER_NAME -h DATABASE_NAME -c "select * from TABLE_NAME" -A -F, > TABLE_NAME.csv