Backup & Restore Database in Postgresql

pg_dumpを利用したバックアップとリストアの手法を紹介する。

Backup data with pg_dump

localhostのeccube_db_testデータベースのc_actionテーブルのバックアップを取る例を次に示す。

C:\Program Files\PostgreSQL\8.3\bin>pg_dump -i -h localhost -p 5432 -U
eccube_db_user -F c -b -v -f "D:\backup\c_action.backup" -t c_action eccube_db_test

ここで、オプションの意味を次に示す。

option	詳細	備考
-p	–port=PORT	database server port number
-i	–ignore-version	proceed even when server version mismatches
-h	–host=HOSTNAME	database server host or socket directory
-U	–username=NAME	connect as specified database user
-W	–password	force password prompt (should happen automatically)
-d	–dbname=NAME	connect to database name
-v	–verbose	verbose mode
-F	–format=[c/t/p]	output file format (custom, tar, plain text)
-c	–clean	clean (drop) schema prior to create
-b	–blobs	include large objects in dump
-v	–verbose	verbose mode
-f	–file=FILENAME	output file name

Restore data with pg_restore

localhostのeccube_dbへリストアする例を次に示す。

C:\Program Files\PostgreSQL\8.3\bin>pg_restore -i -h localhost -p 5432 -U
eccube_db_user -d eccube_db -v "D:\backup\c_action.backup"

ここで、オプションの意味を次に示す。

option	詳細	備考
-p	–port=PORT	database server port number
-i	–ignore-version	proceed even when server version mismatches
-h	–host=HOSTNAME	database server host or socket directory
-U	–username=NAME	connect as specified database user
-W	–password	force password prompt (should happen automatically)
-d	–dbname=NAME	connect to database name
-v	–verbose	verbose mode