PostgreSQLをpg_dumpとpg_restoreでバックアップする手順｜論理バックアップの取得と復元の実務

この記事のポイント

・pg_dump -Fc（カスタム形式）が本番運用での推奨フォーマット
・pg_dump -Fd -j N でディレクトリ形式の並列バックアップも可能（大規模DB向け）
・pg_restore -j N で並列リストアするとリストア時間を大幅短縮できる
・全DBを一括バックアップするにはpg_dumpallを使う
・cronで自動化する際は.pgpassファイルでパスワードを安全に管理する

-- デッドタプルが多いテーブルを確認（上位10件）
SELECT
  schemaname,
  tablename,
  n_dead_tup,
  n_live_tup,
  to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

 schemaname |  tablename   | n_dead_tup | n_live_tup |   last_autovacuum
------------+--------------+------------+------------+---------------------
 public     | order_items  |      48203 |     195820 | 2026-08-23 03:12
 public     | sessions     |      21400 |      63500 | 2026-08-23 03:12
 public     | products     |        412 |      15800 | 2026-08-24 01:30

# postgresユーザーに切り替えて実行
sudo -u postgres pg_dump mydb > /var/lib/pgsql/backup/mydb_20260824.sql

# -f オプションで出力ファイルを指定する書き方でも同じ結果
sudo -u postgres pg_dump mydb -f /var/lib/pgsql/backup/mydb_20260824.sql

$ head -16 /var/lib/pgsql/backup/mydb_20260824.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 16.3
-- Dumped by pg_dump version 16.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

# カスタム形式でダンプ（拡張子は .dump が慣例）
sudo -u postgres pg_dump -Fc mydb -f /var/lib/pgsql/backup/mydb_20260824.dump

# ファイルサイズを確認（プレーンSQLより大幅に小さい）
ls -lh /var/lib/pgsql/backup/mydb_20260824.dump
-rw-r--r-- 1 postgres postgres 4.2M Aug 24 09:00 /var/lib/pgsql/backup/mydb_20260824.dump

# ordersテーブルだけをカスタム形式でダンプ
sudo -u postgres pg_dump -Fc -t orders mydb -f /var/lib/pgsql/backup/mydb_orders_20260824.dump

# ディレクトリ形式で4並列バックアップ（-f にはディレクトリ名を指定）
sudo -u postgres pg_dump -Fd -j 4 mydb -f /var/lib/pgsql/backup/mydb_20260824_dir

# バックアップ先ディレクトリの中身を確認
ls /var/lib/pgsql/backup/mydb_20260824_dir/
toc.dat  3716.dat.gz  3717.dat.gz  3718.dat.gz  3719.dat.gz

# 全データベースとロール情報をダンプ（プレーンSQL形式のみ対応）
sudo -u postgres pg_dumpall -f /var/lib/pgsql/backup/all_databases_20260824.sql

# ファイルサイズを確認
ls -lh /var/lib/pgsql/backup/all_databases_20260824.sql
-rw-r--r-- 1 postgres postgres 28M Aug 24 09:05 /var/lib/pgsql/backup/all_databases_20260824.sql

# ロール（ユーザー・グループ）定義のみを取り出す
sudo -u postgres pg_dumpall -g -f /var/lib/pgsql/backup/roles_20260824.sql

# リストア先DBを作成
sudo -u postgres createdb mydb_restore

# pg_restoreでリストア（-d でリストア先DBを指定）
sudo -u postgres pg_restore -d mydb_restore /var/lib/pgsql/backup/mydb_20260824.dump

# 完了後にデータ件数を確認
sudo -u postgres psql -d mydb_restore -c "SELECT schemaname, tablename, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 5;"
   schemaname |  tablename  | n_live_tup
--------------+-------------+------------
 public       | orders      |     125847
 public       | users       |      45231
 public       | products    |       3812
 public       | categories  |        148
 public       | sessions    |      87654
(5 rows)

# -j 4 で4並列リストア（CPUコア数に合わせて調整。-Fc/-Fd形式のみ対応）
sudo -u postgres pg_restore -d mydb_restore -j 4 /var/lib/pgsql/backup/mydb_20260824.dump

# ディレクトリ形式（-Fd）のリストアも同様
sudo -u postgres pg_restore -d mydb_restore -j 4 /var/lib/pgsql/backup/mydb_20260824_dir/

# ordersテーブルのみをリストア（カスタム形式の利点）
sudo -u postgres pg_restore -d mydb_restore -t orders /var/lib/pgsql/backup/mydb_20260824.dump

# プレーンSQL形式のリストア（リストア先DBを事前に作成しておく）
sudo -u postgres createdb mydb_restore
sudo -u postgres psql -d mydb_restore -f /var/lib/pgsql/backup/mydb_20260824.sql

