最近、寒くなってきたものの東京は12月でも暖かいことに驚いている人です。
今回はwindowsでPostgreSQLのダンプ・リストアの方法について説明していきたいと思います。
はじめに
この記事は、株式会社スピードリンクジャパン Advent Calendar 2023 の20日目の記事です。
概要
そもそもDBのダンプ・リストアとは何か、それを行う方法について説明していきます。
初心者にもわかるように丁寧に説明します。
環境
- PostgreSQL 15.5
- Windows 11
ダンプ・リストアとは
この2つの作業を行うと同じ内容のデータベースを作成することができます。
| 名前 | 内容 |
|---|---|
| ダンプ | データベースをもとにダンプファイルを作ること。 |
| ダンプファイル | 元のデータベースの内容が反映されたSQLの羅列が記載されたもの |
| リストア | そのダンプファイルを使って別のデータベースに内容をコピーすること。 |
ダンプファイルのSQL文を編集することによって機密情報などの含めたくないデータを
他のデータに置き換えたり削除したりしてリストアすることもできます。
ダンプ・リストアの方法
手順を大雑把に説明するとこうなります
- dumpしてダンプファイルを作成
- ダンプしたものを入れる器となるdbを作成
- 作ったdbにリストアする
細かく説明していきます。
ダンプファイルの作成
以下のコマンドをコマンドプロンプトで実行します。
理由は分かりませんが、Windows Powershellで実行したところ、UTF-8でファイルが生成できませんでした。
pg_dump -d [db名] -h [ホスト名] -p [ポート] --encoding=UTF-8 --format=p -U [ユーザー名] -W --verbose > dump.sql
dump.sqlの部分は自分が保存したいファイル名を入力してください。
オプション
いくつかのオプション項目をピックアップし紹介します。
-E encoding
--encoding=encoding
指定した文字セット符号化方式でダンプを作成します。 デフォルトではダンプはデータベースの符号化方式で作成されます。 （環境変数PGCLIENTENCODINGを好みのダンプ時の符号化方式に設定することで、同じ結果を得ることができます。）
-F format
--format=formatdetails
出力形式を選択します。 formatには以下のいずれかを取ることができます。
-
p
plain
平文のSQLスクリプトファイルを出力します（デフォルト）。 -
c
custom
pg_restoreへの入力に適したカスタム形式アーカイブを出力します。 ディレクトリ出力形式と一緒に使用する場合、リストア時に手作業で保管された項目の選択、再順序付けできますので、これはもっとも柔軟な出力形式です。 また、この形式はデフォルトで圧縮されます。 -
d
directory
pg_restoreへの入力に適したディレクトリ形式のアーカイブを出力します。 これは、ダンプされる各テーブルおよびblobごとに1つのファイル、さらに、pg_restoreから読み取り可能な、機械的に読み取り易い書式でダンプしたオブジェクトを記述する目次ファイルと呼ばれるファイルを持つディレクトリを作成します。 ディレクトリ形式アーカイブは標準Unixツールで操作することができます。 例えば、未圧縮アーカイブ内のファイルをgzipツールを使用して圧縮することができます。 この形式はデフォルトで圧縮されます。 また並行ダンプをサポートします。 -
t
tar
pg_restoreへの入力に適したtar形式のアーカイブを出力します。 このtar形式はディレクトリ形式と互換性があります。 tar形式アーカイブを展開すると、有効なディレクトリ形式のアーカイブを生成します。 しかしtar形式は圧縮をサポートせず、個々のテーブルのサイズに関して8ギガバイトという上限があります。 またリストア時にテーブルデータ項目の相対的な順序を変更することはできません。
-W
--password
データベースに接続する前に、pg_dumpは強制的にパスワード入力を促します。
サーバがパスワード認証を要求する場合pg_dumpは自動的にパスワード入力を促しますので、これが重要になることはありません。 しかし、pg_dumpは、サーバにパスワードが必要かどうかを判断するための接続試行を無駄に行います。 こうした余計な接続試行を防ぐために-Wの入力が有意となる場合もあります。
-v
--verbose
冗長モードを指定します。 これを指定すると、pg_dumpは、詳細なオブジェクトコメント、開始時刻、終了時刻をダンプファイルに、進行状況メッセージを標準エラーに出力します。
引用：https://www.postgresql.jp/document/9.4/html/app-pgdump.html
他のオプションについてはコマンドのpg_dump --helpまたは上記の引用元URLから確認することができます
リストア先のDBを作成
psqlコマンドでpostgreSQLを起動します
例）psql -U username ※usernameにはpostgresなど自分の使用するものを入力してください
次に以下のコマンドでデータベースを作成します。
postgres=# create database [db名];
リストアする
以下のコマンドでリストアを行います。
psql -U [ユーザー名] -d [db名] -f dump.sql
db名には先ほど作成したdbの名前を入れます。
また、dump.sqlの部分は自分が保存したダンプファイルの名前を入力してください。
先ほど--format=pを指定したのでpsqlコマンドでリストアを行います。
フォーマットがplain以外のときはpg_restoreコマンドを使用します。
他のフォーマットでのリストアについては以下のサイトを参考にしてみてください。
リストア時のエラー
リストアの際にこのようなエラーが出る場合があります。
psql:dump.sql:1: エラー: 不正なコマンド \3-3c59dafd
psql:dump.sql:1: ERROR: invalid byte sequence for encoding "SJIS": 0xff 0xfe
これは文字コードが違うことで生じるエラーです。
自分の環境に合わせた文字コードでダンプしてきましょう。
私は以上のエラーが出た際はencodingの指定をUTF-8にしてdumpすることでエラーが解消されました。
参考：https://qiita.com/kuchita_el/items/142dbbfd416b86577e5c
終わりに
以上がダンプ・リストアの流れになります。
ここまで読んでいただきありがとうございます。
参考になりましたら幸いです。