0Pricing
SQL Academy · レッスン

論理バックアップと物理バックアップ

pg_dumpとベースバックアップを学びます。

「論理バックアップと物理バックアップ」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

データベースバックアップとは

バックアップとは、データの損失、破損、災害の発生後にシステムを復元するために使用できる、データベースデータのコピーです。信頼できるバックアップがなければ、1回のハードウェア障害や誤ったDELETEによって、数か月、数年分のデータが永久に失われる可能性があります。

PostgreSQLには、バックアップ戦略として大きく論理バックアップと物理バックアップの2種類があります。それぞれ特徴、用途、トレードオフが異なるため、すべてのDBAが理解しておく必要があります。

論理バックアップの概要

論理バックアップは、データベースを人間が読めるSQL文 — CREATE TABLE、INSERT、COPYなどのコマンド — としてエクスポートします。PostgreSQLでこの処理に使われる最も一般的なツールはpg_dumpです。

出力がプレーンなSQLであるため、論理バックアップは移植性に優れています。別のPostgreSQLバージョンや別のオペレーティングシステムに復元でき、個々のテーブルやスキーマだけを選択して復元することもできます。一方で、大規模なデータベースではダンプと復元に時間がかかることがあります。

pg_dumpを使った論理バックアップ

pg_dumpユーティリティはSQLの内部ではなく、コマンドラインから実行します。実行中のPostgreSQLサーバーに接続し、指定したデータベースをエクスポートします。プレーンなSQL、カスタム圧縮形式、またはディレクトリ形式で出力できます。

以下のSQLは、論理バックアップに含まれる内容 — 再現可能な文としてのテーブルの構造とデータ — をシミュレートしています。

-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)

CREATE TABLE orders (
    id        SERIAL PRIMARY KEY,
    customer  TEXT        NOT NULL,
    amount    NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (customer, amount, created_at) VALUES
    ('Alice',  149.99, '2024-01-15 09:30:00+00'),
    ('Bob',     89.50, '2024-01-16 14:00:00+00'),
    ('Carol',  210.00, '2024-01-17 11:15:00+00');

pg_dumpの出力形式

pg_dumpは4種類の出力形式をサポートしており、それぞれ異なる復元ワークフローに適しています。

  • plain — どのテキストエディターでも読めるプレーンなSQLスクリプトです。
  • custom — 圧縮されたバイナリ形式です。最も柔軟性が高く、並列復元をサポートします。
  • directory — テーブルごとに1つのファイルを作成し、並列ダンプと並列復元をサポートします。
  • tar — directory形式のtarアーカイブです。

大規模なデータベースにはcustom形式が推奨されます。pg_restoreで-j Nワーカーを使用してオブジェクトを並列に復元できるためです。

-- Checking which databases exist before choosing what to back up
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM   pg_database
WHERE  datname NOT IN ('template0', 'template1')
ORDER  BY pg_database_size(datname) DESC;

論理バックアップの復元

プレーンSQL形式の論理バックアップはpsqlで復元します。カスタム形式のバックアップにはpg_restoreが必要です。どちらのツールもSQL文を再実行して、テーブル、インデックス、制約、データを再作成します。

論理バックアップにはSQLが含まれているため、復元前に編集できます。たとえば、1つのテーブルだけを復元したり、スキーマ名を変更したりできます。この柔軟性は、論理方式の大きな利点の1つです。

-- After restoring a backup, verify row counts match expectations
SELECT
    schemaname,
    relname           AS table_name,
    n_live_tup        AS estimated_rows
FROM  pg_stat_user_tables
ORDER BY n_live_tup DESC;

物理バックアップの概要

物理バックアップ（ベースバックアップとも呼ばれます）は、PostgreSQLがディスク上で使用する生のデータファイル — ページ、WAL（先行書き込みログ）セグメント、設定ファイル — をコピーします。その結果、ある時点におけるクラスター全体のバイナリスナップショットが作成されます。

大規模なデータベースでは、物理バックアップの復元は通常、はるかに高速です。SQLを再実行する必要がないため、PostgreSQLはファイルを所定の場所に戻して読み込み、WALを再生して整合性のある状態に到達させるだけで済みます。

pg_basebackupを使った物理バックアップの取得

pg_basebackupは、物理バックアップ用のPostgreSQL標準ツールです。レプリケーション接続を介して、実行中のプライマリサーバーからデータディレクトリをストリーミングします。レプリケーション権限を持つユーザーと、wal_levelをreplica以上に設定する必要があります。

ベースバックアップを開始する前に、データベース内でレプリケーション設定を照会し、サーバーが正しく構成されていることを確認できます。

-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM   pg_settings
WHERE  name IN (
    'wal_level',
    'max_wal_senders',
    'archive_mode',
    'archive_command'
)
ORDER  BY name;

WALアーカイブとポイントインタイムリカバリ

ベースバックアップは、ある時点の状態を取得したものです。そのバックアップ以降の任意の時点まで復旧するには、アーカイブされたWALセグメントをPostgreSQLで再生します。これをポイントインタイムリカバリ（PITR）と呼びます。

archive_mode = onに設定し、archive_commandを構成すると、PostgreSQLは完了したWALセグメントをアーカイブ先にコピーします。復旧中は、restore_commandがそれらのセグメントを取得し、目的の時刻までサーバーが再生できるようにします。

-- Inspect current WAL position and archive status
SELECT
    pg_current_wal_lsn()                        AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn())        AS current_wal_file,
    archived_count,
    failed_count,
    last_archived_wal,
    last_archived_time