# pg_dumpall の全DB一括ダンプをリストア（DB作成コマンドも含まれる）
sudo -u postgres psql -f /var/lib/pgsql/backup/all_databases_20260824.sql

# 旧サーバーで実行: カスタム形式でダンプしてSSH経由で新サーバーへ転送
sudo -u postgres pg_dump -Fc mydb | ssh new-server "sudo -u postgres pg_restore -d mydb_new -"

# または新サーバー側から旧サーバー（192.168.1.10）に接続してpg_dumpを実行
pg_dump -h 192.168.1.10 -U postgres -Fc mydb | pg_restore -d mydb_new -

# postgresユーザーのホームディレクトリに .pgpass を作成
# 形式: hostname:port:database:username:password
echo "localhost:5432:mydb:backup_user:バックアップ用パスワード" > /var/lib/pgsql/.pgpass
chmod 600 /var/lib/pgsql/.pgpass
chown postgres:postgres /var/lib/pgsql/.pgpass

# /usr/local/bin/pg_backup.sh として作成
#!/bin/bash
BACKUP_DIR="/var/lib/pgsql/backup"
DB_NAME="mydb"
DATE=$(date +%Y%m%d)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"

# バックアップ取得（カスタム形式）
pg_dump -U backup_user -Fc "${DB_NAME}" -f "${BACKUP_FILE}"

# 7日より古いバックアップを削除（世代管理）
find "${BACKUP_DIR}" -name "${DB_NAME}_*.dump" -mtime +7 -delete

# バックアップサイズをログに記録
ls -lh "${BACKUP_FILE}" >> "${BACKUP_DIR}/backup.log"

# スクリプトに実行権限を付与
chmod 755 /usr/local/bin/pg_backup.sh
chown postgres:postgres /usr/local/bin/pg_backup.sh

# postgresユーザーのcrontabに登録（毎日02:00に実行）
sudo -u postgres crontab -e
# 以下の1行を追加して保存
0 2 * * * /usr/local/bin/pg_backup.sh

# カスタム形式の内容一覧を表示（エラーなく表示できれば破損なし）
sudo -u postgres pg_restore -l /var/lib/pgsql/backup/mydb_20260824.dump | head -20
;
; Archive created at 2026-08-24 02:00:15 JST
;     dbname: mydb
;     TOC Entries: 38
;     Compression: 6
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 16.3
;     Dumped by pg_dump version: 16.3
;
; Selected TOC Entries:
;
225; 1259 16384 TABLE public orders postgres
226; 1259 16392 TABLE public users postgres
227; 1259 16401 TABLE public products postgres
228; 1259 16410 TABLE public categories postgres
229; 1259 16419 TABLE public sessions postgres

# pg_dumpの終了コードをチェックしてメール通知する例
pg_dump -U backup_user -Fc "${DB_NAME}" -f "${BACKUP_FILE}"
if [ $? -ne 0 ]; then
  echo "pg_dump failed on $(date)" | mail -s "BACKUP ERROR: ${DB_NAME}" admin@example.com
fi

# サービス状態の確認
systemctl status postgresql-16

# RHEL 9.4での正常な出力例
* postgresql-16.service - PostgreSQL 16 Database Server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled)
     Active: active (running) since Sun 2026-08-24 09:00:01 JST; 2h 5min ago

# 接続確認
sudo -u postgres psql -c "\conninfo"
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

# --clean で既存オブジェクトをDROPしてから再作成
sudo -u postgres pg_restore --clean -d mydb_restore /var/lib/pgsql/backup/mydb_20260824.dump

# または リストア先DBを削除→再作成してからリストア（より確実）
sudo -u postgres dropdb mydb_restore
sudo -u postgres createdb mydb_restore
sudo -u postgres pg_restore -d mydb_restore /var/lib/pgsql/backup/mydb_20260824.dump

# バックアップ専用ユーザーにSELECT権限を付与する例
sudo -u postgres psql -d mydb
mydb=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
GRANT
mydb=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup_user;
ALTER DEFAULT PRIVILEGES

# バックアップ前にディスク空き容量と想定ダンプサイズを確認
df -h /var/lib/pgsql/backup

# 実際のDBサイズを確認（ダンプ後のファイルサイズの目安になる）
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('mydb'));"
 pg_size_pretty
----------------
 8543 MB
(1 row)

＜PR＞手元に置いて学びを深める1冊

PostgreSQL実践入門 ──アーキテクチャ、運用監視、性能改善

まず無料メルマガで学ぶのが第一歩、加えてpg_dump・pg_restoreを含むバックアップ設計から性能改善まで体系的に身につけるなら本書が一冊目の選択肢になる。