PostgreSQL バックアップとリストア - データ保護の基本

はじめに (Introduction)

この実験 (lab) では、PostgreSQL データベースをバックアップおよび復元する方法を学びます。この実験 (lab) では、pg_dump を使用したデータベースのエクスポート、pg_restore を使用したデータベースの復元、特定のテーブルのバックアップなど、データを保護するための重要なテクニックについて説明します。

まず、mydatabase という名前のサンプルデータベースを作成します。次に、pg_dump コマンドを使用して、このデータベースを SQL ファイルにエクスポートします。次に、pg_restore を使用して、バックアップファイルからデータベースを復元する方法を学びます。最後に、データベース内の特定のテーブルのみをバックアップし、復元されたデータの整合性を検証する方法を学びます。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_setup("Create New Database") postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_all("Select All Data") postgresql/PostgreSQLGroup -.-> postgresql/row_drop("Delete One Row") subgraph Lab Skills postgresql/db_setup -.-> lab-550949{{"PostgreSQL のバックアップとリストア"}} postgresql/db_access -.-> lab-550949{{"PostgreSQL のバックアップとリストア"}} postgresql/table_init -.-> lab-550949{{"PostgreSQL のバックアップとリストア"}} postgresql/row_add -.-> lab-550949{{"PostgreSQL のバックアップとリストア"}} postgresql/data_all -.-> lab-550949{{"PostgreSQL のバックアップとリストア"}} postgresql/row_drop -.-> lab-550949{{"PostgreSQL のバックアップとリストア"}} end

データベースの作成とデータの投入 (Create and Populate a Database)

このステップでは、mydatabase という名前の PostgreSQL データベースを作成し、サンプルデータを投入します。このデータベースは、以降のステップでのバックアップと復元の演習で使用されます。

まず、次のコマンドを使用して mydatabase データベースを作成します。

sudo -u postgres psql -c "CREATE DATABASE mydatabase;"

このコマンドは、postgres ユーザーとして PostgreSQL サーバーに接続し、SQL コマンド CREATE DATABASE mydatabase を実行します。

次に、psql コマンドを使用して mydatabase データベースに接続します。

sudo -u postgres psql -d mydatabase

これで、mydatabase データベースに接続された psql シェルに入るはずです。mydatabase=# のようなプロンプトが表示されます。

次に、id と name のカラムを持つ users という名前のテーブルを作成します。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

この SQL コマンドは、users という名前のテーブルを、id (自動的にインクリメントされる整数) と name (文字列) の 2 つのカラムで作成します。

次に、サンプルデータを users テーブルに挿入します。

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

これらの SQL コマンドは、users テーブルに 'Alice' と 'Bob' という名前で 2 つの行を挿入します。

最後に、products という名前の別のテーブルを作成し、データを挿入します。

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

INSERT INTO products (name) VALUES ('Laptop');
INSERT INTO products (name) VALUES ('Tablet');

テーブルとデータが正常に作成されたことを確認するには、次の SQL クエリを実行します。

SELECT * FROM users;
SELECT * FROM products;

挿入したデータが表示されるはずです。

psql シェルを終了するには、次のように入力します。

\q

これで Linux ターミナルに戻ります。

データベースのエクスポート (Export the Database)

このステップでは、pg_dump コマンドを使用して、mydatabase データベースを SQL ファイルにエクスポートします。このファイルには、データベースを再作成するために必要な SQL コマンドが含まれます。

次のコマンドを実行して、データベースをエクスポートします。

pg_dump -U postgres -d mydatabase -f ~/project/mydatabase.sql

このコマンドは、pg_dump ユーティリティを使用して、postgres ユーザーとして mydatabase データベースに接続し、データベースを ~/project ディレクトリにある mydatabase.sql という名前のファイルにエクスポートします。

ファイルが正常に作成されたことを確認するには、~/project ディレクトリの内容を一覧表示します。

ls ~/project

出力に mydatabase.sql が表示されるはずです。

head コマンドを使用して、バックアップファイルの最初の数行を表示することもできます。

head ~/project/mydatabase.sql

これにより、pg_dump によって作成された SQL スクリプトの冒頭が表示されます。

データベースのリストア (Restore the Database)

このステップでは、mydatabase.sql バックアップファイルから mydatabase データベースを、newdatabase という名前の新しいデータベースにリストアします。

まず、newdatabase データベースを作成します。

sudo -u postgres psql -c "CREATE DATABASE newdatabase;"

次に、バックアップファイルからデータベースをリストアします。

pg_restore -U postgres -d newdatabase ~/project/mydatabase.sql

このコマンドは、pg_restore ユーティリティを使用して、postgres ユーザーとして newdatabase データベースに接続し、mydatabase.sql ファイルからデータベースをリストアします。

データベースが正常にリストアされたことを確認するには、psql を使用して newdatabase データベースに接続します。

sudo -u postgres psql -d newdatabase

そして、テーブルを一覧表示します。

\dt

users テーブルと products テーブルがリストに表示されるはずです。

テーブル内のデータをクエリして、データが正しくリストアされたことを確認することもできます。

SELECT * FROM users;
SELECT * FROM products;

ステップ 1 で挿入したのと同じデータが表示されるはずです。

psql シェルを終了します。

\q

単一テーブルのバックアップとリストア (Backup and Restore a Single Table)

このステップでは、PostgreSQL データベースから単一のテーブルをバックアップおよびリストアする方法を学びます。これは、データベースの特定の部分のみをバックアップする必要がある場合に役立ちます。

まず、mydatabase データベースから users テーブルのみをバックアップしましょう。

pg_dump -U postgres -d mydatabase -t users -f ~/project/users_only.sql

このコマンドは、-t オプションを使用して、users テーブルのみをバックアップするように指定します。

次に、このテーブルを newdatabase データベースにリストアしましょう。まず、newdatabase に存在する users テーブルを削除します。

sudo -u postgres psql -d newdatabase -c "DROP TABLE IF EXISTS users;"

次に、users_only.sql ファイルから users テーブルをリストアします。

pg_restore -U postgres -d newdatabase ~/project/users_only.sql

テーブルが正常にリストアされたことを確認するには、psql を使用して newdatabase データベースに接続します。

sudo -u postgres psql -d newdatabase

そして、テーブルを一覧表示します。

\dt

users テーブルが表示されますが、products テーブルは表示されません (users テーブルのみをリストアしたため)。

users テーブル内のデータをクエリして、データが正しくリストアされたことを確認することもできます。

SELECT * FROM users;

mydatabase データベースの users テーブルにあったのと同じデータが表示されるはずです。

psql シェルを終了します。

\q

まとめ (Summary)

この実験 (Lab) では、pg_dump および pg_restore ユーティリティを使用して PostgreSQL データベースをバックアップおよびリストアする方法を学びました。具体的には、以下の方法を習得しました。

データベースを作成し、データを投入する。
pg_dump を使用して、データベース全体を SQL ファイルにエクスポートする。
pg_restore を使用して、SQL ファイルからデータベースをリストアする。
pg_dump を使用して、データベースから単一のテーブルをバックアップする。
pg_restore を使用して、単一のテーブルをデータベースにリストアする。

これらは、PostgreSQL データベース管理者にとって不可欠なスキルです。