FROM  pg_stat_archiver;

論理バックアップと物理バックアップの比較

論理バックアップと物理バックアップのどちらを選ぶかは、要件によって決まります。

  • 論理（pg_dump）：バージョン間で移植でき、部分的な復元に対応し、人間が読み取れます。ただし、大規模なデータベースでは遅く、サブトランザクション単位の粒度には対応しません。
  • 物理（pg_basebackup + WAL）：大規模なクラスターを高速に復元でき、PITRに対応します。ただし、バージョン固有であり、同じメジャーバージョンに復元する必要があります。また、クラスター全体を復元するため、単一のテーブルだけを復元することはできません。

本番環境では通常、両方を使用します。毎晩の物理ベースバックアップと継続的なWALアーカイブに加えて、移植性と対象を絞った復元のために、定期的な論理ダンプも実行します。

バックアップの整合性の確認

一度もテストしていないバックアップはバックアップではなく、単なる希望にすぎません。必ずバックアップをテスト環境に復元し、データを検証して有効性を確認してください。

論理バックアップの場合、簡単な整合性チェックとして、行数を数えてチェックサムを比較します。物理バックアップの場合、PostgreSQL 14以降ではpg_verifybackupが導入されており、pg_basebackupによって書き込まれたマニフェストファイルを検査できます。

-- After a test restore, compare row counts across critical tables
SELECT
    relname                              AS table_name,
    n_live_tup                           AS live_rows,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM  pg_stat_user_tables
WHERE  schemaname = 'public'
ORDER  BY n_live_tup DESC
LIMIT  20;

バックアップの監視とスケジュール設定

バックアップの自動化と監視は、バックアップを取得することと同じくらい重要です。最後にバックアップを実行した時刻、所要時間、成功したかどうかを追跡してください。PostgreSQLは、この目的に役立つメタデータを提供します。

物理バックアップの場合、pg_stat_archiverに最後に成功したアーカイブと失敗の情報が表示されます。論理バックアップの場合は、pg_dumpを、開始時刻、終了時刻、ファイルサイズ、終了コードを監視テーブルまたはアラートシステムに記録するスクリプトでラップします。

-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
    id          SERIAL PRIMARY KEY,
    backup_type TEXT        NOT NULL CHECK (backup_type IN ('logical', 'physical')),
    started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at TIMESTAMPTZ,
    size_bytes  BIGINT,
    status      TEXT        NOT NULL DEFAULT 'running',
    notes       TEXT
);

-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;

論理バックアップと物理バックアップ：理解度チェック

PostgreSQLにおける論理バックアップと物理バックアップの戦略について、理解度を確認しましょう。

レッスンのまとめ：論理バックアップと物理バックアップ

このレッスンでは、PostgreSQLの基本的なバックアップ戦略を2つ学びました。

  • 論理バックアップはpg_dumpを使用して、データベースをSQL文としてエクスポートします。移植性と可読性に優れ、部分的な復元にも対応しますが、非常に大規模なデータベースでは時間がかかることがあります。
  • 物理バックアップはpg_basebackupを使用して、生のデータファイルをコピーします。WALアーカイブと組み合わせることで高速な復元とポイントインタイムリカバリが可能になりますが、バージョン固有であり、常にクラスター全体を復元します。
  • 本番システムでは通常、両方の戦略を組み合わせます。高速で、きめ細かな復旧のためにWALアーカイブを伴う物理ベースバックアップを使用し、移植性のために定期的な論理ダンプも実行します。
  • 必ず復元をテストしてください。テストされていないバックアップは、実際の災害時には信頼できません。

これら2つの方式を理解することは、あらゆるPostgreSQL環境で堅牢な災害復旧計画を設計するために不可欠です。

よくある質問

「論理バックアップと物理バックアップ」レッスンは無料ですか？

はい。「論理バックアップと物理バックアップ」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「論理バックアップと物理バックアップ」で何を学びますか？

pg_dumpとベースバックアップを学びます。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。

「論理バックアップと物理バックアップ」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. 論理バックアップと物理バックアップ
  2. ポイントインタイムリカバリ
  3. 復元をテストする
  4. ディザスタリカバリ計画
← SQL Academyに戻る