【DB】WindowsでPostgreSQLのDBのダンプ・リストアしてみた

最近、寒くなってきたものの東京は12月でも暖かいことに驚いている人です。
今回はwindowsでPostgreSQLのダンプ・リストアの方法について説明していきたいと思います。

# はじめに

この記事は、株式会社スピードリンクジャパン Advent Calendar 2023 の20日目の記事です。

https://qiita.com/advent-calendar/2023/slj						
						
						
						

# 概要
そもそもDBのダンプ・リストアとは何か、それを行う方法について説明していきます。
初心者にもわかるように丁寧に説明します。

# 環境
- PostgreSQL 15.5
- Windows 11

# ダンプ・リストアとは
この2つの作業を行うと同じ内容のデータベースを作成することができます。
|名前|内容|
|:-----------|:-----------|
|[ダンプ](https://wa3.i-3-i.info/word1569.html)|データベースをもとにダンプファイルを作ること。|
|[ダンプファイル](https://wa3.i-3-i.info/word1570.html)|元のデータベースの内容が反映されたSQLの羅列が記載されたもの|
|[リストア](https://wa3.i-3-i.info/word17817.html)|そのダンプファイルを使って別のデータベースに内容をコピーすること。|


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3524128/910fb97a-4990-bc5d-861f-3cf04a700eff.png)

ダンプファイルのSQL文を編集することによって機密情報などの含めたくないデータを
他のデータに置き換えたり削除したりしてリストアすることもできます。

# ダンプ・リストアの方法
手順を大雑把に説明するとこうなります
1. dumpしてダンプファイルを作成
1. ダンプしたものを入れる器となるdbを作成
1. 作ったdbにリストアする

細かく説明していきます。
### ダンプファイルの作成
以下のコマンドをコマンドプロンプトで実行します。
理由は分かりませんが、Windows Powershellで実行したところ、UTF-8でファイルが生成できませんでした。
```terminal
pg_dump -d [db名] -h [ホスト名] -p [ポート] --encoding=UTF-8 --format=p -U [ユーザー名] -W --verbose > dump.sql
```
`dump.sql`の部分は自分が保存したいファイル名を入力してください。

##### オプション
いくつかのオプション項目をピックアップし紹介します。
<details><summary>-E encoding<br>
--encoding=encoding</summary>

:::note info
指定した文字セット符号化方式でダンプを作成します。 デフォルトではダンプはデータベースの符号化方式で作成されます。 （環境変数PGCLIENTENCODINGを好みのダンプ時の符号化方式に設定することで、同じ結果を得ることができます。）
:::
</details>
リストア時に文字コード関係のエラーが出ないような適切な文字コードを選択するようにしてください。
 
<details><summary>-F format<br>
--format=formatdetails</summary>

:::note info
出力形式を選択します。 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ギガバイトという上限があります。 またリストア時にテーブルデータ項目の相対的な順序を変更することはできません。
:::
</details>

<details><summary>-W<br>--password</summary>

:::note info
データベースに接続する前に、pg_dumpは強制的にパスワード入力を促します。

サーバがパスワード認証を要求する場合pg_dumpは自動的にパスワード入力を促しますので、これが重要になることはありません。 しかし、pg_dumpは、サーバにパスワードが必要かどうかを判断するための接続試行を無駄に行います。 こうした余計な接続試行を防ぐために-Wの入力が有意となる場合もあります。
:::
</details>

<details><summary>-v<br>
--verbose</summary>

:::note info
冗長モードを指定します。 これを指定すると、pg_dumpは、詳細なオブジェクトコメント、開始時刻、終了時刻をダンプファイルに、進行状況メッセージを標準エラーに出力します。
:::

</details>

引用：https://www.postgresql.jp/document/9.4/html/app-pgdump.html
他のオプションについてはコマンドの`pg_dump --help`または上記の引用元URLから確認することができます

### リストア先のDBを作成
psqlコマンドでpostgreSQLを起動します
例）`psql -U username` ※usernameにはpostgresなど自分の使用するものを入力してください

次に以下のコマンドでデータベースを作成します。
```terminal
postgres=# create database [db名];
```

### リストアする
以下のコマンドでリストアを行います。
```terminal
psql -U [ユーザー名] -d [db名] -f dump.sql
```
db名には先ほど作成したdbの名前を入れます。
また、`dump.sql`の部分は自分が保存したダンプファイルの名前を入力してください。

先ほど`--format=p`を指定したので`psql`コマンドでリストアを行います。
フォーマットがplain以外のときは`pg_restore`コマンドを使用します。
他のフォーマットでのリストアについては以下のサイトを参考にしてみてください。

https://postgresweb.com/post-4279
##### リストア時のエラー
リストアの際にこのようなエラーが出る場合があります。
```
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

# 終わりに
以上がダンプ・リストアの流れになります。
ここまで読んでいただきありがとうございます。
参考になりましたら幸いです。