PostgreSQLのデータをpg_dumpallで移行する

#目的

__pg_dumpall__[^2]コマンドを使って__データベースクラスタ__[^1]ごとデータ移行する方法を説明します！
今回はTeraTermでWebサーバーに接続してpg_dumpallで出力したスクリプトファイルを使い、
Docker上のPostgreSQLコンテナにデータを移行します。
異なる環境の方でもpg_dumpallを利用したデータ移行の参考になれば幸いです！

#環境

- Windows 10 Pro 64bit メモリ 32GB
- Docker for Windows v20.10.2
- Docker Compose v1.27.4
- TeraTerm v4.105

#1. pg_dumpallでデータベースクラスタをスクリプト化する

TeraTermでWebサーバーに接続後、任意のディレクトリでpg_dumpallを実行します。

```
[web] $ pg_dumpall -U [user_name] > [file_name].out
```

user_name: データベースのユーザー名を入れてください。
file_name: スクリプトファイルに任意のファイル名をつけてください。

lsコマンドで指定したファイル名が表示されていれば成功です。

```
[web] $ ls
file_name.out
```

#2. スクリプトファイルをWebサーバーからローカルに転送する

TeraTermのSSH SCP機能を使ってWebサーバーからローカルにスクリプトファイルを転送します。
ファイル > SSH SCP... の順に操作し、以下のウィンドウを開きます。
![teraterm_captcha .png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/450243/61f6221e-926b-a8c1-e831-0359a24c2466.png)
From: スクリプトファイルのパスを入力します。
To: スクリプトファイルの転送先を入力します。ここではデスクトップを指定しています。

FromとToを入力したら「Recieve」をクリックして転送を開始します。

#3. Dockerコンテナに入る

Dockerコンテナは以下の通りです。ここではwebというコンテナがWebサーバーになります。

```
[win] $ docker-compose ps 
pgadmin4   /entrypoint.sh                   Up      443/tcp, 0.0.0.0:8000->80/tcp
php-cli    docker-php-entrypoint apac ...   Up      80/tcp
postgres   docker-entrypoint.sh postgres    Up      0.0.0.0:5432->5432/tcp
web        docker-php-entrypoint apac ...   Up      0.0.0.0:80->80/tcp
```

以下のコマンドでwebに入ります。

```
[win] $ docker-compose exec web bash
```

#4. スクリプトファイルからデータベースクラスタを作成する

以下のコマンドでスクリプトファイルを実行します。

```
[web] $ psql -h postgres -U default -d postgres -f file_name.out
```

-h: ホスト名を指定しています。
-U: ユーザー名を指定しています。
-d: データベース名を指定しています。
-f: 実行するファイル名を指定しています。

エラーが出ることなく処理が終われば、これで移行完了です。

試しに、スクリプトファイルのCREATE ROLEで作成されたロールで、新たに作成されたデータベースに接続してみます。

```
[web] $ psql -h postgres -U [role_name] -d [db_name]
```

以下のコマンドでテーブルが作成されていることを確認します。

<pre>
db_name=# &yen;dt
</pre>

以上がPostgreSQLのデータをpg_dumpallで移行する流れになります。

#スクリプトファイル実行時にALTER ROLEの権限エラーが発生した場合

pg_dumpallで出力したスクリプトファイルをそのまま実行すると、以下のようなエラーが出る場合があります。
僕もAWSのRDSにデータ移行をする際にこのエラーが発生したました。その時、対処した方法を説明します。

```
ERROR:  must be superuser to alter superusers
ERROR:  must be superuser to alter replication users
```

上記のエラーが出る場合、以下の権限についてはスクリプトファイルでALTER ROLEすることができません。

- SUPERUSER
- NOSUPERUSER
- REPLICATION
- NOREPLICATION

この場合は、スクリプトファイルのCREATE ROLEとALTER ROLEをコメントアウトし、
データベースに接続してCREATE ROLEをしてからGRANTで代替ロールを付与することで解消できます。

###CREATE ROLEとALTER ROLEをコメントアウトする

```
-- CREATE ROLE test_user;
-- ALTER ROLE test_user WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'xxxxxx';
```

###webからpsqlコマンドでデータベースに接続する

```
[web] $ psql -h postgres -U default -d postgres
```

###ロールの一覧を表示する

<pre>
postgres=# &yen;du
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 default    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
</pre>

これからスクリプトファイルでコメントアウトしたCREATE ROLEを実行し、
defaultが持つ権限をCREATE ROLEで作成されたロールに付与します。

###CREATE ROLEでロールを作成する

<pre>
db_name=# CREATE ROLE test_user
CREATE ROLE
</pre>

CREATE ROLE: PostgreSQLデータベースクラスタに新しいロールを追加します。

これで、test_userというロールが新たに作成されました。

<pre>
db_name=# &yen;du
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 default    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user  | Cannot login                                               | {}
</pre>

test_userでログイン可能にする場合は、以下のコマンドを実行してください。

<pre>
db_name=# ALTER ROLE test_user WITH LOGIN PASSWORD 'xxxxxx';
ALTER ROLE
</pre>

ALTER ROLE: ロールの属性を変更します。WITH以降に付与する属性を指定します。
LOGIN: ロールでログイン可能にします。
PASSWORD: パスワードを設定します。

これでtest_userのCannot loginが消え、test_userがユーザーとしてみなされるようになり、
設定したパスワードでログインできるようになります。

<pre>
db_name=# &yen;du
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 default    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user  |                                                            | {}
</pre>

###defaultが持っている権限をtest_userに付与する

<pre>
postgres=# GRANT "default" TO postgres;
GRANT ROLE
</pre>

GRANT: ロールに付与された権限を各メンバに伝えます。
ここではdefaultが持つ権限をtest_userに付与しています。

下記のようにtest_userのMember ofに{default}と記載されていれば成功です。

<pre>
postgres=# &yen;du
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 default   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user |                                                            | {default}
</pre>

これでスクリプトファイルを実行する準備ができました。

###psqlを終了する
<pre>
postgres=# &yen;q
</pre>

###スクリプトファイルからデータベースクラスタを作成する

```
[web] $ psql -h postgres -U default -d postgres -f file_name.out
```

エラーが出ることなく処理が終われば、これで移行完了です。

#おわりに

間違っている点やもっと良い方法などありましたら、ご指摘いただけるとありがたいです！
最後までお読みいただきありがとうございました！

#参考にした記事

- [PostgreSQL 10.5文書 第25章 バックアップとリストア](https://www.postgresql.jp/document/10/html/backup.html)
- [PostgreSQL 10.5文書 SQLコマンド](https://www.postgresql.jp/document/10/html/sql-commands.html)
- [RDS for PostgreSQLにユーザを移行する際の権限エラーについて](https://wannabe-data-engineer.net/user-migration-to-rds-for-postgresql/)
- [PostgreSQLの権限系操作まとめ](https://katsusand.dev/posts/postgresql-auth/)
- [PostgreSQLのbackup, restore方法まとめ](https://qiita.com/rice_american/items/ceae28dad13c3977e3a8)

[^1]: 1つのサーバインスタンスによって管理されるデータベースの集合体を意味します。
[^2]: データベースクラスタの全てのPostgreSQLデータベースをスクリプトファイルに出力します。
[^3]: PostgreSQLデータベースをスクリプトファイルまたは他のアーカイブファイルに出力します。