PostgreSQLのpg_dumpとpg_restore ― 形式の選び方と復元で出るエラー

※この記事にはアフィリエイト広告（プロモーション）が含まれます。

pg_dump はデータベース1つを、SQL 文の並んだテキストか、PostgreSQL 独自のアーカイブ形式で書き出します。

この記事のログは PostgreSQL 18.6（Ubuntu 26.04 の標準パッケージ）で採りました。コマンドは postgres ユーザーで実行しています。

まず見る（結論）

• 迷ったら -Fc。1表だけ戻す、中身の一覧を見る、といったことが pg_restore でできる
• ロール（ユーザー）は pg_dump に入らない。pg_dumpall --globals-only で別に取る
• pg_restore はエラーがあっても最後まで流して終了コード 1 を返す。errors ignored on restore の件数を見る

形式ごとのサイズ

1,000 行の表が1つだけある DB を、3つの形式で取りました。

-rw-r--r-- 1 postgres postgres  5508 src.dump
-rw-r--r-- 1 postgres postgres 13184 src.sql

src.dir:
-rw-r--r-- 1 postgres postgres 3727 3474.dat.gz
-rw-r--r-- 1 postgres postgres 1753 toc.dat

カスタム形式のファイルは先頭が PGDMP で始まります。どちらの形式か分からないファイルは head -c 5 で見分けられます。

形式とコマンドを取り違えたとき

$ pg_restore -d appdb src.sql
pg_restore: error: input file appears to be a text format dump. Please use psql.

$ psql -d appdb -f src.dump
The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.

どちらも何も書き込まずに止まります。

復元先のデータベースが無い

$ pg_restore -d dstdb src.dump
pg_restore: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "dstdb" does not exist

pg_restore は -d の DB に接続して中身を作ります。先に createdb dstdb で作っておきます。

-C を付けると DB ごと作ります。このとき -d には接続先として既存の DB（postgres など）を指定します。作られる DB の名前はダンプ元と同じ srcdb になりました。別の名前で戻したいときは -C を使わず、先に作った DB を -d に指定します。

pg_restore -C --no-owner -d postgres src.dump

所有者のロールが無い

別のサーバーに戻すと、表の所有者だったロールが無いことがあります。

$ pg_restore -d dstdb src.dump
pg_restore: error: could not execute query: ERROR:  role "rowner" does not exist
Command was: ALTER TABLE public.orders OWNER TO rowner;

pg_restore: warning: errors ignored on restore: 1

終了コードは 1 でしたが、表とデータ（1,000 行）は入っていました。所有者だけが変わらず、表の所有者は実行した postgres のままです。所有者を気にしない場合は、--no-owner を付ければこの ALTER ... OWNER を出さなくなります。所有者を合わせたい場合は、先にロールを作ります。

ロールの定義は pg_dump の出力に入りません（平文 SQL を grep -c "CREATE ROLE" すると 0）。元のサーバーで pg_dumpall --globals-only を実行すると、CREATE ROLE と ALTER ROLE ... PASSWORD が出ます。パスワードのハッシュが含まれるので、ファイルの扱いに注意します。

既に表がある DB に重ねて戻す

同じダンプをもう一度 pg_restore すると、所有者ロールの1件と合わせて4件のエラーになりました。

pg_restore: error: could not execute query: ERROR:  relation "orders" already exists
pg_restore: error: COPY failed for table "orders": ERROR:  duplicate key value violates unique constraint "orders_pkey"
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "orders" are not allowed
pg_restore: warning: errors ignored on restore: 4

既存の表を消してから作り直すには --clean --if-exists を付けます。こちらはエラーなし（終了コード 0）で、行数は 1,000 のままでした。

pg_restore --clean --if-exists --no-owner -d dstdb src.dump

中身の一覧と、1表だけ戻す

-l でアーカイブの中身が見られます。

$ pg_restore -l src.dump
;     dbname: srcdb
;     Format: CUSTOM
;     Dumped from database version: 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)
...
219; 1259 16495 TABLE public orders rowner
3481; 0 0 ACL public TABLE orders rowner
3474; 0 16495 TABLE DATA public orders rowner
3326; 2606 16502 CONSTRAINT public orders orders_pkey rowner

-t で表を絞れます。-f - で DB に入れずに SQL として標準出力に出せるので、中身を確かめてから流せます。

pg_restore -f - -t orders --data-only src.dump

平文 SQL は psql がエラーで止まらない

psql で平文 SQL を流すと、既定ではエラーが出ても次の文に進みます。-v ON_ERROR_STOP=1 を付けると最初のエラーで止まります。

$ psql -q -d plaindb -v ON_ERROR_STOP=1 -f src.sql
psql:src.sql:36: ERROR:  role "rowner" does not exist

ただし、止まるまでに実行した文は残ります。この例では orders 表が作られた状態で止まりました。-1（--single-transaction）も付けると全体が1つのトランザクションになり、失敗したときは表も残りませんでした。

psql -q -d plaindb -v ON_ERROR_STOP=1 -1 -f src.sql

MySQL の mysqldump については「mysqldumpの使い方」にまとめています。