この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは!コンサル部のinomaso(@inomasosan)です。
RDS for PostgreSQL内の特定データベースやテーブルを、pg_dumpとpg_restoreを使用してバックアップ・リストアできるか検証してみました。
まずは結論
RDS for PostgreSQL内に作成したデータベースやテーブルは、pg_dumpとpg_restoreを使用してバックアップ・リストア可能です。
しかし本番環境等で運用する場合、バックアップ運用やリストア時の推奨されるパラメータ変更で考慮が必要となりますので運用負荷が増えてしまいます。
そのためユースケースとしては、オンプレやEC2からRDSへのデータ移行や検証時の一部データベース・テーブルのバックアップ・リストア等が望ましいです。
基本的にはRDS標準機能であるDBスナップショットにてバックアップ運用して頂くほうが、運用負荷も大幅に軽減されますので良いかと思います。
やってみた
検証環境
項目 | バージョン |
---|---|
Amazon Linux2 | amzn2-ami-kernel-5.10-hvm-2.0.20211201.0-x86_64-gp2 |
pg_dump | 13.5 |
PostgreSQL | 13.4 |
構成
EC2(Amazon Linux2)とRDS for PostgreSQLが既に構築済みの前提となります。
検証ではRDS for PostgreSQLにdevelopment
というデータベースを追加で作成します。
今回は上記データベースと、その中のテーブルをバックアップ・リストアできるか検証していきます。
pg_dumpやpg_restoreを使用するために、今回の構成ではEC2(Amazon Linux2)にPostgreSQLのクライアントツールが必要となります。
EC2(Amazon Linux2)へのインストール方法については、下記ブログにまとめましたのでご参照ください。
尚、RDSのPostgreSQL拡張機能の追加は、今回の検証では特に必要ありませんでした。
検証用データベース・テーブル作成
EC2(Amazon Linux2)からRDS for PostgreSQLに接続し、検証用データベース・テーブルを作成していきます。
データベース接続
RDS for PostgreSQL作成時にデフォルトで作成されるpostgres
データベースに接続します。
$ psql -h <RDSエンドポイント> -U postgres -d postgres
データベース作成
development
というデータベースを新規作成します。
postgres=> CREATE DATABASE development LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;
DB一覧の確認
development
データベースが正常に作成できたか確認します。
postgres=> \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-------------+----------+------------------+-------------+-------------------+----------------------
-
development | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
+
| | | | | rdstopmgr=Tc/rdsadmin
template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin
+
| | | | | rdsadmin=CTc/rdsadmin
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | | postgres=CTc/postgres
(5 行)
データベース切断
postgres
データベースとの接続を切断します。
postgres=> \q
新規作成したデータベース接続
先ほど作成したdevelopment
データベースに接続します。
$ psql -h <RDSエンドポイント> -U postgres -d development
テーブル作成
検証用にdevelopment_table1
とdevelopment_table2
の2つのテーブルを新規作成します。
development=> CREATE TABLE development_table1
(id char(4) not null,
name text not null,
PRIMARY KEY(id));
development=> CREATE TABLE development_table2
(id char(4) not null,
name text not null,
PRIMARY KEY(id));
テーブル一覧確認
作成したテーブルを確認します。
development=> \d
リレーション一覧
スキーマ | 名前 | タイプ | 所有者
----------+--------------------+----------+----------
public | development_table1 | テーブル | postgres
public | development_table2 | テーブル | postgres
(2 行)
レコード追加
検証用に各テーブルにレコードを登録します。
development=> INSERT INTO development_table1(id,name) VALUES (1,'hogehoge1');
INSERT 0 1
development=> INSERT INTO development_table2(id,name) VALUES (1,'hogehoge2');
INSERT 0 1
テーブルのレコード確認
テーブルに登録したレコードを確認します。
development=> select * from development_table1;
id | name
------+-----------
1 | hogehoge1
(1 行)
development=> select * from development_table2;
id | name
------+-----------
1 | hogehoge2
(1 行)
データベース切断
development
データベースとの接続を切断します。
development=> \q
データベース単位のリストア
EC2(Amazon Linux2)からpg_dumpとpg_restoreを実行し、development
データベースのバックアップ・リストアを検証します。
バックアップ
pg_dump
でdevelopment
データベースのバックアップを作成します。
-Fc
オプションを指定する事で、バックアップはデフォルトで圧縮されます。
$ pg_dump -Fc -v -h <RDSエンドポイント> -U postgres development > db.dump
パスワード:
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.development_table1"の列と型を探しています
pg_dump: テーブル"public.development_table2"の列と型を探しています
pg_dump: 子テーブルの継承列にフラグを設定しています
pg_dump: インデックスを読み込んでいます
pg_dump: テーブル"public.development_table1"のインデックスを読み込んでいます
pg_dump: テーブル"public.development_table2"のインデックスを読み込んでいます
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.development_table1"の内容をダンプしています
pg_dump: テーブル "public.development_table2"の内容をダンプしています
リストア
pg_restoreでdevelopment
データベースをリストアします。
既存データベースへリストアするため、-c
オプションで新しいテーブルが作成される前に古いテーブルが削除します。
$ pg_restore -c -v -h <RDSエンドポイント> -U postgres -d development db.dump
pg_restore: リストアのためデータベースに接続しています
パスワード:
pg_restore: CONSTRAINT development_table2 development_table2_pkeyを削除しています
pg_restore: CONSTRAINT development_table1 development_table1_pkeyを削除しています
pg_restore: TABLE development_table2を削除しています
pg_restore: TABLE development_table1を削除しています
pg_restore: TABLE "public.development_table1"を作成しています
pg_restore: TABLE "public.development_table2"を作成しています
pg_restore: テーブル"public.development_table1"のデータを処理しています
pg_restore: テーブル"public.development_table2"のデータを処理しています
pg_restore: CONSTRAINT "public.development_table1 development_table1_pkey"を作成しています
pg_restore: CONSTRAINT "public.development_table2 development_table2_pkey"を作成しています
-c
オプションを指定せずに既存のデータベースへリストアしようとすると、テーブル作成時に重複エラーとなります。
$ pg_restore -v -h <RDSエンドポイント> -U postgres -d development db.dump
pg_restore: リストアのためデータベースに接続しています
パスワード:
pg_restore: TABLE "public.development_table1"を作成しています
pg_restore: TOC処理中:
pg_restore: TOCエントリ200; 1259 40978 TABLE development_table1 postgres から
pg_restore: エラー: could not execute query: ERROR: relation "development_table1" already exists
コマンド: CREATE TABLE public.development_table1 (
id character(4) NOT NULL,
name text NOT NULL
);
pg_restore: TABLE "public.development_table2"を作成しています
pg_restore: TOCエントリ201; 1259 40986 TABLE development_table2 postgres から
pg_restore: エラー: could not execute query: ERROR: relation "development_table2" already exists
コマンド: CREATE TABLE public.development_table2 (
id character(4) NOT NULL,
name text NOT NULL
);
pg_restore: テーブル"public.development_table1"のデータを処理しています
pg_restore: TOCエントリ3847; 0 40978 TABLE DATA development_table1 postgres から
pg_restore: エラー: テーブル"development_table1"へのコピーに失敗しました: ERROR: duplicate key value violates unique constraint "development_table1_pkey"
DETAIL: Key (id)=(1 ) already exists.
CONTEXT: COPY development_table1, line 1
pg_restore: テーブル"public.development_table2"のデータを処理しています
pg_restore: TOCエントリ3848; 0 40986 TABLE DATA development_table2 postgres から
pg_restore: エラー: テーブル"development_table2"へのコピーに失敗しました: ERROR: duplicate key value violates unique constraint "development_table2_pkey"
DETAIL: Key (id)=(1 ) already exists.
CONTEXT: COPY development_table2, line 1
pg_restore: CONSTRAINT "public.development_table1 development_table1_pkey"を作成しています
pg_restore: TOCエントリ3714; 2606 40985 CONSTRAINT development_table1 development_table1_pkey postgres から
pg_restore: エラー: could not execute query: ERROR: multiple primary keys for table "development_table1" are not allowed
コマンド: ALTER TABLE ONLY public.development_table1
ADD CONSTRAINT development_table1_pkey PRIMARY KEY (id);
pg_restore: CONSTRAINT "public.development_table2 development_table2_pkey"を作成しています
pg_restore: TOCエントリ3716; 2606 40993 CONSTRAINT development_table2 development_table2_pkey postgres から
pg_restore: エラー: could not execute query: ERROR: multiple primary keys for table "development_table2" are not allowed
コマンド: ALTER TABLE ONLY public.development_table2
ADD CONSTRAINT development_table2_pkey PRIMARY KEY (id);
pg_restore: 警告: リストア中に無視されたエラー数: 6
テーブル単位のリストア
EC2(Amazon Linux2)からpg_dumpとpg_restoreを実行し、development
データベース内の特定テーブルのバックアップ・リストアを検証します。
バックアップ
pg_dump
でdevelopment
データベースのバックアップを作成します。
-Fc
オプションを指定する事で、バックアップはデフォルトで圧縮されます。
-t
オプションでdevelopment_table1
テーブルのみをバックアップ対象としています。
$ pg_dump -Fc -v -h <RDSエンドポイント> -U postgres -t development_table1 development > db_table.dump
パスワード:
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.development_table1"の列と型を探しています
pg_dump: 子テーブルの継承列にフラグを設定しています
pg_dump: インデックスを読み込んでいます
pg_dump: テーブル"public.development_table1"のインデックスを読み込んでいます
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: encoding = UTF8 を保存しています
pg_dump: standard_conforming_strings = on を保存しています
pg_dump: search_path = を保存しています
pg_dump: データベース定義を保存しています
pg_dump: テーブル "public.development_table1"の内容をダンプしています
リストア
pg_restore
でdevelopment
データベースをリストアします。
既存データベースへリストアするため、-c
オプションで新しいテーブルが作成される前に古いテーブルが削除します。
pg_dump
でdevelopment_table1
テーブルのみバックアップ対象としているので、リストアもdevelopment_table1
テーブルのみとなります。
$ pg_restore -c -v -h <RDSエンドポイント> -U postgres -d development db_table.dump
pg_restore: リストアのためデータベースに接続しています
パスワード:
pg_restore: CONSTRAINT development_table1 development_table1_pkeyを削除しています
pg_restore: TABLE development_table1を削除しています
pg_restore: TABLE "public.development_table1"を作成しています
pg_restore: テーブル"public.development_table1"のデータを処理しています
pg_restore: CONSTRAINT "public.development_table1 development_table1_pkey"を作成しています
参考URL
まとめ
RDS for PostgreSQLでpg_dumpやpg_restoreを利用できるかについてわからなかったため、実際に検証で試してみました。
移行や検証等のユースケースでは有用ですが、本番環境等ではRDSのDBスナップショットでのバックアップ運用を推奨いたします。
この記事が、どなたかのお役に立てば幸いです。それでは!