PostgreSQL :: dump / restore / vaccum 関連

DB をまるごと dump して zip 圧縮する(おもにバックアップ用途)

% pg_dump -h DB名 -U ユーザ名 | gzip -c > dump.gz

テーブルのみバックアップする

% pg_dump -t テーブル名 DB名 > table.out

上記バックアップから復元する

% psql -e データベース名 < table.out

テーブルはdropされている必要がある

vaccum

% vacuumdb -f -z -v -d DB名 -h ホスト名 -U postgres

-f: do full vacuuming フルバキューム
-z: update optimizer hints オプティマイザの最適化
-v: write a lot of output 冗長表示モード

あるDBを丸ごと(スキーマ、データ、ストアド等すべて)別DBに複製したい時は、ダンプしてリストアすれば簡単です。

以下は、
　postgresql の スーパーユーザを「postgres」、
　ダンプするDBを「hoge_db」、hoge_db のユーザを「dbuser」
として説明していますので、適宜読みかえてください。

DBのdump

% pg_dump -U postgres hoge_db > hoge_db.dump

DBの削除(dropdb)

% dropdb -U postgres hoge_db
DROP DATABASE

以下のようなエラーでDBが削除できない場合は、ps で対象プロセスを探して kill する

dropdb: database removal failed: ERROR:  
database "hoge_db" is being accessed by other users

対象プロセスを探す

% ps aux | grep hoge_db
postgres 30403  0.0  0.9 2043972 34208 ? S 12:42 0:00 postgres: dbuser hoge_db 192.168.3.3(51218) idle
postgres 31348  0.0  1.0 2045080 36116 ? S 12:43 0:00 postgres: dbuser hoge_db 192.168.3.3(51428) idle
postgres 31568  0.0  1.0 2044516 35804 ? S 12:43 0:00 postgres: dbuser hoge_db 192.168.3.3(51484) idle
postgres 31953  0.0  1.0 2045092 35968 ? S 12:43 0:00 postgres: dbuser hoge_db 192.168.3.3(51585) idle
postgres 32402  0.0  1.0 2044920 35592 ? S 12:44 0:00 postgres: dbuser hoge_db 192.168.3.3(51707) idle

対象プロセスを kill する

% sudo kill 30403 31348 31568 31953 32402

すべて kill して、プロセスが存在しないことを確認したら

DBの削除(dropdb)

% dropdb -U postgres hoge_db
DROP DATABASE

DBのリストア

まず、DBの作成

% createdb -U ユーザ名 -E EUC_JP DB名
CREATE DATABASE

※ -E オプションはDBの文字エンコーディング。ダンプ元DBと同じにします。今回は「EUC_JP」を指定。

次に、dumpデータを読み込ませる

% psql -U postgres -e DB名 < hoge_db.dump