はじめに
この実験 (Lab) では、PostgreSQL データベースのバックアップと復元のための必須テクニックを学びます。データの損失を防ぐために、適切なバックアップ管理はデータベース管理者にとって非常に重要なスキルです。
まず、サンプルデータベースを作成し、データを投入します。次に、pg_dump ユーティリティを使用してデータベースの完全バックアップを作成します。その後、psql コマンドラインツールを使用してバックアップファイルからデータベースを復元する方法を学びます。最後に、データベースから単一の特定のテーブルをバックアップおよび復元する練習を行います。
サンプルデータベースの作成とデータ投入
このステップでは、salesdb という名前の PostgreSQL データベースを作成し、サンプルデータを投入します。このデータベースは、後続のステップで行うバックアップおよび復元操作の基盤となります。
ステップ 1: データベースの作成
まず、ターミナルから createdb コマンドラインユーティリティを使用して salesdb データベースを作成します。これは CREATE DATABASE SQL コマンドの便利なラッパーです。
createdb salesdb
ステップ 2: データベースへの接続
次に、新しく作成した salesdb データベースに psql 対話型ターミナルを使用して接続します。
psql -d salesdb
これで、salesdb=# のような psql プロンプトが表示され、salesdb データベースに接続されていることが示されます。
ステップ 3: テーブルの作成とデータの挿入
psql シェル内で、以下の SQL コマンドを実行して customers と products の 2 つのテーブルを作成します。
customers テーブルの作成：
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
products テーブルの作成：
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10, 2)
);
次に、両方のテーブルにサンプルデータを挿入します。
customers へのデータ挿入：
INSERT INTO customers (name) VALUES ('Alice'), ('Bob');
products へのデータ挿入：
INSERT INTO products (name, price) VALUES ('Laptop', 1200.00), ('Mouse', 25.00);
ステップ 4: データの検証
データが正しく挿入されたことを確認するために、両方のテーブルに対して SELECT クエリを実行します。
SELECT * FROM customers;
出力は以下のようになるはずです：
id | name
----+-------
1 | Alice
2 | Bob
(2 rows)
次に、products テーブルを確認します：
SELECT * FROM products;
出力は以下のようになるはずです：
id | name | price
----+--------+---------
1 | Laptop | 1200.00
2 | Mouse | 25.00
(2 rows)
最後に、psql シェルを終了して通常のターミナルに戻ります。
\q
これで、次のステップのためにデータベースのセットアップが完了しました。
データベース全体のバックアップ
このステップでは、pg_dump ユーティリティを使用して salesdb データベースのフルバックアップを作成します。pg_dump は、データベースを再構築するために使用できる SQL コマンドを含むファイルを生成します。
pg_dump の理解
pg_dump は、単一のデータベースをバックアップするための標準的な PostgreSQL ユーティリティです。データベースがアクティブに使用されている場合でも、データベースの一貫性のあるスナップショットを作成します。デフォルトでは、プレーンテキストの SQL スクリプトファイルが生成されます。
ステップ 1: バックアップの実行
ターミナルから、以下のコマンドを実行して salesdb データベースをプロジェクトディレクトリ内の salesdb.sql という名前のファイルにダンプします。
pg_dump salesdb > ~/project/salesdb.sql
コマンドの内訳を見てみましょう：
pg_dump salesdb: これはsalesdbデータベースをバックアップしたいことを指定します。> ~/project/salesdb.sql: これはコマンドの標準出力を~/projectディレクトリにあるsalesdb.sqlという名前のファイルにリダイレクトします。
ステップ 2: バックアップファイルの検証
まず、ls コマンドを使用してファイルが作成されたかどうかを確認します。
ls -l ~/project
ファイルリストに salesdb.sql が表示されるはずです。
次に、バックアップファイルの内容を調べて、pg_dump が何を作成したかを理解します。head コマンドを使用して最初の数行を表示します。
head ~/project/salesdb.sql
出力には、pg_dump のバージョンに関するコメントやデータベース環境を設定するためのコマンドなど、SQL スクリプトの開始部分が表示されます。これにより、バックアップファイルが有効な SQL スクリプトであることが確認できます。
バックアップからのデータベース復元
このステップでは、プレーンテキスト SQL バックアップファイルからデータベースを復元する方法を学びます。新しいデータベースを作成し、その後 psql ユーティリティを使用してバックアップファイルから SQL コマンドを実行します。
データベース復元の理解
pg_dump によって作成されたプレーンテキスト SQL ファイルの場合、psql ユーティリティが復元の標準的なツールです。ファイルから SQL コマンドを読み取り、指定されたデータベースに対して実行することで、テーブルとデータを効果的に再作成します。
ステップ 1: 復元用の新しいデータベースの作成
元のデータベースを上書きしないように、バックアップを新しく空のデータベースに復元します。salesdb_restored という名前のデータベースを作成します。
createdb salesdb_restored
ステップ 2: バックアップの復元
次に、psql を使用して salesdb.sql バックアップを salesdb_restored データベースに復元します。
psql -d salesdb_restored -f ~/project/salesdb.sql
コマンドの内訳を見てみましょう：
psql: PostgreSQL の対話型ターミナルで、スクリプトファイルを実行することもできます。-d salesdb_restored: 復元の対象データベースを指定します。-f ~/project/salesdb.sql: 実行する SQL コマンドを含む入力ファイルを指定します。
CREATE TABLE や INSERT のような、実行されている SQL コマンドの出力が表示されます。
ステップ 3: 復元されたデータベースの検証
salesdb_restored データベースに接続して、テーブルとデータが正常に復元されたことを確認します。
psql -d salesdb_restored
psql シェル内で、まずテーブルを一覧表示します：
\dt
customers および products テーブルが表示されるはずです。
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | customers | table | labex
public | products | table | labex
(2 rows)
次に、customers テーブルをクエリして、データが存在することを確認します。
SELECT * FROM customers;
出力は元のデータと一致するはずです。
id | name
----+-------
1 | Alice
2 | Bob
(2 rows)
psql シェルを終了します。
\q
これで、バックアップからデータベースを正常に復元できました。
単一テーブルのバックアップと復元
データベース全体ではなく、特定のテーブルのみをバックアップまたは復元する必要がある場合があります。pg_dump ユーティリティは、-t オプションでこれをサポートします。
ステップ 1: 単一テーブルのバックアップ
元の salesdb データベースから customers テーブルのみをバックアップしましょう。
ターミナルで以下のコマンドを実行します：
pg_dump -d salesdb -t customers > ~/project/customers_table.sql
-d salesdb: ソースデータベースを指定します。-t customers: ダンプする対象テーブルを指定します。> ~/project/customers_table.sql: 出力を新しいバックアップファイルにリダイレクトします。
ステップ 2: データ損失のシミュレーション
復元プロセスを実証するために、まず salesdb_restored データベースから customers テーブルを削除して、誤って削除された状況をシミュレートします。
psql -d salesdb_restored -c "DROP TABLE customers;"
コマンドは DROP TABLE を出力し、削除を確認します。
ステップ 3: 単一テーブルの復元
次に、テーブル固有のバックアップファイルから customers テーブルを salesdb_restored データベースに復元します。
psql -d salesdb_restored -f ~/project/customers_table.sql
ステップ 4: テーブル復元の検証
最後に、salesdb_restored データベースに接続し、customers テーブルが復元されており、products テーブルは変更されていないことを確認します。
psql -d salesdb_restored
psql 内で、テーブルを一覧表示します：
\dt
customers および products の両方のテーブルが再度一覧表示されるはずです。
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | products | table | labex
public | customers | table | labex
(2 rows)
復元された customers テーブルをクエリして、データがそのまま残っていることを確認します。
SELECT * FROM customers;
出力には、元の顧客データが表示されるはずです。
id | name
----+-------
1 | Alice
2 | Bob
(2 rows)
psql シェルを終了します。
\q
これで、個々のテーブルのバックアップと復元が正常に完了しました。
まとめ
この実験では、PostgreSQL データベースのバックアップと復元の基本的な手順を学びました。これらのスキルは、データ保護と災害復旧に不可欠です。
以下の方法を学びました：
- データベースを作成し、テーブルとデータでそれを投入する方法。
pg_dumpを使用して、データベース全体をプレーンテキスト SQL ファイルにエクスポートする方法。psqlを使用して、SQL バックアップファイルから新しいデータベースにデータベースを復元する方法。pg_dumpの-tオプションを使用して、データベースから単一のテーブルをバックアップする方法。- 単一のテーブルをデータベースに復元する方法。
これらのコマンドを習得することで、PostgreSQL データを効果的に管理および保護する自信が得られます。
