はじめに

データベース単位のバックアップとリストア(復元)の方法を調査しました。

概要

1. データベースのバックアップ
2. データベースの削除
3. データベースの作成
4. データベースのリストア

1. データベースのバックアップ

今回バックアップの対象はデータベースtestdbとし、テーブルtesttableを保持しているものとします。

testdb=# \dt
              リレーション一覧
 スキーマ |   名前    |  タイプ  |  所有者
----------+-----------+----------+----------
 public   | testtable | テーブル | postgres
(1 行)


testdb=# select * from testtable;
 id | name
----+------
  1 | abc
  2 | def
  3 | ghi
(3 行)

まず、PostgreSQLでのバックアップにはpg_dumpコマンドを使用します。

他のオプションを知りたい場合はpg_dump --helpコマンドで一覧を表示できます。

bin\pg_dump -f testdb_20220904.dump -F c -v -h localhost -p 5432 -U postgres -w testdb

オプション	値	説明
-f	testdb_20220904.dump	バックアップファイル名（フルパス可）
-F	c	出力形式（カスタム形式）
-v	なし	冗長モード
-h	localhost	ホスト名
-p	5432	ポート番号
-U	postgres	ユーザ名
-w	なし	PGPASSファイルからパスワード自動認証
指定	testdb	バックアップ対象データベース名

C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\pg_dump -f testdb_20220904.dump -F c -v -h localhost -p 5432 -U postgres testdb
pg_dump: 最後の組み込みOIDは16383
pg_dump: 機能拡張を読み込んでいます
pg_dump: 機能拡張の構成要素を特定しています
pg_dump: スキーマを読み込んでいます
pg_dump: ユーザ定義テーブルを読み込んでいます
pg_dump: ユーザ定義関数を読み込んでいます
pg_dump: ユーザ定義型を読み込んでいます
pg_dump: 手続き言語を読み込んでいます
pg_dump: ユーザ定義集約関数を読み込んでいます
pg_dump: ユーザ定義演算子を読み込んでいます
pg_dump: ユーザ定義アクセスメソッドを読み込んでいます
pg_dump: ユーザ定義演算子クラスを読み込んでいます
pg_dump: ユーザ定義演算子族を読み込んでいます
pg_dump: ユーザ定義のテキスト検索パーサを読み込んでいます
pg_dump: ユーザ定義のテキスト検索テンプレートを読み込んでいます
pg_dump: ユーザ定義のテキスト検索辞書を読み込んでいます
pg_dump: ユーザ定義のテキスト検索設定を読み込んでいます
pg_dump: ユーザ定義の外部データラッパーを読み込んでいます
pg_dump: ユーザ定義の外部サーバーを読み込んでいます
pg_dump: デフォルト権限設定を読み込んでいます
pg_dump: ユーザ定義の照合順序を読み込んでいます
pg_dump: ユーザ定義の変換を読み込んでいます
pg_dump: 型キャストを読み込んでいます
pg_dump: 変換を読み込んでいます
pg_dump: テーブル継承情報を読み込んでいます
pg_dump: イベントトリガを読み込んでいます
pg_dump: 機能拡張構成テーブルを探しています
pg_dump: 継承関係を検索しています
pg_dump: 対象テーブルの列情報を読み込んでいます
pg_dump: テーブル"public.testtable"の列と型を探しています
pg_dump: 子テーブルの継承列にフラグを設定しています
pg_dump: インデックスを読み込んでいます
pg_dump: パーティション親テーブルのインデックスにフラグを設定しています
pg_dump: 拡張統計情報を読み込んでいます
pg_dump: 制約を読み込んでいます
pg_dump: トリガを読み込んでいます
pg_dump: 書き換えルールを読み込んでいます
pg_dump: ポリシを読み込んでいます
pg_dump: reading row-level security policies
pg_dump: パブリケーションを読み込んでいます
pg_dump: パブリケーションの構成要素を読み込んでいます
pg_dump: サブスクリプションを読み込んでいます
pg_dump: ラージオブジェクトを読み込んでいます
pg_dump: データの依存データを読み込んでいます
pg_dump: encoding = UTF8 を保存しています
pg_dump: standard_conforming_strings = on を保存しています
pg_dump: search_path =  を保存しています
pg_dump: データベース定義を保存しています
pg_dump: テーブル "public.testtable"の内容をダンプしています

これでデータベースのバックアップファイルが作成されました。

オプション-wのパスワード自動認証の設定については過去記事を参照してください。

2. データベースの削除

リストアする前にデータベースの情報をクリアにするために削除します。

ログインしてからdrop databaseで削除してもよいですが、ここではログインせずにpsql -cから直接SQLコマンドを実行します。

C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\psql -h localhost -p 5432 -U postgres -d postgres -c  "drop database testdb;"
DROP DATABASE

こうすることでバッチでデータベース削除～リストアまでを一気に実行できます。

3. データベースの作成

こちらもpsql -cコマンドで空のデータベースを作成します。

リストアの対象テーブルはバックアップ元のデータベース名と異なっていても問題ありません。

C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\psql -h localhost -p 5432 -U postgres -d postgres -c  "create database testdb;"
CREATE DATABASE

4. データベースのリストア

最後にpg_restoreを用いてデータベースをリストアします。

もしバックアップ時のオプション-Fで出力形式を平文pにしている場合は、pg_restoreが非対応のため平文SQLをそのまま実行しましょう。

他のオプションを知りたい場合はpg_restore --helpコマンドで一覧を表示できます。

C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\pg_restore -d testdb -v -h localhost -p 5432 -U postgres -w testdb_20220904.dump
pg_restore: リストアのためデータベースに接続しています
pg_restore: TABLE "public.testtable"を作成しています
pg_restore: テーブル"public.testtable"のデータを処理しています

オプション	値	説明
-d	testdb	リストア対象データベース名
-v	なし	冗長モード
-h	localhost	ホスト名
-p	5432	ポート番号
-U	postgres	ユーザ名
-w	なし	PGPASSファイルからパスワード自動認証
指定	testdb_20220904.dump	バックアップ対象データベース名

これでデータベースのリストアが完了しました。

testdb=# \dt
              リレーション一覧
 スキーマ |   名前    |  タイプ  |  所有者
----------+-----------+----------+----------
 public   | testtable | テーブル | postgres
(1 行)


testdb=# select * from testtable;
 id | name
----+------
  1 | abc
  2 | def
  3 | ghi
(3 行)