目的
pg_dumpall1コマンドを使ってデータベースクラスタ2ごとデータ移行する方法を説明します！
今回は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... の順に操作し、以下のウィンドウを開きます。
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]
以下のコマンドでテーブルが作成されていることを確認します。
db_name=# ¥dt
以上が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
ロールの一覧を表示する
postgres=# ¥du
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
default | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
これからスクリプトファイルでコメントアウトしたCREATE ROLEを実行し、
defaultが持つ権限をCREATE ROLEで作成されたロールに付与します。
CREATE ROLEでロールを作成する
db_name=# CREATE ROLE test_user
CREATE ROLE
CREATE ROLE: PostgreSQLデータベースクラスタに新しいロールを追加します。
これで、test_userというロールが新たに作成されました。
db_name=# ¥du
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
default | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | Cannot login | {}
test_userでログイン可能にする場合は、以下のコマンドを実行してください。
db_name=# ALTER ROLE test_user WITH LOGIN PASSWORD 'xxxxxx';
ALTER ROLE
ALTER ROLE: ロールの属性を変更します。WITH以降に付与する属性を指定します。
LOGIN: ロールでログイン可能にします。
PASSWORD: パスワードを設定します。
これでtest_userのCannot loginが消え、test_userがユーザーとしてみなされるようになり、
設定したパスワードでログインできるようになります。
db_name=# ¥du
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
default | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
defaultが持っている権限をtest_userに付与する
postgres=# GRANT "default" TO postgres;
GRANT ROLE
GRANT: ロールに付与された権限を各メンバに伝えます。
ここではdefaultが持つ権限をtest_userに付与しています。
下記のようにtest_userのMember ofに{default}と記載されていれば成功です。
postgres=# ¥du
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
default | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {default}
これでスクリプトファイルを実行する準備ができました。
psqlを終了する
postgres=# ¥q
スクリプトファイルからデータベースクラスタを作成する
[web] $ psql -h postgres -U default -d postgres -f file_name.out
エラーが出ることなく処理が終われば、これで移行完了です。
おわりに
間違っている点やもっと良い方法などありましたら、ご指摘いただけるとありがたいです！
最後までお読みいただきありがとうございました！