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

この記事のポイント

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

# 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

# 全データベースとロール情報をダンプ（プレーン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形式のみ対応）
sudo -u postgres pg_restore -d mydb_restore -j 4 /var/lib/pgsql/backup/mydb_20260824.dump

# 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

# サービス状態の確認
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